Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

How to Import from Excel to Oracle with SQL Developer

6 Mins Read

TL;DR – Skip to the video!

Hate GUIs, want to do this via the command-line?

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

The Movie

Author

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

Related Posts

359 Comments

  2. divya Reply

    Hi thatjeffsmith,
    Thank you so much for ur response.Can you pls explain me the steps to setup a sql*plus script to run via OS scheduler and use the spool command to capture the output.As i am beginner to the technology,i am not able to understand the tech terms clearly.

  3. divya Reply

    i want to run a sql query everyday at 5.30 pm.Cn u pls help me to do this automatically and place the output in a folder.i use only sql developer to connect to database.But my database does not have scheduler option ?Thanks in advance.

    • thatjeffsmith Post Author

      Every Oracle database has a scheduling option. DBMS_SCHEDULER or DBMS_JOBS. You could also setup a sql*plus script to run via OS scheduler and use the spool command to capture the output.

  4. imtiyaz Reply

    Randomly it caught my eye and now it is going to help me a lot…Thank you very much.

  5. phani Reply

    Thanks Jeff for the walk through.
    I walked along successfully till the end ( with date format issues ) and upon “Finish”

    Problem
    SQL Developer freezes.

    Environment
    I’m on Mac 10.7.5 with SQL Developer 3.0.04

    Is this the JVM settings thing ? I’m importing less than 10 rows (12 columns)

    Your help is much appreciated

    • phani

      Sorry forgot to mention … Not sure if this can have implications :

      Oracle SE server Instance is Amazon RDS

    • thatjeffsmith Post Author

      Question #1: Why are you using SQL Developer 3.0.04? Can you upgrade to version 3.2.20_09?

      Something you could try: have SQL Developer generate an INSERT script to the worksheet instead – that’s an option in the wizard. Then run that script to move your data into your table. If it hangs there too, then there’s something funny with your spreadsheet. Feel free to send me your XLS file if you want me to investigate further, [email protected].

    • phani

      First, i upgraded my SQL Developer to 3.2.20.09 Looks good.

      I got the INSERT generated earlier but to analyze or copy the IDE used to freeze immediately. Now i got the INSERT script generated and says

      “ORA-01722: invalid number”

      My XLS is having numbers in couple of columns like, 1, 610, 66945
      While importing during the Data Preview phase i noticed that the values are coming as floating points like 1.0, 610.0, 66945.0

      Then i made the corresponding columns formatted as Text and the Data Preview was all good.

      But the INSERT failed because those floating points came up again. Not sure if this is some very simple setting in my MS Excel 2008 for Mac

      Thank you very much for your response and your help in this regard.

    • Cyleste

      Phani – Did Jeff ever answer this. We ran into this last night when trying to import about 200 records into a payroll table. Same exact thing. I would really appreciate an answer if possible.

      Thank you. Great post.

    • thatjeffsmith Post Author

      Cyleste,

      If you have the wizard create a script instead of run directly against the table/database, what does the INSERT look like for one of the rows?

      Also, what is the data type definition for the table column in question?

  6. Buzz Reply

    Great tutorial,

    But no joy for me.
    I get a error message at step 8

    Any thoughts?

    –Insert failed for rows 1 through 50
    –ORA-04091: Tabel CRP.ITEM wordt gemuteerd en mag niet door trigger/functie worden benaderd.
    ORA-06512: in “CRP.RECORD_ID”, regel 33
    ORA-06512: in “CRP.RECORD_ID_TRIG”, regel 4
    ORA-04088: Fout bij uitvoering van trigger ‘CRP.RECORD_ID_TRIG’.

    • thatjeffsmith Post Author

      The easiest solution would be to disable any INSERT triggers you have on the table. The problem is with the triggers, not with SQL Developer. I’m guessing if you used the SCRIPT vs INSERT option, and ran the script via SQL*Plus you’d see the exact same errors. Is there a DBA you can consult?

  7. Chaitanya Reply

    Hi,

    This is really useful info. I used this as it is. Thanks much

  8. Waheed D Reply

    Hi Smith,

    I have a samll query, is there any row limit when we try to import the data from excel file. Cause I’m trying to insert 10k records but the process is rolling back.

    • thatjeffsmith Post Author

      What does the log show Waheed? Is SQL Developer freezing/becoming unresponsive during the wizard, or are you getting an error message? Please tell me what you are seeing.

      When importing large amounts of data via Excel, you may need to increase the amount of memory available to SQL Developer via the JVM – that would be the solution if the application becomes non-responsive. Let me know and I’ll send you the directions if necessary.

    • Waheed D

      the message displayed is “Import Data into table TABLE_NAME from the file.xlsx. Task failed and import rolled back.”, I see that the issue is cause because of the erroneous data in the file now it’s resolved

      Thank you very much for your response. 🙂

  9. Najma Reply

    Very nice explanation in this post. I have a question, I am using excel 2007 and created a small file with few rows. Each row has data which is linked with other worksheet in same files. During import in SQL Developer, I could not see any row in the table which has linked data with other sheets. I used past link special option here. But if I am using simple data row (without paste link special option) then Import is fine. Any idea how can I fix this problem in file with paste link special option? I will appreciate.

    • thatjeffsmith Post Author

      I’m guessing that’s a failing of the POI Apache library we’re using…which might get updated soon.

      In the meantime, you could work around this by saving your XLS file to a CSV file and do the import from there – which should run even faster.

    • Najma

      Thanks for quick response. I appreciate your response.

  10. Mark McCloskey Reply

    Thank you for this webiste!
    I would like to know if I can change the defualt path on the “Import Data” menu option after right clicking on a Table.
    I am using version 3.1.07 or Oracle SQL Developer.

    Thanks for your time.

    • Mark McCloskey

      Dang. Sorry for the typos. “default” path, and “of” Oracle SQL Developer.

    • thatjeffsmith Post Author

      Yes – and don’t mind the typos!

      Tools > Preferences > Database > Utilities > Import > Default Import Directory

      It’s not like we buried it, or anything 😉

    • Mark McCloskey

      Thanks for the quick reply!
      Right after I submitted the question, I noticed the list of folders on the left that pops up after clicking on the Import Data option and one of them is the folder that I wanted to be my default…so I may not even need to change the default.

      I appreciate your response.

      Mark

  11. Rich Reply

    I have run into an issue at times when the Data Import Wizard will not deploy. The File-Log section will show that a file is open but the wizard is not displayed. Any ideas as to why this occurs? Thanks!

    • thatjeffsmith Post Author

      Are you working with an XLS or XLSX file? If so, how big is it?

      Could be we’re running out of memory – an issue we know about and are working on.

      You can launch sqldveloper from the bin directory and get a command console to see any error messages that are occuring when you get to that step…or you could just try to increase the amount of memory for SQL Developer in the sqldeveloper.conf file by modifying this line
      AddVMOption -XX:MaxPermSize=256M

      Or you could try saving your xls file as a CSV and trying that – no memory issues with CSV files.

      Or it could be something completely different 🙂

    • Rich

      Increasing the Max Perm size to 512 seems to have resolved the issue. Thanks for your help!

  12. Sherri P Reply

    Thanks! This was great! The only other thing I ran into is that my numbers had commas in them in the csv from when I converted from xls, so I had to modify the csv accordingly. Once I changed the format, it worked great. You saved me a ton of time. Thanks again

    • thatjeffsmith Post Author

      There should be way to get the commas out of the source data…let me know if you’re interested in figuring that out.

      Thanks for sharing your experience with us!

    • Sherri

      I did get the comma out of the source because I changed the format for that column, but thanks.

  14. Yas V Reply

    I have inner joy at finding your blog, I’m a big fan of sql developer, I use a Mac at home. I am quite new to Oracle so your posts are very useful. I used to be an avid Excel person but after learning just the basics of sql I know I won’t be using Excel in the same way ever again.

    I’ve just spent nearly two days trying to get 11g2 working on ora6 via virtual box. So your post on getting sql developer working on the Mac was very welcome for my fried brain.

    Keep up the good work!

  15. Alex Reply

    In trying to follow your example, I cannot proceed past step 4. The “Import Method” list is empty, so I can’t choose Insert or Insert Script. When looking at Tools->Preferences, the Database->Utilities->Import->Import Methods setting shows “Insert” as the default value for both Existing Table Import Method and New Table Import Method.

    Any idea why the list would be empty, and how I might correct this?

    • thatjeffsmith Post Author

      I’m sorry to hear you’re having problems Alex.

      A few questions:

      • What version of SQL Developer are you running?
      • What OS are you running on?
      • What version JDK (java) are you using for SQL Dev?
      • What kind of file is your data being read from? Excel, Delimited?

      If you’d like, you can email me your data file and I can try to reproduce the problem here locally – [email protected]

    • Alex

      SQLDeveloper 3.2.20.09.87
      Windows 7
      JDK 1.6.0_37
      Excel file — but it also happens with a regular plain text CSV file.

      I don’t think it has anything to do with the particular data I’m using. I was able to successfully import the same XLS file with dbVisualizer. I also created a dummy csv file with 2 rows of data in it — and that yields the same results (nothing in the import method list) as trying to use the Excel file.

      I’ve even tried importing the data into a different table (in the same schema), and the list is still empty.

      Do I need a certain set of permissions on the Oracle database for this to work in SQLDeveloper? I have a limited-access user id where I can query/insert/delete data but not perform any schema-relatated activities (add/drop tables or columns, etc.). I wouldn’t expect this to be the case, because dbVisualizer works correctly…

    • Q

      I’m having the same issue with SQL Developer 4 on Windows 8.1 64bit. I have two Oracle11g instances. One of them is fine when using the import wizard. The other one has the issue as Alex mentioned. The two instances are basically the same. One is lower level, the other is production. Do you think this is related to permission? Thank you!

    • thatjeffsmith Post Author

      I’d be surprised if it was a privilege issue, but that it works on one and not the other makes me wonder…if you run SQL Developer in debug mode and open the log panel, you can see the queries run across the wire as you run the wizard.

    • Q

      Thanks for your reply, Jeff!
      The problem instance gave me an error “Could not get schema Object:java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist”.
      Instead of digging into this error, I downloaded SQL Developer 3 and it worked. I know it’s not the best solution but it works for now. Thanks so much for your help!

    • thatjeffsmith Post Author

      Some notes from the developer –

      Reading through the code, the only thing I can think of is that maybe his preferences are messed up somehow.

      If there are no errors in the console, the first thing to try is blowing away the preferences.

      Depends on platform, but basically they are in “.sqldeveloper” in the ‘home’ directory.

      check Help->About->Properties to find out where they are
      ide.pref.dir C:\Documents and Settings\user\Application Data\SQL Developer

      Easiest is to delete the directory (export connections somewhere safe 1st)

      BUT if they have multiple versions installed, need to find directory under .sqldeveloper that corresponds to version (e.g., system3.0.03.01)
      then delete at least settings.xml from the 0.ide. directory

    • Barnabas Sipos

      Dear Jeff,
      the “import method dropdown empty” problem exists in the 4.0.2.15, build 15.21 too. No multiple versions.
      I did the preferences cleanup, it does not help.

      However I could find the way to reproduce and get rid of it:
      if You do the import with plain user rights, the list is empty, even on your own table.
      If You do that with sysdba rights, the dropdown is working fine.

      Hope it will help.

      Best regards,
      Barna

    • thatjeffsmith Post Author

      We don’t actually run any queries from what I can tell when running the wizard at that point…can you try our Early Adopter, v4.1?

    • miira

      Hi, I had exactly the same problem. I tried sqldeveloper v 4.1 (4.1.0.18.37) with java JDK 1.8.0_45 and now the dropdown is finally not empty. 🙂
      Thank you

    • Barnabas Sipos

      Dear Jeff,

      Sorry for the delay, had a lot of travel to clients abroad.

      Thank You for Your help, with version 4.1 I can confirm that the problem is solved.

      Best regards,
      Barna

  16. Nilam Reply

    Hello, I am trying to import Data from Excel file to Oracle table. I am using import available in sql developer, but i need to do on daily basis. any ideas, suggestions. please help. Also, pls guide how to use the SQL Loader ? Many thanks, Nilam

    • thatjeffsmith Post Author

      Get it into a SQL*Loader friendly format, like CSV. Then you’ll want to set it up to run in SQL*Loader. That’s an option in the Import wizard. After you’ve stepped through it you’ll be left with a set of files including a script. You’ll want to setup an OS job to run that script on a daily basis, making sure that you also have scripted the daily input file to be placed where the job is going to look for it. I have a post on how to use SQL*Loader with SQL Developer – it’s on my ‘Popular Posts’ widget to the right of the blog content.

      You’ll want to read up on SQL*Loader in our Oracle Docs as well.

  17. Sandy Crofoot Reply

    That did the trick! I saved it as a CSV and loaded it without additional problems..I only had 260 rows on the spreadsheet to insert.
    Thanks for your help,
    sandy

    • thatjeffsmith Post Author

      Glad to hear that worked, but a bit concerned we couldn’t handle your XLS file. If the info in it isn’t of a secure nature, feel free to email it to me at [email protected] so I can debug what’s going on and make sure it’s fixed going forward.

  18. Sandy Crofoot Reply

    I’m on sqldeveloper 3.1.07 and using Office 10. I successfully complete the process through the verificaton step (with all successes), click finish and then sqldeveloper either freezes up or I get a popup box that says: Import data into table SWBDEPT from file CampusDirectory.xls . Task failed and import rolled back. It doesn’t give me any more information than that. I’ve tried using both .xlsx and .xls formats with the same results. We are on oracle 11g. Any Ideas?

    • thatjeffsmith Post Author

      My first idea is that the JVM memory is being exhausted. Reading in the data from Excel is kind of expensive today in SQL Developer. How big is your spreadsheet, number of rows?

      As a test, save your XLS as a CSV and try the import. If it works right away, that’s the issue. The temp fix is to increase the memory in our sqldeveloper.conf setup/config file. The long term fix involves an update internally on how we work with Excel – which is coming!

  19. Tony Reply

    Hi, I hope you can help me. I’ve just started a new job and they are using the Oracle system. Blanket purchase orders are sent out to the supplier with required delivery dates. On a daily basis requests for quicker delivery dates are generated by Oracle. The requests can be downloaded from Oracle into excel and sent to the supplier.
    I need to know if the supplier replies with a new improved delivery date in excel is it possible to upload the new delivery date directly into oracle from excel ? I have asked the people in my department and they have informed me it’s not possible and I have to change the date manually within Oracle. However this is a long process and I would have thought an upload would be possible.

  20. ManikJ Reply

    Hey,

    So I was following this. I’ve just been coming to grips with Oracle for an internship.

    I get a ‘ORA 00928: missing SELECT keyword’ error for every record and nothing gets inserted into the database. I cleared the date values and matched columns.

    According to Oracle Help, this happens when I’m inserting into a view without selecting. But here, quite obviously, I’m inserting into a table.

    Any ideas?

    • thatjeffsmith Post Author

      Change your IMPORT method to ‘Insert Script.’ When the script is loaded into your worksheet, run it with F5. Then we can see exactly where your ORA-00928 is coming from.

    • ManikJ

      Turns out I was running an older version of SQL Developer. Switching to a newer version magically fixed that.
      Also, is there any way of directly importing DBF files in a similar manner?
      The only way I’ve figured out till now is opening each file in excel, saving as xls/xlsx and then doing this. I have multiple files and was wondering if you could point me in the right direction. Writing a script maybe?

  21. CO Reply

    I need to convert some legacy VFP6 data files. I know we can use Excel to convert it to *.csv files. But, how we convert data from Excel *.csv files into brand new Oracle tables (from scratch)? I am new to Oracle, so be kind with me. I need a very basic, step by step sample.
    Thanks

  24. Scott Reply

    This is very informative.
    I have a question though. How to do a weekly import with out going through all the steps? Automatic importing , I should say.

    • thatjeffsmith Post Author

      First we walk, then we run.

      Something we’re kicking the tires on is a more powerful command-line interface. This would allow you to call SQLDev features via your scripts, which you could then schedule via your native OS scheduling interface.

      If it’s a regular activity and you want to automate it today, something you may want to look at is using SQL*Loader. Until we have what you want in SQL Developer, you could define your ctl file for your Excel data – which would need to be converted to a CSV, then you could achieve what you want.

      Thanks for the feedback Scott!

    • Scott

      Thanks for the reply!
      I hope this is not a lot to ask but would you mind walking me into the process?
      What should I do first? I’m not well learned with oracle/sql stuff.

    • thatjeffsmith Post Author

      It may require a bit more coding than you’re comfortable at this time, but if you’re willing to to a bit of research, it WILL work. Try reading this FAQ first to get an idea of what’s involved. http://www.orafaq.com/wiki/SQL*Loader_FAQ

      Once you define what the data stream looks like, you can use windows or *NIX scheduling to run your script on demand. And it will be FAST.

      There are MANY utilities out there for loading Excel data to Oracle. They are not free.

      This process will be free, but it will require more work.

      And the process will continue to evolve and get better in SQL Developer of course!

    • Scott

      Thanks a lot! I’ll read and study more and hopefully get a sufficient knowledge to tackle this.

      * Programming is fun when you know what the heck your doing! lol.

Write A Comment