How to Insert Data Directly from Excel to Oracle Database

How to import data from Excel to PL/SQL Developer

There are two options:

Option I:

• Create test table with no. of columns and data type matching with excel data.
• Open the test table in PL/SQL developer in edit mode. You can do this by selecting rowid in SELECT statement or writing the SELECT with 'for update'. For example:

SELECT t.*, t.rowid FROM test t;
OR
SELECT t.* FROM test t for update;

• Click on the lock ICON to keep it in pressed status for opening the fetched rows for editing.
• Now copy the data range from excel and in PL/SQL developers SQL result grid, select the empty column and paste the copied data.
  (Note: you would need to add one empty column in the beginning of your first data column and include that blank column as well while copying data. This blank column is a placeholder for the serial no. column in PL/SQL developer's result grid. Otherwise your first data column will be eaten up by the serial no. column of result grid. :-) ).
• Click on green tick to commit the data to database.

Option II:
In excel you can compose INSERT query referencing data cells with help of Excel's concatenation (using & ) and execute all INSERT in PL/SQL developer.

Import data from Excel into Oracle Table using Python

Consider the following changes:

• Reversing your nested for loop by running down the rows and iteratively assigning column values and then append each row.
• Indent your execute line in row-wise loop.
• Use .commit() for any action queries like CREATE TABLE and INSERT INTO to propagate changes.
• Parameterize your query by using the second argument in .execute(query, params) which not only insulates SQL injection (in case Excel cells have malicious code by a clever user) but avoids string concatenation and quote enclosure for cleaner code. See Oracle+Python docs.

Adjusted code

# looping through each row for each column      
for i in range(1, ws.max_row+1):
   for j in range(2, ws.max_column+1):       
      col1 = ws.cell(row=i, column=j).value  
      col2 = ws.cell(row=i, column=j).value
      col3 = ws.cell(row=i, column=j).value
      col4 = ws.cell(row=i, column=j).value
      col5 = ws.cell(row=i, column=j).value
      col6 = ws.cell(row=i, column=j).value
      col7 = ws.cell(row=i, column=j).value

   insert_table = "INSERT INTO test (col1, col2, col3, col4, col5, col6, col7)" + \
                  " VALUES (:1, :2, :3, :4, :5, :6, :7)"

   cursor.execute(insert_table, (col1, col2, col3, col4, col5, col6, col7))
   connection.commit()

connection.close()

Related Topics