How to Import from Excel to Oracle with SQL Developer

thatjeffsmith SQL Developer 328 Comments

Tell Others About This Story:

TL;DR – Skip to the video!

This question comes up about as frequently as the ‘how do I export data to XLS?’ question. It’s pretty simple once you’ve ran through the process a few times. But you may be here because you’ve never ran through the process before.

This post will step you from beginning to end. You should be prepared to import data to an existing table from Excel with confidence after reading this post. You can use SQL Developer to create a new table for your Excel data, but that will be covered in a subsequent post.

Warning: This post has a LOT of pictures.

For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.

Note: We’ve updated this feature for version 4.1!

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!

You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.

Here’s what it looks like if you uncheck the ‘Header’ box

Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?

For this exercise the ‘Insert’ method will be used.

Step 5:

Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:

If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.

Ruh roh raggy!

SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.

So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.

After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!

Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!

Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!

Just a few more pointers when it comes to Excel

  • Storing data in Excel can be…dangerous
  • If you have a lot of data or if this will be a repeating process, consider External Tables or SQL*Loader instead
  • If you like to put data in Oracle because you understand SQL better than Excel macros, then welcome to the club 😉

The Movie

Related Posts

Tell Others About This Story:

Comments 328

  1. Hi Jeff,

    I needed to update a section of my data.

    I exported to excel, corrected the data and now I want to update the columns. Please guide

    1. thatjeffsmith Post
      Author

      Once the data is in Oracle, why would you go back to Excel?

      Open the data panel for your table and do your edits there.

      Or, truncate the table and do the import again.

  2. Thank you for the posting. I have use cases where the input data is delimited – not just an excel file. Typically, we have users who access the standby database for their read only queries. Sometimes, these users will have an input file that needs to be loaded into a temp table to join with other tables. This process is repeated periodically, the only variant being the input data.

    I would like to automate the process of importing the data into a temp table followed by the queries instead of a manual/interactive process like described above. Can we use SQL Developer to automate this import into temporary tables?

    1. thatjeffsmith Post
      Author

      So a delimited file, yes. You can use SQL Developer to setup that as an external table scenario.

      So what you’d want is to have an external table on the database defined and a process to move those delimited text files to the oracle directory where the external table is reading from.

      Then you could create a job to do an insert as select from the external into your perm table.

    2. Thank you. An external table is not an option since the clients will not have write access to the multi-tenant database server. It would be great if we could script the interactive process that is used for importing data.

    3. thatjeffsmith Post
      Author
  3. Hi Jeff,
    Actually I have an Excel file containing the records which will populate various tables in my database.
    It works with the sql developer. What I intend to do is to write a generic sql script for selecting and inserting data from the excel file.
    My question is how to convert this procedure of clicks to an executable script?

    1. thatjeffsmith Post
      Author

      I have two ideas:
      1 – instead of Excel files, work with CSVs. Then put those files in an Oracle Directory on the database server. Then you can create external tables over those and just use SQL to do your updates.
      2 – find out where the raw data is coming from. If that is a system, create a API on those systmes and have the database reach out to where the data is. In other words, cut out the Excel middle-man.

    2. A process to automate the clicks would be very valuable. Typically, self service analytics involves using a set of external/personal files that are used to constrain the result set from the database. IT policies and network segmentation does not allow the use of External tables, though it may be an option. SQL Loader does not work with loading this data into global temp tables, since the load happens in a separate session. I hope SQL Developer will consider this automation feature.

  4. Hi,

    I am having the following problem when inserting data.

    I have used this method seamlessly for months now, the new data always comes in an excel and i just inert the excel.

    I am now getting this error that says “Data is not compatible with column definition or is not available for a not nullable column”

    Please note that none of th erows are null, and the table is set up to allow nulls anyways…

    Any Help?

    Thnaks

    1. thatjeffsmith Post
      Author

      I’d really need to see your table and Excel file to help, but:

      • version of SQLDev?
      • when do you get the error exactly?
      • does the INSERT SCRIPT method work?
      • save the Excel file as a CSV and try that
  5. Hi,
    It TOAD it was an option to commit every Number records while loading from Excel. Is there similar option in Oracle Developer?
    Thank you.

  6. I have an auto generated sequence (PK) column that I need to exclude during import from file. Is there a way to exclude that column from the target table during import? There is no guarantee that the header column will be consistently the same or match the column name so I have to use the position during import.

    1. thatjeffsmith Post
      Author
  7. I had 7500 records in the excel , but the script inserted nearly 50k records , the remaining were all null records, how do I prevent it

    1. thatjeffsmith Post
      Author
  8. Hi Jeff,

    I am trying to load a date column in a Table from a csv file. I have labelled the column as a DATE column and explicitly specified the format as ‘MM/DD/YYYY’. It works fine for most of the rows but in cases where the date is null, it is giving me an error.
    It is creating an INSERT statement that looks something like this :
    Insert into table (id, date_col) values (123, to_date(”,”MM/DD/YYYY”));

    The column is allowed to have NULL values in the table definition but it is still giving an error. Is there a work around that I can use to overcome this issue.

    Thanks

    1. thatjeffsmith Post
      Author
    2. person_id admit discharge Performedon Profession
      100 02/01/2018 RESPIRATORY THERAPIST
      101 02/15/2018 02/18/2018 02/15/2018 RESPIRATORY THERAPIST
      102 02/15/2018 02/16/2018 RESPIRATORY THERAPIST
      103 02/18/2018 RESPIRATORY THERAPIST

      In the above rows, Person_ID 101 has all the columns filled and rest of the rows have some date elements missing. These are all valid rows, so the table is built to accept NULL values for these columns. However, when I am trying to do the import using SQL Developer. I am getting an error related to null values being present in Date columns.

    3. thatjeffsmith Post
      Author
    4. Yes, the commas are there. Sorry I just copied it from the open sheet and pasted it here. The file loads fine except the rows where dates are null. Is there a way of fixing this problem while doing the import from SQL Developer.

    5. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *