TL;DR – Skip to the video!

THIS is your number one question – and it has been here on my blog since the day I posted it.

You have an Excel file – and you want that data put into a table. I’ll show you how, and we’ll document each step of the way with plenty of pictures.

You will be prepared to import data to an existing table from Excel with confidence after reading this post. Want to build a new table from Excel? We can do that, too.

Warning: This post has a LOT of pictures.

For our example I’ll be using the HR.EMPLOYEES table to create the XLS file for our import. We’ll use that Excel file to populate an empty copy of the EMPLOYEES table in another schema.

Step 0: The Empty Oracle Table and your Excel File

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

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

Step 1: Mouse-right click – Import Data

Step 2: Select your input (XLSX) file and verify the data

If you’ve ran this wizard before, you can pick files from previous sessions.

As you select the file, we’ll grab the first 100 rows for you to browse below. This ‘Preview Row Limit’ defines how many rows you can use to verify the IMPORT as we step through the wizard. You can increase it, but that will take more resources, so don’t go crazy.

Also, does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. If you uncheck the ‘Header’ flag, the column names will become a new row in your table – and probably fail to be inserted.

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 3: Create a script or import automatically

As the wizard progresses, we’ll keep the File Contents preview panel handy so you don’t have to alt+tab back and forth from Excel to SQL Developer.

For this exercise the ‘Insert’ method will be used. Each row processed in the Excel file will result in an INSERT statement executed on the table we’re importing to.

If you choose ‘Insert Script’, the wizard will end with an INSERT Script in your SQL Worksheet. This is a nice alternative if you want to customize the SQL, or if you need to debug/see why the ‘Insert’ method isn’t working.

Working with CSV? You’ll get even more methods – great for VERY LARGE data sets.

Step 4: Select the Excel Columns to be Imported

The wizard defaults to all of the Excel columns being used, in the order they’re found in the file.

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 modify the column order, which may make the next step a bit easier.

Step 5: Map the Excel Columns to the Table Columns

The left panel represents the columns in the XLS file. The information on the right shows where that data is going, and how it will be treated.

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.

And remember how we set that preview window to 100 rows? We’re peaking at the data, looking for problems as we try to fit it into your table column. If we find a problem, we’ll mark the columns with those ‘warning’ symbols.

I’ve polluted my Excel file with some values that I know won’t ‘fit.’ When these rows are encountered in the wizard, they’ll be rejected by the database – but the other rows will come in.

Let’s talk about DATES for a second.

And TIMESTAMPS too. In the excel file, you’re probably going to have some date/time fields you want to move into DATE or TIMESTAMP columns. SQL Developer is treating those value as strings – and YOU need to tell SQL Developer the DATE or TIMESTAMP format to use to be able to convert them.

Let’s look at HIREDATE.

That ‘ha ha’ value will never make it in as a HIRE_DATE value – unless you’re storing DATES in a VARCHAR2 – and if you’re doing that, you’re doing it WRONG. Always store DATES as a DATE!

See the ‘Format’ drop down selector? SQL Developer has defaulted the DATE format string to ‘DD-MON-RR’ – we try to guess based on the rows we’re looking at in that 100 preview window.

If we have guessed wrong, or were unable to figure it out, you’ll need to input this yourself. The Oracle Docs can help you define the correct DATE Format Model. If you see the little warning graphics next to your Date values in the Data panel, you might have the wrong format.

Step 6: Verify your settings and GO!

The ‘Finish’ button will start the magic.

Click on the ‘Finish’ button.

If the Wizard runs into any problems doing the INSERTs, you’ll see this:

Remember that funny data i put into the Excel file? That’s causing problems now.

I’m going to say ‘Yes’ to ignore all the errors. But, if you need EVERY single row – you need to say ‘Cancel’ to start over. Then you can either fix your data in the Excel file, or make changes to your table so the data will fit/work.

After clicking ‘Yes’, we’ll get to the end of our story, and our data!

Step 7: See What Worked and What Didn’t

First, any bad news?

If there were rows rejected by the database, we’ll see those now.

Behind that dialog are the INSERT statements we tried to run, but didn’t work. You might be able to edit those manually to fix a few records. But if you have thousands of rejected rows – better to fix at the source.

Now let’s go look at our new table data!

I love the sweet smell of data in the morning!

Note the ‘Log’ panel. We’ll show you the file we worked with, and how much time it took to load the data.

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

thatjeffsmith
Author

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

339 Comments

  1. Julia Wiener Reply

    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.

    • thatjeffsmith

      >>and avoid duplicates?
      The easiest fix would be to create a primary or unique key constraint on your table. Then any duplicates would be rejected.

    • Julia Wiener

      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…

    • thatjeffsmith

      Use the import SCRIPT mode, that will generate the insert statements and then you can run them on your own.

    • Julia Wiener

      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?

    • thatjeffsmith

      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.

  2. mehrdel ira Reply

    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;

    • thatjeffsmith

      Instead of one big Excel file, you’ll want lots of smaller one. And then you’ll do inserts into each table, in the order required by your relationships.

  3. thatjeffsmith

    Without seeing your excel file and table – my best advice would be to use the wizard to generate INSERT SCRIPT, and then fix that as needed.

  4. Tomkin Lee Reply

    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?

    • thatjeffsmith

      how are you mapping columns, by name or pos?

      also if you rename the excel column to something other than CNAME, does it go away?

    • Tomkin Lee

      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,

  5. 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.

    • thatjeffsmith

      What long message?

      Run the scenario again and have it generate INSERT statements instead. Then run that.

      Also, what version of sqldev are you using?

  6. Tomkin Lee Reply

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

    • thatjeffsmith

      The real data…not to be flippant, but if it’s not nullabble and you don’t have any data there, your data is incomplete.

  7. 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.

    • thatjeffsmith

      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

    • 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.

    • thatjeffsmith

      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.

    • 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

  8. 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.

  9. 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.

    • thatjeffsmith

      write a query in the worksheet – add a where clause predicate to filter out the data you don’t want – run it via F9. right-click on the grid, export to excel

  10. @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

    • Dhananjay

      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?

    • thatjeffsmith

      Try saving the xls as a csv and do the import with that.

      Also, theres no upgrade. You just unZip v4.1.3 to a new directory and then run it.

  11. Hello
    I need to import data by code can you help me with this
    thanks

    • thatjeffsmith

      No, but check the otn plsql forum and stack overflow, I know there’s plsql out there that can read/write Excel docs

  12. 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.

    • thatjeffsmith

      You’re going to probably need to write a custom program. Our tooling doesn’t give you the flexibility you require.

  13. 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.

  14. Ujjwal Tiwari Reply

    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!

    • thatjeffsmith

      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)

    • Ujjwal Tiwari

      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!

    • Ujjwal Tiwari

      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.

    • Ujjwal Tiwari

      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.

    • 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

    • thatjeffsmith

      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

    • Arumugam

      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

  15. 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.

  16. 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.

    • thatjeffsmith

      make sure you’re on version 4.1, and make sure you’re working with an XLSX file – 50,000 records should be no problem

    • Ok thanks. If i want to upload more than 1 lakh records. how i can proceed?

    • thatjeffsmith

      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.

  17. Dear Jeffsmith,
    I am having data around 30 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me,

    Best!!!

    • Neeraj Kashyap

      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.

  18. 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.

  19. 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.

    • thatjeffsmith

      that error is telling you exactly what the problem is. you’re trying to insert 22 characters into a field that’s sized for 21. make the column bigger, or make your data shorter

  20. 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

    • Did you try assigning a check constraint to the column?

  21. Rick Liang Reply

    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

    • thatjeffsmith

      what version of 4.0?

      make sure it’s at least 4.0.3 – lots of bug fixes between 4.0 and 4.0.3 – not to mention that current version is 4.1.3.

      how big is your xlsx file?

    • Rick Liang

      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

    • thatjeffsmith

      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

    • Rick Liang

      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

  22. 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.

    • thatjeffsmith

      Import the excel file to a temp table and then write an update statement to fill in the new columns for your table.

    • Hi,
      It TOAD it was an option to commit every records while loading from Excel. Is there similar option in Oracle Developer?
      Thank you.

    • thatjeffsmith

      No, but you could also use the INSERT SCRIPT method and insert your own as needed.

      But, how many rows are you importing?

    • Thank you for the prompt response! I don’t know yet how many records I will have to load. I guess it’s about 100,000 records. Just preparing myself for the upcoming tasks after TOAD is not available to me any more. I guess it’s not big deal 100,000 in one commit. Thanks.

Write A Comment