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
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
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
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
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.
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!
Click on the ‘Finish’ button.
If the Wizard runs into any problems doing the INSERTs, you’ll see this:
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.
Now let’s go look at our new table data!
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 😉
- Here’s 11 more tips on Importing Excel/CSV into your database
359 Comments
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?
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)
Have you tried it with just one CSV file, or it happens for all CSV files?
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.
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.
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.
Just got back with dev, this is fixed for the next release 🙂
Thanks a lot..Helped me a lot
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.
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.
Thank you man. You saved my time!
The information is very useful.Thanks for posting such useful tutorials…
Thanks vishnu, for taking the time to share that with me!
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.
It does – we only support that feature for Oracle databases.
Thanks.
This really worked for me.
It’s wonderful & very easy to load data from CSV file.
Thanks Bhasha! If you can believe it, we’re going to make it even easier in the next version of the tool. Stay tuned!
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.
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…
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.
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?
SQL Developer doesn’t have an easy way to facilitate this. But, I found a cool script on the OTN Forums from a user.
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.
Is there any limitation on data rows to be importes from .csv file?
No, but I’m guessing you’re having an issue?
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.
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…
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.
it show msg
Import Data into table STU_DETAIL from file student1.xls . Task successful and import committed.
and in log
Task Cancelled:
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
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
There’s ALWAYS a way to use PL/SQL. But I’m not the right person to help you do this.
am exporting an excel file the calculated data are not showwing
Not showing in the database or not showing in Excel? Can you give an example?
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 .