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, 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
it’s an Oracle only feature – try SSMS if you want to import data to SQL Server
sorry, I mean to say am facing the above error in Oracle SQL database while importing data.
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.
Jeff, this is exactly what I was looking for.
Thank you for your great work!
Excellent, thanks Jagtar!
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…:)
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
Emil, sorry I can’t help you with PL/SQL Developer 10, or any other version.
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.
* using Oracle Sql developer tool.
Check the Other Users bit of the tree. The table probably doesn’t belong to your user/schema.
sir how to create a table by uploading a csv file directly in oracle
you’re close, i show how to do that in this post
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
So first things first – confirm that you are indeed connected to an Oracle Database.
ya i all ways connected to oracle cloud database .
>>connected to oracle cloud database
Which service are you using?
services means .iam using latest sqldeveloper 4.1.1 something i am using for oracle bi cloud services
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.
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
Can you upgrade? Version 1.5.5 is too old for me to help you with.
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”.
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.
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.
Thank you Jeff…… 🙂
This is much better than video tutorials.
-Dhiraj
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.
Not sure if i’ll be able to get that upgraded, but i’ll try. Thanks for your help.
sorry i am using version 2.1.0.63
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.
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.
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.
You’re using what version?
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.
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
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.
find the column in your table of datatype ‘BINARY_DOUBLE’ – remove the column in your worksheet that’s mapped to that table column
hellow jeff smith..
i am unable to load data through this process in sql developer.. please help me…
Thanks alot…Its helps me alot to import the data……….Cheers 🙂
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.
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 ?
Thank you very much Jeff!
It worked for me 🙂
I was exactly what I was looking for.
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
This is important – did you try to import 100k records from CSV or from XLSX file?
First Excel, then CSV.
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
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! 🙂
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.
Is it possible to default a value into one of the columns if it does not exist within the Excel file? thanks
Yes, but I do that with the database. I edit the table.column to have a DEFAULT value.
How about dbf files?
Sorry, no.
Thanks Its really great and helpful. Please keep me updated with such good tricks if possible.