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 Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

354 Comments

  1. Hi, I am experiencing the same error mentioned by Isingh.

    When I tried to import data using excel to sql server using import data option, in the final step the status shows as ‘STARTED’. I couldn’t complete the import. thanks for your help

    • sorry, I mean to say am facing the above error in Oracle SQL database while importing data.

    • thatjeffsmith

      I have the same advice for you as I had for Insigh then. Try that and let me know. Also please confirm the version you are using. Hopefully it’s not 4.0.1 or 4.0.2.

  2. Jagtar Singh Reply

    Jeff, this is exactly what I was looking for.
    Thank you for your great work!

  3. When I do Import Data from SQL Developer, all it works fine, but getting an issue when a cell in csv has the data in multiple lines. Error shows as below
    “Line contains invalid enclosed character data or delimiter at position ”
    Same issue when in either of the cases “Insert” or “Insert Scripts”
    Is there anyway to handle this issue in SQL Developer.
    It would be a great help answering this…:)

  4. IS IT POSSIBLE TO IMPORT excel files into sql which is having null values ,null column and null rows ?

    • Hi,

      I encountered an odd problem during import of data from excel. I work with PL/SQL Developer 10.* on my laptop, accessing remote oracle server. So far I haven’t faced a problem during import from excel, till yesterday. I tried to import 40K IDs from a single column to a temporary table in the base, made for this reason, with the same header name , only with one column and so on Which I have done it many times. But when I started the import an error message appeared, which was like this one – “You tried to insert a “NULL value into a column that does not accept NULL values”

      I think it was this error: http://www.techonthenet.com/oracle/errors/ora01400.php

      In this moment I thought that there are blank rows in the excel, because I removed duplicates with the function in excel, which deletes the data only from the cells, not the rows. So I checked again, deleted the blank rows below the data, clear everything blank, but still nothing. In the error message box was a button “skip” (during the import), every time when I was able to press the button, I could see different indications – 66 records successfuly imported, 54 records …, 32 … and so on (on portions)

      I didn’t understand what was happening. I tried to move the data to another excel file with paste special, but nothing changed when I tried to import the data from the new file.

      The data was in text format, the last thing what I tried, was to convert the data in to a number. And after this move the import was successful without any problems. I cannot explain to myself why this happened, where is the reason?

      And one more thing, strange again – the records in the file were 29444, after import I saw indication that I have 29444 successfully imported records. But when I checked the table in the database, there were 30560. Which extra confused me. Before the import I intentionally deleted the old data in this table, and it was blank.

      Regards’
      Emil

  5. Hi, I want to import excel sheet in already exist table using oracle SQL, but i cannot see my table under connections tab. As i have very limited access to DB, but the table in which i want to insert excel sheet, for that table i have read, insert, update , delete access. Can u please help me, to insert excel in this already exist table, as i don’t want to create any new table in DB. Looking forward for your reply.

  6. sir how to create a table by uploading a csv file directly in oracle

  7. hello brother iam using latest oracle sql developer when i want to try to import my excel sheet its not working .i mean when i right click on my oracle tables it ‘s not enabling i cont get the import opctions what type of settings i want to change plz tell me

    • ya i all ways connected to oracle cloud database .

    • services means .iam using latest sqldeveloper 4.1.1 something i am using for oracle bi cloud services

    • thatjeffsmith

      BICS runs on the schema service, we don’t support direct excel to oracle support for the schema service. You have to use the cart to upload your data.

  8. Hi,
    I am trying to import data from an excel file to a table in oracle database. During Verify stage I am getting error saying “Source columns , do not have data types assigned”. Though I have assigned datatypes for all the columns in table. Here source columns means our excel or table?. Please help. I am using sql developer 1.5.5 version and oracle 11.5g

    • Gurunadh

      Hi Smith,
      It might going to take time for me to upgrade to higher versions. Is import option has any issues in 1.5.5 version or could you let me know why I am getting that error if you have any idea. I understand that version is too old for you to help with. But if you could do any favour in this regard will be much appreciated.
      I stuck with this issue for a long while. After clicking on Verify I am getting error in of the check points saying “Source columns , do not have data types assigned”.

    • thatjeffsmith

      takes 5 minutes to install sqldev, I timed it

      download 4.1.1 with the embedded JDK

      unzip it

      run it – you’re done 🙂

      >>Source columns , do not have data types assigned
      So you’re creating a new table based on a spreadsheet. The error is telling you to that one of the columns doesn’t have a data type assigned to it. If you think you DO have this take care of, in the earlier part of the wizard, look for an option to ‘create script’ – this will generate code for the process and you can use that and skip the verify bit.

    • Gurunadh

      Hi Smith,

      Thank you so much for the help. After using ‘create script’, I realized my errors (There was an extra null column I was trying to import and many blank rows of data for which my table data types does not match hence giving the error).

      Thanks once again for your valuable inputs.

  9. Thank you Jeff…… 🙂
    This is much better than video tutorials.
    -Dhiraj

  10. Hiii.Thanks a lot for such good information.I tried inserting data from .xlsx file to sql tools table.The only problem that i seem to get is that my .xlsx file has 1580 rows and this method just imports 50 rows. When i tried your method , the values in preview row limit and import row limit were 1580 only.Still i get just 50rows imported.

  11. Not sure if i’ll be able to get that upgraded, but i’ll try. Thanks for your help.

    • thatjeffsmith

      yeah, that’s pretty old. I wouldn’t be surprised if that’s just a bug from 2010. Can you upgrade to version 4.0.3?

    • Well..unfortunately, the latest i can go right now is 2.1.0.63 due to certain limitations at my work place.

    • thatjeffsmith

      Understand that. But also understand your software is beyond old and supported at this point. Maybe you can use this as a valid reason to refresh machines there.

  12. I am trying to import data from a csv file to a table using Oracle SQL developer. However, in doing so, an additional single quote gets added whenever there’s an apostrophe in the string. For example, a string *He’s* in the csv gets loaded as *He”s* in the table. The column type is varchar2(200). I am using version 1.3. Any idea what i can do differently to resolve this? Thank you.

  13. hello jeff smith Reply

    i am unable to load data through this process in sql developer.It is not showing me any kind of FAILED verify parameter, all process are ok. but still data not coming in my table.. i have one table in sql developer containing 52 column and exel sheet containing data with 52 column. please help me…

    thanks in advance.

    • thatjeffsmith

      set the wizard to generate an INSERT SCRIPT instead – see if that gives you any code or hint as to what is going wrong. Or try the 4.1 Early Adopter and see if we handle your data any better there

  14. Jeff:
    I’m using SQL Developer to import an Excel 2003 formatted spreadsheet into an Oracle 11g database table. Unfortunately it’s giving me a FAILED verify parameter and doesn’t tell me which column it’s referring to.
    How can I handle the following verify parameters FAILED message in SQL Developer:
    Data Types BINARY_DOUBLE, not supported for import

    There is only one spreadsheet column formatted as Number, two columns that are formatted as General, and three columns that are formatted as Text which contain numerical data. Thus, I assume the FAILED message is referring to the column formatted as Number.

    Thank you.

    • thatjeffsmith

      find the column in your table of datatype ‘BINARY_DOUBLE’ – remove the column in your worksheet that’s mapped to that table column

  15. shital kadu Reply

    hellow jeff smith..
    i am unable to load data through this process in sql developer.. please help me…

  16. Thanks alot…Its helps me alot to import the data……….Cheers 🙂

  17. Volker Klös Reply

    Hallo Jeff,

    i try to Import Data from an CSV-File or excel and have some problems with my implementation ( on an window-xp PC, Version 4.0.3.16, Java(TM)-Plattform 1.7.0_71, German Menu’s).

    1.) I can not select the “Begrenzungszeichen” / Separator. The Comma is fix (this means the Selector is gray and can not be used).

    2.) If i convert my csv-File to an excel-Spreadsheet, there is an empty Screen after i select the Excel-File to import.

    Is this effekt known and how can i handle this.

    Regards

    Volker Klös

    PS.: I have another effekt, starting Packages with Constant-Parameter, which i can show by Screenshots.

  18. Jeff ,
    i have table where one column is generating using sequence i.e its primary key .
    i want to import data from excel file to this table so how can the auto generated data will come in this primary key column ?

  19. Patricia Zegarra Reply

    Thank you very much Jeff!
    It worked for me 🙂
    I was exactly what I was looking for.

  20. Jeff,
    I have tried to import 679K records via SQL Developer. I exported XLSX file into CSV format. Then, I created a new file with 100K records.
    SQL Developer froze and stopped working.
    When I decreased to a number of records to 10k , it worked.
    Should I increase a memory ?
    I was able to load a file via Toad Data Point from a CSV file but I still would like to discover a problem the prevented me from using SQL Developer.
    I can email you the dataset.
    SLQ Developer Version: 4.0.3
    Thanks
    Chris

    • thatjeffsmith

      I’m guessing for most the Excel won’t work for very large files like that – our library used to read in Excel files doesn’t do a great job of releasing memory as records are written out. But switching to CSV should pretty much just always work.

      You could try bumping the JVM memory space up. And feel free to send me your CSV at [email protected]

    • Sent everything via email.
      Thanks again.
      Chris

  21. Hey Jeff,

    Thanks for the tutorial!

    It doesn’t appear that the import takes records in the order they exist on the Excel spreadsheet. For instance, we have a spreadsheet where entries are added to the bottom as they occur, but there is not a time stamp associated with their addition. In order to see if Person B was added before person A, you simply look to see if person A is on the list before or after person B. Once I’ve imported the data into SQL Developer, the records are out of order. Is there a way to import them exactly in the order they are in, or will I need to just include a column in my spreadsheet that is numbered and sort that way with SQL Developer?

    Thanks for your help! 🙂

    • thatjeffsmith

      I’m betting they are inserted ‘in order’ – you can check the log to see the INSERTs that are run and confirm/deny that. However, row order isn’t guaranteed for tables in an Oracle Database. You’ll need to add a sequence or date field to preserve order if that’s important to you.

    • You’re right, they are inserting in order, but when I view the records using the Data tab, they appear out of order. I’m assuming there isn’t a method of viewing them in the order they were inserted as I only see a Sort option for A-Z and Z-A.

  22. Vishal Dixit Reply

    Thanks Its really great and helpful. Please keep me updated with such good tricks if possible.

Write A Comment