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.

343 Comments

  1. Hi Jeff:
    I’m trying to import an Excel into an existing table T1.
    – Excel has same columns as T1.
    – Excel has a column called DATE1 with no values.
    – T1 is not an empty table.
    – T1 has a column called DATE1 which is NOT NULL which means, it contains values.
    I followed your instructions, but when getting to the step “Column Definition” the “Format” textbox is enabled. However, the “Nullable” checkbox and the “Default” textbox are disabled. How can I let SqlDeveloper know that I want to use SYSDATE as a default value for the new rows from the Excel? I know there’s SQL*Loader or PL/SQL Developer, but is this possible using SqlDeveloper? HOW? Thanks in advance.

  2. Hi Jeff,

    what s the max limit to use the Data import functionality.
    I have an xlsx file which is ~ 48 MB having 183K rows.
    When I try the Data Import and select the File I get The Heap space issue.

    ANy Pointers to tackle this.

    • thatjeffsmith

      You can either add memory (a lot!) to the JVM for SQL Developer, or you can save your xlsx file to CSV and Import that.

      XLSX files are really just XML…it takes a ton of memory to parse large spreadsheets.

  3. Hi Jeff,

    I followed the same process to import a CSV into my Oracle database. I received a “missing left paranthesis” error.

    — Import Data into table systemproceeds from file C:\Users\RegCorp 95\Documents\01RegistrarCorpJAN\split\01RegistrarCorpJAN_chunk1.csv . Task canceled and import rolled back. Statement Failed: CREATE TABLE systemproceeds ( ENTRY_LINE VARCHAR2(26),
    ARVL_DATE VARCHAR2(15),
    SBMSN_DATE VARCHAR2(9),
    FDA_DISTRICT VARCHAR2(25),
    COUNTRY_OF_ORIGIN VARCHAR2(2),
    PRDCT_CODE VARCHAR2(7),
    PRDCT_CODE_DESC_TEXT VARCHAR2(31),
    FEI_NUM NUMBER(10),
    MANUFACTURER_LGL_NAME VARCHAR2(128),
    LINE1_ADRS VARCHAR2(33),
    LINE2_ADRS VARCHAR2(12),
    CITY_NAME VARCHAR2(30),
    STATE_CODE VARCHAR2,
    ISO_CNTRY_CODE VARCHAR2(2),
    FEI_NUM_1 NUMBER(10),
    FILER_LGL_NAME VARCHAR2(128),
    LINE1_ADRS_1 VARCHAR2(17),
    LINE2_ADRS_1 VARCHAR2,
    CITY_NAME_1 VARCHAR2(9),
    STATE_CODE_1 VARCHAR2(2),
    ISO_CNTRY_CODE_1 VARCHAR2(2),
    PNDNG_TEXT VARCHAR2(17),
    FNL_DSPSTN_ACTVTY_DATE VARCHAR2(15)) ORA-00906: missing left parenthesis

    Please help

    Thanks,
    Sabarish

    • thatjeffsmith

      you forgot to specify a size for LINE2_ADRS_1

      Also, please please please don’t store dates as a varchar2 – use the DATE data type

  4. Hi Jeff!

    I like to try another time.. and tell you my problem.

    At work my boss wants me to collect requirements for a program.
    The program should check and import (insert, update and delete) data from a (Excel) table in the program to the Oracle database table. The Background of this is that the specialist Department regular send requests to the IT Department to make a update of his data (more than 400 data records; to much Manual work)
    These are the steps:

    1. Login at the application
    2. Request to the IT Department (request, which table?, how many data records?…)
    3. Which columns should have the table?
    4. create a blank table with the scheme of the Destination table (Access to the Destination table and get the needed metadata to create a table which has an identical scheme to the Destination table)
    5. edit the table in this program (edit Manual or copy and paste) and fill the table with the data from the Excel list
    6. check the data
    – technical test (Primary key not null? correct data type? correct size of the data type?)
    7. upload the table to the Oracle database table

    My Question now is: How can I realize the upload from the table in our program to the Destination table in Oracle?

    I would love to get a response.

    • thatjeffsmith

      I think you should look at building an APEX application.

      Then your user can manage their data directly, online. No need for Excel.

      SQLDev can do imports to a table based on Excel, but not updates or deletes.

    • Hi Jeff,

      it’s not my task to work with an already existing application.
      I have to garner ideas for the need of an new application.
      And I got almost all ideas, but there is one point I don’t know what to do.

      4. create a blank table with the scheme of the Destination table !! maybe I can realize this with Java Jtables?
      5. edit the table in this program (edit Manual or copy and paste) and fill the table with the data from the Excel list
      ..
      7. upload the table to the Oracle database table ??

      Have you got an idea? Thanks.

    • thatjeffsmith

      We wrote our own code to build a table from an Excel file and so has APEX.

      I think you might end up doing the same.

  5. Hi Jeff,

    in your video you import data from your excel sheet into a new table in your database.
    Is it also possible to import data in a already existing table with a lot of data records?

    Thanks, Swantje

    • thatjeffsmith

      Just scroll up this page – I show you how there.

      Or short answer, yes. Right click on your table and use the Import item.

    • Thanks. And is it also possible to update or delete data records? Not just to Import data…

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

    • thatjeffsmith

      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.

  7. Raj Ramakrishnan Reply

    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?

    • thatjeffsmith

      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.

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

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

    • thatjeffsmith

      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.

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

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

    • thatjeffsmith

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

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

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

  13. Pawandeep Singh Reply

    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

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

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

Write A Comment