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.

359 Comments

  1. Hi Jeff, when i try to import excel file (.xlsx) more than 30.000 data into one table, only 1500 data successfully imported, any miss step that i don’t do???

    • Doesn’t sound like you missed steps. Did you look at the failed inserts log and try to run them manually to see what would happen? Were there any errors?

  2. Bogdan Neagu Reply

    Hi,
    In SQL Developer v4.0.3, jdk1.7.0_71, and I get an empty list of Import Methods (mentioned on this blog) when importing a CSV file (did not try with other).
    I also have sqldeveloper-3.0.04.34 on the same box.
    I launched SQL Developer from the bin directory.
    In the console, I get a java.lang.NullPointerException:
    java.lang.NullPointerException
    at oracle.dbtools.raptor.data.core.GenericTable.populateTable(GenericTa
    le.java:43)
    at oracle.dbtools.raptor.data.core.GenericTable.(GenericTable.jav
    :29)
    at oracle.dbtools.raptor.data.ui.ImportMethodPanel.populateDBO(ImportMe
    hodPanel.java:488)
    at oracle.dbtools.raptor.data.ui.ImportMethodPanel.onEntry(ImportMethod
    anel.java:301)
    at oracle.ide.wizard.FSMWizard.gotoPanel(FSMWizard.java:765)
    at oracle.ide.wizard.FSMWizard.setSelectedPage(FSMWizard.java:481)
    at oracle.bali.ewt.wizard.BaseWizard.selectPage(BaseWizard.java:1966)
    at oracle.ide.wizard.FSMWizard.selectPage(FSMWizard.java:465)
    at oracle.ide.wizard.FSMWizard.doNext(FSMWizard.java:340)
    at oracle.bali.ewt.wizard.BaseWizard$Action$1.run(BaseWizard.java:4033)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:312)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:733)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:703)
    at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQ
    eueWrapper.java:169)
    at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQu
    ueWrapper.java:151)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThr
    ad.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:161)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:154)
    at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:182)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:221)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:219)
    at java.awt.Dialog.show(Dialog.java:1082)
    at java.awt.Component.show(Component.java:1655)
    at java.awt.Component.setVisible(Component.java:1607)
    at java.awt.Window.setVisible(Window.java:1014)
    at java.awt.Dialog.setVisible(Dialog.java:1005)
    at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:382)
    at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:298)
    at oracle.ide.dialogs.WizardLauncher.runDialog(WizardLauncher.java:51)
    at oracle.dbtools.raptor.data.DataWizard.launch(DataWizard.java:311)
    at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.invo
    eClassAction(ObjectActionController.java:220)
    at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.hand
    eEvent(ObjectActionController.java:200)
    at oracle.ide.controller.IdeAction$ControllerDelegatingController.handl
    Event(IdeAction.java:1482)
    at oracle.ide.controller.IdeAction.performAction(IdeAction.java:663)
    at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:1
    53)
    at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:618)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2
    18)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.ja
    a:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonMode
    .java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:25
    )
    at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:
    33)
    at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMe
    uItemUI.java:877)
    at java.awt.Component.processMouseEvent(Component.java:6516)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
    at java.awt.Component.processEvent(Component.java:6281)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4872)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:483
    )
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492

    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:708)
    at java.awt.EventQueue$4.run(EventQueue.java:706)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionD
    main.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
    at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQ
    eueWrapper.java:169)
    at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQu
    ueWrapper.java:151)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThr
    ad.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread
    java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThr
    ad.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146

    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138

    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

  3. Hi Jeff
    i have question.When i try to import data from excel to oracle db using sql developer. My excel has one column in which each cell has three divisions. So each row contains a column with 3 divisions.So how to i import data into the table in which i need to get for each row in excel 3 different rows in Oracle db table.
    Can you please help?

    Thanks in advance

    • I don’t think you can, you’ll need to split that row into 3 rows if you want 3 Oracle table rows.

  4. Hi Jeff, I am getting error “ORA-01722: invalid number” during Import of csv file having Numeric values with group separator, for example “2,308,000”.
    On preview step it looks ok and Verification is ok.

    I am doing this with SQL Developer Version 3.2.20.09 and tried with Version 4.0.0.13

    Could you advise how to resolve this issue?

    There is test case :
    CREATE TABLE TEST_NUM(test_val NUMBER)
    /
    file test.csv

    test_val
    “2,308,000”
    “-2,308,000”

    After run Import there is error

    –Insert failed for rows 1 through 2
    –ORA-01722: invalid number
    –Row 1
    INSERT INTO TEST_NUM (TEST_VAL) VALUES (2308000.0);
    –Row 2
    INSERT INTO TEST_NUM (TEST_VAL) VALUES (-2308000.0);

    • Weird, weird, weird.

      As a workaround, use the INSERT SCRIPT method, and run the script in the worksheet. Your data will go in A-OK.

    • Sergey L

      as a workaround we are opening the csv in Excel and changing Format of Number columns to not use group separator. Do you think it may be fixed as our PROD support often is using this tool to load user’s data ?

    • For sure, I’m talking with the developer about it now. The JDBC driver is getting in the way, we need to fix that.

  5. amresh kumar Reply

    thanks for the resolution of date issue,which most of the developers including myself at the same time are facing at the time of data upload from excle to DB.

  6. Format of date is not changing according to my requirement and Column position is not changing according to desired position. Pls suggest any method

    • I don’t know what that means. Format of date is not changing? You have to manually specify the date format as it’s stored in EXCEL so we can successfully import it from text to a DATE in the database when we do the INSERT.

  7. The information is very useful.Thanks for posting such useful tutorials…

  8. I installed SQL Developer but it does not have an Import wizard. Is this normal?

    I need the import capabilities.

    Can anyone assist?

    Version 4.0.2.15
    Build 15.21

    • We have an import wizard – right click on a table and say ‘import.’ What are you looking for?

    • Hi Jeff,
      I’m having the same problem. There is no “import” option when you right click on a table. I can only see “open”, “export” and “copy to oracle”.

      I’m running the same version of Oracle Sql deverloper as Joseph on OSX 10.9.4

    • What kind of table? Is it a table you own, or does it belong to another schema? Do you have INSERT privs?

    • Yes, it’s my own table and I do have INSERT privs. I forgot to say that I’m hooked up to a mssql db using the jTDS driver, if that makes any difference.

    • Thanks Bhasha! If you can believe it, we’re going to make it even easier in the next version of the tool. Stay tuned!

  9. Due to the nature of the data I can’t share it.
    However it is just an integer column, a date columns (yes, I successfully entered the correct date format mm/dd/yyyy), and some text columns.

    I saved the data as an xls and a csv file, no joy in 4.0.2.
    However, 4.0.0 read the xlsx file, no problem.

    I will uninstall 4.0.2 get a fresh download and reinstall it.

    I tried googling the error message in the log but found nothing helpful, I just thought it might mean something to you.

  10. This process worked fine on my Windows 7 x64 machine in version 4.0.0. Since I upgraded to version 4.0.2 it let’s me get through to step 5, but when I hit finish nothing happens. The process just hangs.

    I get the following line of information in the Logging Page:
    SEVERE, 99, 0, oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1, org/antlr/stringtemplate/CommonGroupLoader

    Any suggestions?

    • Go back to version 4.0. In the meantime, send me your input file for me to test – or have you already tried different files/types?

      The other thing you could try is putting 4.0.2 down again…

    • Rob Cline

      Sure enough, I deleted the install directory of 4.0.2 and did a fresh download of the 4.0.2 install files.

      The new installation works like a champ.

      Thanks for the direction.

  11. I need a table to which I load icons. Tge SQL*Loader documentation states I would use the following in an csv file.

    DISABLEDICON FILLER CHAR(100),
    DISABLEDSTATE BFILE(CONSTANT “scott_dir1”, DISABLEDICON)),

    In short, can I use this SQLD feature to load BLOBs and is the above method or something like it available?

  12. Yes, I have been importing .csv file which contains 11 million rows and it is not going beyond ” Data Import Wizard Step 1 of 4 ” , ie; Data Preview window is not showing anything. Please suggest.

    • If you think it’s a file size issue, try taking only the first 100,000 rows or so to a file, and see if that works. If it does, you may need to increase the amount of memory available to the JVM.

  13. Is there any limitation on data rows to be importes from .csv file?

  14. Greetings.

    Thanks for you help here. I’ve followed your steps and all is well. However i am importing 250k 10 character values into a single column table and it has so far taken 45 mins and we are only up to 75K rows. Is this speed normal. i can nearly type them in at that speed

    Cheers

    • I wouldn’t think that’s normal. But I don’t know what’s happening on the database. And I don’t have your table or data to play with. But you could try using the sqlldr or external table route instead and that will always be faster.

  15. To begin with, great post…glad for the help. on to the question:

    What if I don’t have the table structure built out in SQL Developer? I have a bunch of csv files with upwards of 200 columns on some, and I don’t want to create each column in the table. I have no issues with every column being varchar – is there anyway to import the csv with column headers, such that the columns from the csv become the columns in the db table?

    Thanks,
    Bassel

    • Yes actually. There should be a link up near top of this story, where you can see how to create a new table and load it via the CSV or Excel file. The column headers will get interpreted as the new column names…

  16. Hi,

    I am not able to view the tables in sql developer under the schema…how can I import and run large data inserts?

    Thank You!

    • Vandy,

      The person you are logging in as probably doesn’t actually own any tables. Read this post for insight on how to find what you’re looking for.

      You can also scroll down the tree to ‘Other Users’ and expand that. Your tables are probably in another schema.

      View > Find DB Object will also let you search for your tables.

  17. it show msg

    Import Data into table STU_DETAIL from file student1.xls . Task successful and import committed.

    and in log

    Task Cancelled:

  18. it show in msg box import succusful
    bt in log show task cancle and data not import
    i am use office 2007 excle file import in oracle 11g database
    through sql developer

  19. Hi Jeff,

    My requirement is to insert the data from excel/csv file into a table, The excel file that i recieve contains 30 sheets and only specific columns from all the 30 sheets needs to be inserted into a table. All the sheets has data at the same place.
    i tried sql loader but for that i have to manually copy the data from all the sheets into one sheet and then upload it which is very hectic, is there a way to write a pl/sql procedure to insert the data .

    Thanks & Regards,
    Bhavin

  20. belinda enna Reply

    am exporting an excel file the calculated data are not showwing

  21. The sql developer is not going past the verification. i get stuck on ‘Checking Data against Column size’ step and it stops working. It is displaying as ‘started’ but the process does not end. I can see no activity in the task manager so not sure if any thing is being done in the background to complete the step. I am using the latest 4.0.2 i believe. I tried to limit the rows to 10 as well with no success. Any help will be really appreciated.

    • can you make a copy of scott.emp, e.g. create table emp_copy as select * from scott.emp where 1=2…then take an export of scott.emp and see if you can import it to emp_copy?

      Otherwise, feel free to send me your table ddl and input file to [email protected]

    • Cant do that in the test environment as the schema do not exit. I will send the ddl and the file with dummy data though. I can also see the sql developer is version 4.0.1.14 .

Write A Comment