How to Import from Excel to Oracle with SQL Developer

thatjeffsmith SQL Developer 313 Comments

Tell Others About This Story:

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 😉
Tell Others About This Story:

Comments 313

  1. 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
  2. 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
  3. 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
      1. 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.

      2. thatjeffsmith Post
        Author
      3. 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.

      4. thatjeffsmith Post
        Author
  4. Hello,
    I am able to import the csv files into new tables. My problem is that I get new data every year, but this is stored in similar csv file, then and I would like to update my table with the new data only. How do I import only the new rows and avoid duplicates?
    Or should I import completely the new file and then do a merge/ join? I would appreciate your suggestion on the best way to do this…
    Thanks,
    Julia.

    1. thatjeffsmith Post
      Author
      1. Thank you! Then I import as before and I will have error every time it finds a duplicate. My concern is: the import is done by 50 rows at a time, then if I just skip that error, do I miss all 50 data rows? I did a trial import and seems that it did not import all the new data (135 rows missing) Is there a way to do the import one row at a time?
        Thanks…

      2. thatjeffsmith Post
        Author
      3. Great! it works…. its slow though, too much data. I wonder if there is a way to compare tables and identify the new data only? Then insert that into the database table…
        Also, why would it ask for “substitution variables” every other script?

      4. thatjeffsmith Post
        Author

        Define ‘slow?’

        >>Also, why would it ask for “substitution variables” every other script?
        Who is ‘it?’ If your data has & in it, you’ll need to set SCAN OFF in your script.

  5. Hi
    I have big problem to import data.
    I want to know how to solve this problem or can sql developer handle it?
    My problem is I want to import data from oracle tables that have relation.
    But I redesigned my model and new tables in not the same as before.
    Some old table splited to to or more tables.
    i want to import programmatically or customized.
    Please help me if can.
    I use Oracle databases schema;

    1. thatjeffsmith Post
      Author
  6. thatjeffsmith Post
    Author
  7. I have been using this process for several months. Works flawlessly each and everytime. Users fill in hundreds of rows on a spreadsheet, and I load it into the database in a matter of minutes.

    So I get a list of course sessions to load into a table and on step 4, I get a SERIOUS error message…Source columns mapped to the same target table column COURSE:CNAME,

    Table column name is COURSE, name of column on spreadsheet is CNAME. This is the first time I have encountered this. What does the error mean?

    1. thatjeffsmith Post
      Author
      1. Problem solved!!!

        My spreadsheet has 20 columns. When pasting the data into the spreadsheet, I used 21 columns. The first column is the COURSE field. Evidently, when the SQL import encounters data in the 21st column, it maps to COURSE hence the error msg:

        Source columns mapped to the same target table column COURSE:CNAME,

      2. thatjeffsmith Post
        Author
    1. thatjeffsmith Post
      Author
  8. I am not able to import CSV File. I get the long message but in short, it says Task canceled and import rolled back. Can you help, please? I am following all the steps you mentioned above.

    1. thatjeffsmith Post
      Author
  9. When populating an Oracle table with fields that are ‘non nullable’. what value do you put in the Excel cell as a blank character?

    1. thatjeffsmith Post
      Author
  10. I have a csv file that I want to load using import data, one column is a DATE data type. I cannot format the csv input file correctly for nulls. I have tried using the word null,null with quotes ‘null’, nothing or two quotes together and the to_date() function. The import data tool does not like the null values in the csv. I get an error stating the Data is not compatible with column definition or is not available for a not nullable column. The column is a DATE column nulls are allowed all the other dates work but the nulls do not.

    1. thatjeffsmith Post
      Author

      You need to remove the ‘null’ text from the column. Search and replace in your text editor of choice, or in SQL Dev when creating your CSV exports, make sure the preferences are set to have NOTHING printed for NULLs

      1. Hi there,
        Thanks for the prompt reply. The data looks like so: columnA,,columnC,columnD, Where the date field columnB is blank for some rows. The importer still does not like the empty place holder.

      2. thatjeffsmith Post
        Author

        Works for me – but you haven’t shown me your data or what exactly isn’t working. Just b/c the importer shows a funny pic on your NULL val, doesn’t mean it won’t happily import the data.

      3. Hi there,
        Yes in fact it does work fine despite the warning.
        I assumed it would not given the warning on the importer.
        I simply did not try and assumed it would not work.

        Thanks Again

  11. I need urgent solution to this error. I am a beginner in Oracle SQL developer, I am having issues importing to Oracle SQL developer. I created a table and save on my desktop as “test.xlsx”, and I also create empty table on Sql developer database. This is the error message i am having ” c:\users\desktop\test.xlsx cannot be opened due to the following error:org/dom4j/Namespace. Kindly assist me while this has been giving me concern. Thank you.

  12. Hi Sir,

    I would like to export data from table only 1 month data,How can I export using sql developer, I can export data,but all.

    Thanks.
    Best Regards,
    Theingi.

    1. thatjeffsmith Post
      Author
  13. @thatjeffsmith…I have sql developer version 3.1 and want to Import data from Excel(xls) file to an oracle table.the file has 65k records.able to go till last screen and verified parameter after that when loading process started sql developer get hanged and I waited for 30 min it still hanged and no data imported into the table.
    Please help me

    1. thatjeffsmith Post
      Author
      1. I am working on client environment and I do not think client would be ready to upgrade. is there any other way,I can do it?

      2. thatjeffsmith Post
        Author
    1. thatjeffsmith Post
      Author
  14. Hi, I have many excel sheets which all have certain alphanumeric / numeric value which can be distinguished. I want to only import those values/keys from excel to Oracle or any db. Please note that the value/keys to be imported from multiple excel sheets and have varied rows and columns but the defined value/key will remain same. These values imported from each of the excel sheet is a unique record thus would be parsed in db for further analysis.
    I sincerely request for your help on it.

    1. thatjeffsmith Post
      Author
  15. While importing data in .csv or .tsv format in Oracle SQL Developer 3.0.04, I am getting the bellow error:

    o.d.r.data.writers.ImportGenerator$SchemaTaskListener Task rollback.

    Please help to resolve it.

    1. thatjeffsmith Post
      Author
  16. Hello Jeff,
    I appreciate your follow up comments!

    Here, i need your help. I’m uploading a CSV file which runs a job and insert the entries from CSV file into DB. Later give me the required details.

    But it’s not inserting the complete row given in CSV.

    For.e.g.
    1. I gave CSV with 76 K rows and got data for only 15K rows
    2. I gave CSV with 144 K rows and got data for only 28K rows
    3. I gave CSV with 6K records, here it worked fine.

    Looking forward for update on this.

    Thanks!

    1. thatjeffsmith Post
      Author

      do the import again, but set the method to Script.

      take the script that’s generated – and run it in the worksheet

      observe why the rows are not being inserted – and address the issue(s)

      1. I’m sorry but i have no idea how can i change the method to script here. Actually as you saw third condition worked fine, because count was 6K. If it will be more than 6K, a batch job will run.

        Then it only takes first few rows and insert in DB. Please help.

        Thanks!

      2. thatjeffsmith Post
        Author
      3. I’m sorry, i think, issue explanation was not enough earlier. It’s already developed application and we are not doing the manual import/export from sql developer.

        We have an option in app to take input csv file, then its insert the data automatically (by code) after that in DB and generate a output CSV with required details.

        I.e. Here when i give input csv file, its only read first some row and also while exporting, it doesn’t give complete required details.

        Please help.

      4. thatjeffsmith Post
        Author
      5. No, no help in code.. I Just need possibilities which can cause to get only some first data..anyhow we have analyzed the issue & will be fixed soon..Thanks for your help & time.

      6. thatjeffsmith Post
        Author
    2. Hello,
      I am getting the following error in oracle sql developer after clicking the finish button in the data import wizard.

      Task canceled and import rolled back. Task Canceled

      Can you please help me

      1. thatjeffsmith Post
        Author

        did it open a log file with details after the import?

        set the import method to INSERT SCRIPT – and it will build just that, and you can run it yourself and see if and when there are any problems

      2. Thank you, I used Insert script option, but the problem was with the Xls file. I converted the file to csv format. then it worked

  17. Dear Jeffsmith,

    I am having data around 108 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me, My sqldeveloper is getting hanged. i need to do ASAP.

  18. Dear Jeffsmith,
    I am having data around 50 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me. My SQLDeveloper is getting Hanged.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        I’ve done a million records before, with no problems.

        If you’re doing 10M+ records, suggest you save your Excel file as CSV and use SQL*Loader or an External Table to load your data.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        What does status started means.
        For one test “Checking data against column size” I am constantly getting status as “STARTED”.
        Please help me.

  19. You saved me from having to write the insert statements between the columns of the spreadsheets, export the text, verify it and only then execute.

    My xlsx files were created by SQL Developer itself, so this task became even easier. Thank you very much.

  20. While importing sdo_geometry data from excel to oracle db using SQL developer tool getting below error
    ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR.

    Please help anyone on this.

    1. thatjeffsmith Post
      Author
  21. Hi,

    I’m trying to import an excel spreadsheet into a table. The spreadsheet contains string values of “TRUE” or “FALSE” in few columns. When generating insert statement SQL Developer converts them to lower case. Is there any way to disable this “feature” and import as is? The version I use is 4.1.3

  22. I am trying to use sql developer 4.0 to import excel which has a large text field, on the first popup page of the Data Import Wizard (step 1 of 5), it brings up the preview of 100 rows with no problem, but when I try to click ‘Next’, nothing happens, it won’t go to the step 2 page.
    Based on my research, I have changed the conf file by adding the
    AddVMOption -Xmx1024M

    But no luck… Any suggestions? Thanks.. Rick

    1. thatjeffsmith Post
      Author
      1. Version 4.1.2.20…
        40k records, one field contains around 3000 – 4000 characters… the others are small items (like first name, date…)
        For now, it’s working only if the excel file contains around 100 records..

        Thanks, Rick

      2. thatjeffsmith Post
        Author

        that’s a big file, or a lot of data to process

        suggestions:
        add 3-4 GB of RAM for the JVM – make sure you’re making this config change in the product.conf file OR save your Excel file as a CSV and try again

      3. Thanks for the help.
        After changing the conf file (sqldeveloper.conf under bin folder), I had tried with xlsx with 2000 records, still not working.

        Tried with CSV (with | as the delimiter), but the large text item contains multi-lines of text for one record. The sql developer cannot read it well… Any solutions for situation like this?

        Thanks, Rick

  23. I have an existing table with millions of records. Recently we added two fields to the table. Now we have a million of records which we need to insert into those two new fields using Employee ID as key constraint. I data for those two columns is in excel. How can we utilize Oracle SQL Developer to import the data into those columns using Employee Id as Key constraint.

    1. thatjeffsmith Post
      Author

Leave a Reply

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