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
i made an error its terminated by ‘|’ help mee plzz
it’s my controlfile
options(rows=50000)
load data infile ‘eventlink.csv’ append into table eventlink fields terminated by ‘,’ trailing nullcols(
date_heure,a_party_number,b_party_number,event_duration,designation,
incoming_route,outgoing_route,ic_partner_id,
price,billed_duration,cod_agrupint )
and it’s my csv file
05/04/2014 00:09:35|0632540054|0033620803839|7|O|10023
|00023|00023|.004317|7|006021
05/04/2014 00:09:37|0664081474|0033651227794|294|O|10023 |00023|00023|.1813|294|006027
and when i make this in cmd
sqlldr USERID=scott/tiger CONTROL=imran.ctl
SQL*Loader: Release 11.2.0.1.0 – Production on Mer. Avr. 30 10:03:35 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Point de validation (COMMIT) atteint – nombre d’enregis. logiques 64
but i want to have 200 000 lines in the table eventlink but i found any line
helps meeeeee,plzzz
Good moorning,
I have rather unusual input data file in CSV format. I tried to import to oracle table using SQL developer version 3.2. It’s pretty straight forward import. However, I’m not sure why not all records imported to the table. What is the best way for me to send you the sample data for you to review? Please advice. Thanks
Have you reviewed it? What makes it ‘unusual?’ What rows don’t go in?
Use the ‘script’ output method. Then run your script. You’ll see the errors there and can make changes as necessary yourself.
good moorning,i want to import a large data(1500 000records) from file.txt into oracle, but i need how to use sqlloader (the correctly methode).thanks for you
Do you have access to the database server? If you do, moving the file there and creating an External Table will be faster. If not, then SQL*Loader is perfectly fine. If your text file is delimited, it shouldn’t be too bad to setup.
You can actually use SQL Developer to do this.
i want to import a large data(two millions records) from .txt into oracle but i havaen’t any idea about sqlloader(how to use it? i have oracle 10g express edition )
Is there any way to incorporate a sequence, and static fields into a data import from an Excel file.
For instance, if I have table X with fields A,B,C,D,E and have and excel file with fields B and C only. I want to put a sequence.nextval into field A, sysdate into Field D, a constant into field E (a name, or an id of the operator, etc..).
It’s fairly straight forward to just insert data, but is there any way to add these extra fields?
thanks.
Yes, leave the Excel file alone, but setup DEFAULT values or BEFORE INSERT triggers on your table to get the static fields populated as the Excel file loads.
That wont work. The “defaults” are generally NOT the same, they will be unique for each load. No triggers allowed on tables (for many reasons).
I will just continue creating insert statements. Just was hoping for something a little easier.
Thanks.
If it’s for each load, then probably just search and replace in your XLS file is the easiest way to go, yes?
Thanks.
I converted the excel file to CSV and imported all 250000 rows successfully.
I have used SQL Developer Version 3.2.20.09 to import the excel (MS Excel 2010) into oraclce DB 11g R2.
Is there any other way to import from excel without using SQL Loader/External Table?
Import discarded automatically after 65536 rows are inserted using SQL Developer. How to resolve this issue?
I took SH.SALES, queried out ROWNUM < 70000 to CSV Imported to a new table, HR.SALES_COPY. All 69,999 rows imported successfully. You're going to have to give me much more detail to reproduce this. It's possible that you're exhausting the memory in the JVM with that many rows if it's an Excel import. You can add more, but I need to know which version of SQL Developer you're running first so I can tell you the right file name.
Thank you very much buddy.. Great work.. keep it up…
I think I’ve hit a problem that a couple of other replies mentioned. Numbers (formatted NUMBER(9,0)) and with input values like 1234 are being imported as 1234.0. This is a problem because those numbers are foreign keys. An example row in my input CSV looks like this:
“2383”,”2414″,”PARTY_ASSOCIATION_TYPE_SYS”,”HAS_MEMBER”
but after it’s been through the import wizard this row is being inserted like this:
INSERT INTO ESP_PARTY_ASSOCIATIONS (PAS_FROM, PAS_TO, PAS_ASSOCIATION_GROUP_ID, PAS_ASSOCIATION_CODE_ID) VALUES (2383.0,2414.0,’PARTY_ASSOCIATION_TYPE_SYS’,’HAS_MEMBER’);
You can see it’s appended “.0”. It’s quite strange because some of the earlier tables have NUMBER(9,0) columns with input data that looks the same and which processes correctly.
Any ideas?
John, can you send me a sample input file and table DDL to test with? You can email that to [email protected]
Hi thatjeffsmith,
Can you please tell me how to export data from notepad++ to a oracle table?
Thanks for any suggestion.
The easiest way would be to save your text file in a delimited format, say CSV. So separate each row with a line break, and each column value with a comma.
Then right-click on the table (or if it doesn’t exist yet the Tables tree node) and say ‘import table data.’
This is an approach that is often applicable. In the past I’ve employed the following three other approaches:
* Cheap but effective for small volumes: add a column, put a formula in it : =”insert into TABLE(list) values (“‘ & a1 & ‘”, /* number */ ” & b1 & “);” and then fill down that formula and copy it in to TOAD or SQL*Plus. Possibly enclosed by begin/end.
* Use Invantive Control (note I work there). It allows you to download from Oracle into Excel, add rows according to business rules and upload in the originating view or table using synchronize. Fit for larger volumes upto 20 M rows.
* Use Pentaho Kettle: can repetively load many tables from one or more worksheets. Fit for very large volumes upto (I guess) 1000 M and more rows. It is free.
I can think of many, many more ways to do this. Thanks for sharing. This post is how to do it with with SQL Developer 🙂
How to insert excel data to oracle sql developer with Multiple excel table?
One at a time…
Jeff,
Is it possible, when you do an import, to have the target table created for you by sql dev? In other words in your example above you have the table created in oracle already and you import to it which is great – appreciate the great post!. A nice feature of other products is the ability to have the tool build the target table and then load it with the imported file in one fell swoop. (I realize Oracle dba’s will cringe at this but for testing, productivity and reality – it is a great benefit).
Read the post again, the first link takes you to a post that shows how to do this 🙂
Oh man, sorry Jeff. I read through all those posts too looking for it and it was right in the second paragraph!!! Thanks a bunch!
No worries!
My advice on this: think HARD on the datatype definitions and don’t just blindly accept the defaults!
Cheers…
It worked for me.
Thanks
I created new tables for my csv files and loaded 6 files into 1 table that consists of 84k records. Everything was fine. As for my second table, after loading the first file, the second file doesn’t seems to load into the oracle database. :/ tried multiple times still to no avail.
i am using oracle 11g
step-1 and step-2 are successful but at step-3 import method option is missing. what to do. Is anything missing or must be missed in installing. sometimes at the step-1 after selecting the file to open i am not getting the data of the file displayed as in step-3. what should i do. please reply.
Can you send me your file for me to test with?
Can you use the feature with ANY Excel file, or are you just having problems with one in particular. For example, can you export SCOTT.EMP and use that as a test? If that doesn’t work, then there’s something wrong or off with your environment or install.
can u give ur mail id so that i can send the excel file. no other excel files are also imported.
while importing i am not getting the import method option between data preview and columns. i didn’t get the insert option which u showed in step-4 . But still i tried and all the columns are chosen and in column definition verify is also done and shown success. i then clicked the finish option but no values are inserted into my table. please help me. i need it. as i have a lot of excel files to import data into oracle database.
i sent you excel file to ur mail. please check
Hi
How can I see the script that SQL Developer did for importing the data ?
Thanks a lot! 🙂
On step 4 – set the drop-down option to ‘Insert Script’
Instead of doing the inserts, we’ll just give you the script to review and run on your own.
Yes I have the INSERT rights
Something’s going on, but I’m not sure what. I’m on vacation this week. Can you post this issue to our OTN Forums?
Be sure to include all the details of your scenario, version of SQL Developer, Java, Client OS, Oracle Database, etc.
Hi,
I have an issue at step 4. In printscreen appears 2 import methods: “Insert” and “Insert Script”,
These options are not available for me. In fact there is no option available and I can pass to the next step
SQL developer 3.2.20.09
One more thing. I have setup two different database.
For one (test environment) it works.For the other (production environment) I have this issue. Which make me think is about giving certain set of rights/permissions to be granted by my DBA?
Let’s start with INSERT, do you have that priv for the table in question?
Hi Jeff,
Thanks for the post.
Though I knew about this feature but I did not have to use it until today. And now I cannot get it to work. Things work fine until I click the ‘Finish’ button after which nothing seems to happen. The data does not get imported, nor do I get any error message. Tried with different tables and different Import Methods too, but it does not seem to work.
SQL Developer: 3.2.20_09
OS: Windows 7
Will be sending you a mail with the csv file. Though I doubt it is an issue with the file since it contains only a single column with three rows.
So got your email, with your table DDL and CSV file. It imported just fine for me. I ended up with 3 rows, the last one being a null;
I did notice on the preview screen, I needed to change the file encoding from Unicode to ASCII to get the data to display correctly…did you also do this?
Thanks Jeff,
I did not have to do that since the data was displayed correctly. Though I later did follow this step also but it did not help.
I have already mailed you the debug log as you suggested. Let’s see if anything comes up.
I’ve sent the logs over to the developer…I didn’t see anything glaringly wrong, but what do I know?
Thanks a lot. Excel files with huge row count (280K) in my case, this works fine with .csv. I tried with .xlsx file and had issue of slw developer going into hung status.
” is NULL, i know that. But I am not able to verify where to rectify ?
Can you please look into the scenario I have explained above and help me out?
Hi
It was a real valuable imformation. I have one query. If we want to upload SYSDATE in the date field , then how to proceed? Can you help me out here?
What I normally do in that case is define the DEFAULT value for that column as SYSDATE, then make sure that col value in the spreadsheet is a NULL, then when the load happens, the DB takes care of that for me.
I have done the same thing but its not working out for me… It inserts NULL value in the table. What I found is that, when the internal import query is prepared (INSERT INTO XYZ VALUES (to_date(”,’DD-MON-YY)) it is inserting the NULL value.
NOTE: I have also defined DEFAULT value as SYSDATE for that column and haven’t passed any value in the excel column for that.
Run a sample INSERT to your table, passing nothing for your date field. Then query the table. Do you see the SYSDATE time from when you ran the insert in your new record? If not, you haven’t set the DEFAULT property of your column definition for the table correctly.
Thanks.. But I have tried that also…Not working.
The problem is the internal query which is generated. It takes the null value inside single qoutes(‘ ‘)(to_date(”,’DD-MON-YY’)). There is the problem! If that qoutes (‘ ‘) can be removed it will work.
Try this. Let the date field in the Excel have some manual date (put some random date) and leave some NULL. Then try to insert. Hope I am able to make you understand my problem… When you do that the data where you have specified the date gets inserted into the table. But the one you left blank will insert NULL value in the table, not the SYSDATE (which is required)….
” is not a NULL – that’s your problem