Mar-08-2024, 11:19 PM
hi
I am working on this for the last night but no luck . I am trying to load multiple csv files from a specific folder and load in into oracle database table.
I checked the oracle connection and it is working fine and I also have table existing but still getting an error, see below for code:
Code:
import pandas as pd
import glob
import os
import oracledb
# Get a list of all CSV files in a directory
csv_files = glob.glob('C:/Temp/ihotel/*.csv')
# Create an empty dataframe to store the combined data
combined_df = pd.DataFrame()
# Loop through each CSV file and append its contents to the combined dataframe
for csv_file in csv_files:
# Extract the worksheet name (file name without extension)
worksheet_name = os.path.splitext(os.path.basename(csv_file))[0]
# Read the CSV file into a dataframe
df = pd.read_csv(csv_file)
# Add a new column with the worksheet name
df['worksheet_name'] = worksheet_name
# Append the dataframe to the combined dataframe
combined_df = pd.concat([combined_df, df])
# Print the combined dataframe
print(combined_df)
# Establish a connection to your Oracle database
# Replace 'username', 'password', 'hostname', 'port', and 'service_name' with your Oracle connection details
connection = oracledb.connect('sales/temp@WHTHYT-SSPRD5:1521/REPDBTMPOP')
print (connection.version)
# Convert the DataFrame to a list of tuples
data = [tuple(row) for row in combined_df.values]
# Define the SQL INSERT statement
# Replace 'crv_loader' with the name of your Oracle table
sql = "INSERT INTO csv_loader (group_name,circuit,bw_up,bw_down,usage,rate,total_gb,rounded_gb,usage_charge,worksheet_name) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" # Adjust the SQL statement as per your table structure
print(data)
# Create a cursor
cursor = connection.cursor()
# Execute the INSERT statement for each row of data
cursor.executemany(sql, data)
# Commit the transaction to save the changes
connection.commit()
# Close the cursor and connection
cursor.close()
connection.close()
print("Data loaded successfully into cv_loader table.")
Error
PS C:\Users\khan>
oracledb.exceptions.DatabaseError: ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
PS C:\Users\khan> & C:/Users/mzkhan/AppData/Local/Programs/Python/Python311-32/python.exe "c:/Users/mzkhan/OneDrive - North Inc/Desktop/import pandas_new as pd.py"
Group_Name Circuit BW_Up BW_Down Usage Rate Total_GB Rounded_GB Usage_Charge worksheet_name
0 iHotel_Ktikmeot 31LUXX 1 3 50 7.5 147.236 147.5 731.25 i-int_hotel_usage012013
1 iHotel_WInn 32LUXX 7 3 75 7.5 386.644 387.0 2340.00 i-int_hotel_usage012013
C:\Users\khan\AppData\Local\Programs\Python\Python311-32\Lib\site-packages\cryptography\hazmat\backends\openssl\backend.py:17: UserWarning: You are using cryptography on a 32-bit Python on a 64-bit Windows Operating System. Cryptography will be significantly faster if you switch
to using a 64-bit Python.
from cryptography.hazmat.bindings.openssl import binding
12.1.0.2.0
[('iHotel_Ktikmeot', '31LUXX', 1, 3, 50, 7.5, 147.236, 147.5, 731.25, 'i-int_hotel_usage012013'), ('iHotel_WInn', '32LUXX', 7, 3, 75, 7.5, 386.644, 387.0, 2340.0, 'i-int_hotel_usage012013')]
Traceback (most recent call last):
File "c:\Users\khan\OneDrive - North Inc\Desktop\import pandas_new as pd.py", line 44, in <module>
cursor.executemany(sql, data)
File "C:\Users\khan\AppData\Local\Programs\Python\Python311-32\Lib\site-packages\oracledb\cursor.py", line 809, in executemany
self._impl.executemany(
File "src\\oracledb\\impl/thin/cursor.pyx", line 211, in oracledb.thin_impl.ThinCursorImpl.executemany
File "src\\oracledb\\impl/thin/protocol.pyx", line 425, in oracledb.thin_impl.Protocol._process_single_message
File "src\\oracledb\\impl/thin/protocol.pyx", line 426, in oracledb.thin_impl.Protocol._process_single_message
File "src\\oracledb\\impl/thin/protocol.pyx", line 419, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
PS C:\Users\khan>
I am working on this for the last night but no luck . I am trying to load multiple csv files from a specific folder and load in into oracle database table.
I checked the oracle connection and it is working fine and I also have table existing but still getting an error, see below for code:
Code:
import pandas as pd
import glob
import os
import oracledb
# Get a list of all CSV files in a directory
csv_files = glob.glob('C:/Temp/ihotel/*.csv')
# Create an empty dataframe to store the combined data
combined_df = pd.DataFrame()
# Loop through each CSV file and append its contents to the combined dataframe
for csv_file in csv_files:
# Extract the worksheet name (file name without extension)
worksheet_name = os.path.splitext(os.path.basename(csv_file))[0]
# Read the CSV file into a dataframe
df = pd.read_csv(csv_file)
# Add a new column with the worksheet name
df['worksheet_name'] = worksheet_name
# Append the dataframe to the combined dataframe
combined_df = pd.concat([combined_df, df])
# Print the combined dataframe
print(combined_df)
# Establish a connection to your Oracle database
# Replace 'username', 'password', 'hostname', 'port', and 'service_name' with your Oracle connection details
connection = oracledb.connect('sales/temp@WHTHYT-SSPRD5:1521/REPDBTMPOP')
print (connection.version)
# Convert the DataFrame to a list of tuples
data = [tuple(row) for row in combined_df.values]
# Define the SQL INSERT statement
# Replace 'crv_loader' with the name of your Oracle table
sql = "INSERT INTO csv_loader (group_name,circuit,bw_up,bw_down,usage,rate,total_gb,rounded_gb,usage_charge,worksheet_name) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" # Adjust the SQL statement as per your table structure
print(data)
# Create a cursor
cursor = connection.cursor()
# Execute the INSERT statement for each row of data
cursor.executemany(sql, data)
# Commit the transaction to save the changes
connection.commit()
# Close the cursor and connection
cursor.close()
connection.close()
print("Data loaded successfully into cv_loader table.")
Error
PS C:\Users\khan>
oracledb.exceptions.DatabaseError: ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
PS C:\Users\khan> & C:/Users/mzkhan/AppData/Local/Programs/Python/Python311-32/python.exe "c:/Users/mzkhan/OneDrive - North Inc/Desktop/import pandas_new as pd.py"
Group_Name Circuit BW_Up BW_Down Usage Rate Total_GB Rounded_GB Usage_Charge worksheet_name
0 iHotel_Ktikmeot 31LUXX 1 3 50 7.5 147.236 147.5 731.25 i-int_hotel_usage012013
1 iHotel_WInn 32LUXX 7 3 75 7.5 386.644 387.0 2340.00 i-int_hotel_usage012013
C:\Users\khan\AppData\Local\Programs\Python\Python311-32\Lib\site-packages\cryptography\hazmat\backends\openssl\backend.py:17: UserWarning: You are using cryptography on a 32-bit Python on a 64-bit Windows Operating System. Cryptography will be significantly faster if you switch
to using a 64-bit Python.
from cryptography.hazmat.bindings.openssl import binding
12.1.0.2.0
[('iHotel_Ktikmeot', '31LUXX', 1, 3, 50, 7.5, 147.236, 147.5, 731.25, 'i-int_hotel_usage012013'), ('iHotel_WInn', '32LUXX', 7, 3, 75, 7.5, 386.644, 387.0, 2340.0, 'i-int_hotel_usage012013')]
Traceback (most recent call last):
File "c:\Users\khan\OneDrive - North Inc\Desktop\import pandas_new as pd.py", line 44, in <module>
cursor.executemany(sql, data)
File "C:\Users\khan\AppData\Local\Programs\Python\Python311-32\Lib\site-packages\oracledb\cursor.py", line 809, in executemany
self._impl.executemany(
File "src\\oracledb\\impl/thin/cursor.pyx", line 211, in oracledb.thin_impl.ThinCursorImpl.executemany
File "src\\oracledb\\impl/thin/protocol.pyx", line 425, in oracledb.thin_impl.Protocol._process_single_message
File "src\\oracledb\\impl/thin/protocol.pyx", line 426, in oracledb.thin_impl.Protocol._process_single_message
File "src\\oracledb\\impl/thin/protocol.pyx", line 419, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
PS C:\Users\khan>