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
Hello,
I am able to import the csv files into new tables. My problem is that I get new data every year, but this is stored in similar csv file, then and I would like to update my table with the new data only. How do I import only the new rows and avoid duplicates?
Or should I import completely the new file and then do a merge/ join? I would appreciate your suggestion on the best way to do this…
Thanks,
Julia.
>>and avoid duplicates?
The easiest fix would be to create a primary or unique key constraint on your table. Then any duplicates would be rejected.
Thank you! Then I import as before and I will have error every time it finds a duplicate. My concern is: the import is done by 50 rows at a time, then if I just skip that error, do I miss all 50 data rows? I did a trial import and seems that it did not import all the new data (135 rows missing) Is there a way to do the import one row at a time?
Thanks…
Use the import SCRIPT mode, that will generate the insert statements and then you can run them on your own.
Great! it works…. its slow though, too much data. I wonder if there is a way to compare tables and identify the new data only? Then insert that into the database table…
Also, why would it ask for “substitution variables” every other script?
Define ‘slow?’
>>Also, why would it ask for “substitution variables” every other script?
Who is ‘it?’ If your data has & in it, you’ll need to set SCAN OFF in your script.
Hi
I have big problem to import data.
I want to know how to solve this problem or can sql developer handle it?
My problem is I want to import data from oracle tables that have relation.
But I redesigned my model and new tables in not the same as before.
Some old table splited to to or more tables.
i want to import programmatically or customized.
Please help me if can.
I use Oracle databases schema;
Instead of one big Excel file, you’ll want lots of smaller one. And then you’ll do inserts into each table, in the order required by your relationships.
Without seeing your excel file and table – my best advice would be to use the wizard to generate INSERT SCRIPT, and then fix that as needed.
I have been using this process for several months. Works flawlessly each and everytime. Users fill in hundreds of rows on a spreadsheet, and I load it into the database in a matter of minutes.
So I get a list of course sessions to load into a table and on step 4, I get a SERIOUS error message…Source columns mapped to the same target table column COURSE:CNAME,
Table column name is COURSE, name of column on spreadsheet is CNAME. This is the first time I have encountered this. What does the error mean?
how are you mapping columns, by name or pos?
also if you rename the excel column to something other than CNAME, does it go away?
Problem solved!!!
My spreadsheet has 20 columns. When pasting the data into the spreadsheet, I used 21 columns. The first column is the COURSE field. Evidently, when the SQL import encounters data in the 21st column, it maps to COURSE hence the error msg:
Source columns mapped to the same target table column COURSE:CNAME,
Ugh, sounds like a bug. I’ll try it later so we can fix it.
Thanks Jeff.. worked like a charm. 🙂 Ken
Excellent!
I am not able to import CSV File. I get the long message but in short, it says Task canceled and import rolled back. Can you help, please? I am following all the steps you mentioned above.
What long message?
Run the scenario again and have it generate INSERT statements instead. Then run that.
Also, what version of sqldev are you using?
When populating an Oracle table with fields that are ‘non nullable’. what value do you put in the Excel cell as a blank character?
The real data…not to be flippant, but if it’s not nullabble and you don’t have any data there, your data is incomplete.
I have a csv file that I want to load using import data, one column is a DATE data type. I cannot format the csv input file correctly for nulls. I have tried using the word null,null with quotes ‘null’, nothing or two quotes together and the to_date() function. The import data tool does not like the null values in the csv. I get an error stating the Data is not compatible with column definition or is not available for a not nullable column. The column is a DATE column nulls are allowed all the other dates work but the nulls do not.
You need to remove the ‘null’ text from the column. Search and replace in your text editor of choice, or in SQL Dev when creating your CSV exports, make sure the preferences are set to have NOTHING printed for NULLs
Hi there,
Thanks for the prompt reply. The data looks like so: columnA,,columnC,columnD, Where the date field columnB is blank for some rows. The importer still does not like the empty place holder.
Works for me – but you haven’t shown me your data or what exactly isn’t working. Just b/c the importer shows a funny pic on your NULL val, doesn’t mean it won’t happily import the data.
Hi there,
Yes in fact it does work fine despite the warning.
I assumed it would not given the warning on the importer.
I simply did not try and assumed it would not work.
Thanks Again
I need urgent solution to this error. I am a beginner in Oracle SQL developer, I am having issues importing to Oracle SQL developer. I created a table and save on my desktop as “test.xlsx”, and I also create empty table on Sql developer database. This is the error message i am having ” c:\users\desktop\test.xlsx cannot be opened due to the following error:org/dom4j/Namespace. Kindly assist me while this has been giving me concern. Thank you.
Hi Sir,
I would like to export data from table only 1 month data,How can I export using sql developer, I can export data,but all.
Thanks.
Best Regards,
Theingi.
write a query in the worksheet – add a where clause predicate to filter out the data you don’t want – run it via F9. right-click on the grid, export to excel
@thatjeffsmith…I have sql developer version 3.1 and want to Import data from Excel(xls) file to an oracle table.the file has 65k records.able to go till last screen and verified parameter after that when loading process started sql developer get hanged and I waited for 30 min it still hanged and no data imported into the table.
Please help me
Please upgrade and try again. That version is more than 5 years old.
I am working on client environment and I do not think client would be ready to upgrade. is there any other way,I can do it?
Try saving the xls as a csv and do the import with that.
Also, theres no upgrade. You just unZip v4.1.3 to a new directory and then run it.
Hello
I need to import data by code can you help me with this
thanks
No, but check the otn plsql forum and stack overflow, I know there’s plsql out there that can read/write Excel docs
Hi, I have many excel sheets which all have certain alphanumeric / numeric value which can be distinguished. I want to only import those values/keys from excel to Oracle or any db. Please note that the value/keys to be imported from multiple excel sheets and have varied rows and columns but the defined value/key will remain same. These values imported from each of the excel sheet is a unique record thus would be parsed in db for further analysis.
I sincerely request for your help on it.
You’re going to probably need to write a custom program. Our tooling doesn’t give you the flexibility you require.
While importing data in .csv or .tsv format in Oracle SQL Developer 3.0.04, I am getting the bellow error:
o.d.r.data.writers.ImportGenerator$SchemaTaskListener Task rollback.
Please help to resolve it.
Go get version 4.1 please. Your version is about 6 years old.
Hello Jeff,
I appreciate your follow up comments!
Here, i need your help. I’m uploading a CSV file which runs a job and insert the entries from CSV file into DB. Later give me the required details.
But it’s not inserting the complete row given in CSV.
For.e.g.
1. I gave CSV with 76 K rows and got data for only 15K rows
2. I gave CSV with 144 K rows and got data for only 28K rows
3. I gave CSV with 6K records, here it worked fine.
Looking forward for update on this.
Thanks!
do the import again, but set the method to Script.
take the script that’s generated – and run it in the worksheet
observe why the rows are not being inserted – and address the issue(s)
I’m sorry but i have no idea how can i change the method to script here. Actually as you saw third condition worked fine, because count was 6K. If it will be more than 6K, a batch job will run.
Then it only takes first few rows and insert in DB. Please help.
Thanks!
From Step 4
I’m sorry, i think, issue explanation was not enough earlier. It’s already developed application and we are not doing the manual import/export from sql developer.
We have an option in app to take input csv file, then its insert the data automatically (by code) after that in DB and generate a output CSV with required details.
I.e. Here when i give input csv file, its only read first some row and also while exporting, it doesn’t give complete required details.
Please help.
so…what exactly are you asking me? to help you code your app?
No, no help in code.. I Just need possibilities which can cause to get only some first data..anyhow we have analyzed the issue & will be fixed soon..Thanks for your help & time.
Ok…sure?
Hello,
I am getting the following error in oracle sql developer after clicking the finish button in the data import wizard.
Task canceled and import rolled back. Task Canceled
Can you please help me
did it open a log file with details after the import?
set the import method to INSERT SCRIPT – and it will build just that, and you can run it yourself and see if and when there are any problems
Thank you, I used Insert script option, but the problem was with the Xls file. I converted the file to csv format. then it worked
Dear Jeffsmith,
I am having data around 108 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me, My sqldeveloper is getting hanged. i need to do ASAP.
Dear Jeffsmith,
I am having data around 50 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me. My SQLDeveloper is getting Hanged.
make sure you’re on version 4.1, and make sure you’re working with an XLSX file – 50,000 records should be no problem
Ok thanks. If i want to upload more than 1 lakh records. how i can proceed?
I’ve done a million records before, with no problems.
If you’re doing 10M+ records, suggest you save your Excel file as CSV and use SQL*Loader or an External Table to load your data.
Dear Jeffsmith,
I am having data around 30 lakhs records, I am not able to uploading into tables using SQL Developer, could you please suggest me,
Best!!!
What happens when you try?
Hi Jeff,
What does status started means.
For one test “Checking data against column size” I am constantly getting status as “STARTED”.
Please help me.
You saved me from having to write the insert statements between the columns of the spreadsheets, export the text, verify it and only then execute.
My xlsx files were created by SQL Developer itself, so this task became even easier. Thank you very much.
While importing sdo_geometry data from excel to oracle db using SQL developer tool getting below error
ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR.
Please help anyone on this.
Hello Jeff,
I am unable to import data correctly. trying to import excel data to sql.
https://infoallsite.wordpress.com/
table column imported but not data ? any suggestion
that error is telling you exactly what the problem is. you’re trying to insert 22 characters into a field that’s sized for 21. make the column bigger, or make your data shorter
Hi,
I’m trying to import an excel spreadsheet into a table. The spreadsheet contains string values of “TRUE” or “FALSE” in few columns. When generating insert statement SQL Developer converts them to lower case. Is there any way to disable this “feature” and import as is? The version I use is 4.1.3
Did you try assigning a check constraint to the column?
I am trying to use sql developer 4.0 to import excel which has a large text field, on the first popup page of the Data Import Wizard (step 1 of 5), it brings up the preview of 100 rows with no problem, but when I try to click ‘Next’, nothing happens, it won’t go to the step 2 page.
Based on my research, I have changed the conf file by adding the
AddVMOption -Xmx1024M
But no luck… Any suggestions? Thanks.. Rick
what version of 4.0?
make sure it’s at least 4.0.3 – lots of bug fixes between 4.0 and 4.0.3 – not to mention that current version is 4.1.3.
how big is your xlsx file?
Version 4.1.2.20…
40k records, one field contains around 3000 – 4000 characters… the others are small items (like first name, date…)
For now, it’s working only if the excel file contains around 100 records..
Thanks, Rick
that’s a big file, or a lot of data to process
suggestions:
add 3-4 GB of RAM for the JVM – make sure you’re making this config change in the product.conf file OR save your Excel file as a CSV and try again
Thanks for the help.
After changing the conf file (sqldeveloper.conf under bin folder), I had tried with xlsx with 2000 records, still not working.
Tried with CSV (with | as the delimiter), but the large text item contains multi-lines of text for one record. The sql developer cannot read it well… Any solutions for situation like this?
Thanks, Rick
Thanks, this article helped me a lot.
I have an existing table with millions of records. Recently we added two fields to the table. Now we have a million of records which we need to insert into those two new fields using Employee ID as key constraint. I data for those two columns is in excel. How can we utilize Oracle SQL Developer to import the data into those columns using Employee Id as Key constraint.
Import the excel file to a temp table and then write an update statement to fill in the new columns for your table.
Hi,
It TOAD it was an option to commit every records while loading from Excel. Is there similar option in Oracle Developer?
Thank you.
No, but you could also use the INSERT SCRIPT method and insert your own as needed.
But, how many rows are you importing?
Thank you for the prompt response! I don’t know yet how many records I will have to load. I guess it’s about 100,000 records. Just preparing myself for the upcoming tasks after TOAD is not available to me any more. I guess it’s not big deal 100,000 in one commit. Thanks.