In a previous post, How to Import from Excel to Oracle with SQL Developer, I covered step-by-step how to import data from Excel to an existing Oracle table. This post shows how to take a spreadsheet and transform it into a new table in your Oracle database.
But first, please permit me just a few seconds as I step up to my bully-pulpit:
Please do not import data to Excel just because you know SQL better than Excel functions and macros.
Everything has a cost. If you can keep your ad-hoc queries local to your machine and your spreadsheet, that’s a win for everyone. However, if you wan to put your data in Oracle anyway, then who am I to argue? So without much further ado, here’s what you need to know to load your data. And I promise – no more preaching, mostly.
Hate GUIs, and want to do this from the command-line?
Sample Data
For this exercise I’m taking some data from HR.EMPLOYEES and sending it to a new table. I’m purposely querying the data out in a different column order than the original source.
SELECT DEPARTMENT_ID, MANAGER_ID, HIRE_DATE, FIRST_NAME, LAST_NAME, EMAIL, SALARY, COMMISSION_PCT, PHONE_NUMBER, JOB_ID, EMPLOYEE_ID FROM EMPLOYEES
Obviously if your data is already in a table somewhere, it’s always going to be better to load it to a new table via a Create Table As Select (CTAS.) Even going across a DB_LINK may be more efficient than writing to Excel and then loading it back to Oracle. For this tutorial I’m assuming your data ONLY exists in Excel before getting started.
Step One: Connect to Your Database as the New Table Owner
You can only invoke the ‘Import Data…’ wizard from your connection owner schema. Of course if the table already exists, you can simply load your data by mouse-right-clicking on said table.
Step Two: Find the ‘Table’ Node and Access the Context Menu
Step Three: Select ‘Import Data…’
It says ‘Import,’ but we’ll also be creating a new table for the data to be stored in.
Step Four: Step Through the Wizard
You get to answer a few questions. Don’t like wizards? You could always script out this process yourself using SQL*Loader.
Load the file
Point to your XLS, XLSX, or CSV file.
Preview the data
Depending on the size of your spreadsheet, it may take a few moments to load up the preview dialog. This step exactly mirrors the data preview from my earlier post. However, a few things to note:
- The ‘Header’ tell the tool that there IS a header and not to include the first read row as data
- Skip rows – how many rows to skip. If your header line doesn’t appear until row 5, you’ll want to skip 4 rows
- You can load the data from any worksheet, we’ll default to the first one though
Name Your Table
There are a few options here. You can generate a script to create the table and then populate the table via INSERTs. Use this option if you want to preview or approve what’s going to be happening in your database. You can also tell SQL Developer to only load a specific number of rows.
Choose Columns
This step is important. Your spreadsheet may have 30 columns, but you only want to import 10 of them. Select the 10 columns you want to form the new table. Also, add them to the ‘Selected Columns’ panel in the ORDER you want them created in the new table.
Column(s) Definition
If there’s a wizard you don’t want to blindly click through, this one definitely qualifies. But if you’re asleep at the wheel, PLEASE make sure you’re paying attention when it comes to defining the columns. Two things to be aware of:
- We’re defaulting the columns to strings (VARCHAR2s)
- We’re defaulting the size to the max length of the data previewed
For point #1, we’re looking to have a better ‘guessing’ scheme for upcoming versions. But there’s no bigger problem in applications and data quality today than developers storing dates as strings or numbers. Be sure the datatype for each column is appropriate. If in doubt, consult your data model. If you don’t have a data model, consider taking a step back and creating one. I know, I know, I said I wouldn’t be preaching anymore. Sorry.
For point #2, there might be values that exceed this length. Size the columns appropriately based on your data model and application specifications.
Verify Your Parameters
This step is voluntary, but I recommend you always use it. It will help you catch date columns defined with missing or invalid date formats.
Finish!
SQL Developer will either create the table and load the data, or will will generate the create and insert scripts.
78 Comments
Hello Jeff, thanks for the post.
At first you said “Everything has a cost. If you can keep your ad-hoc queries local to your machine and your spreadsheet, that’s a win for everyone.”
My question, how can we do that? I don’t see any way to connect Oracle Sql Developer to Excel, or even create tables without having to connect to a database.
Thank you
i meant it costs something to put it in the database. if you’re just using the db to do simple things you could do in Excel, you might not want to do it
if it’s business data, it probably should ALREADY be in the database
we don’t offer jdbc connectivity from SQL Developer to Excel documents, for which I’m very thankful
you can however use ODBC to connect to an Oracle Database from an Excel file (and also Access) – which I’m not a big fan of
So if I understand all this, and I am no expert, then I need to get what is in Excel into a db table to use with other db tables. In my case it would mean running some type of SQL server on my desktop for the data I need to use.
Can tables on different connections even be joined?
Hi Jeff,
when you create a table like this how long will it be here? What would cause it to get deleted?
thanks
Hi Jeff,
Tried to create a table and import data from Excel workbook yesterday for the first time. Finally got it to see the data rows and work by copying the spreadsheet (somewhat at random). Now trying it again today and it’s doing the same thing. SQL Developer 4.1.19, Excel 2010, Java 1.8.0_45, and Windows 10.
On the Import Wizard step 1 to browse for the workbook and select a worksheet, everything is fine … but the preview only sees the column heading row, the first row, and none of the data rows. xls, xlsx doesn’t matter. But some workbooks (the ones I”m not interested in) show data rows.
Unless the preview sees more than 1 row, the Next button does nothing.
The workaround is to save the worksheet as a .csv file, but it sure looks like the wizard preview knows about .xls and .xlsx file formats, as evidenced by being able to read one row.
Are there restrictions on what types of data (plain text, formulas, conditional formatting, whether the workbook is open in Excel, whether Excel is running, etc.) can be seen?
Any suggestions?
Thanks,
Skip
Forumlas might not make it happy.
Send me your spreadsheet or one that exhibits the behavior, and I’ll take a look.
I believe the code is expecting ‘flat data’ on a worksheet, and that’s pretty much it.
hellow jeff smith..
am unable to load data through this process in sql developer.. please help me…
You may have already answered this question, but there is no plsql script that I can execute in the worksheet to import data out of a csv file and into a table? I understand how to do it through the GUI, but there is no corresponding sql to do the same thing?
I’m sure there is, but you’d have to write it. Other people most likely already have. I’ve seen solutions posted online in other communities.
You can also use the GUI to produce a SQL*Loader script that you can run if that’s more to your likely.
Hey Jeff,
Let me start off by saying great blog sir! This is helpful. I am pretty new to Oracle development, but have developed in SQL server and DB2 for a few years. If you already commented on this question my apologies, but is there is a way to do this by a script? All my attempts are failing. I can’t find a good explanation of how to do this. I am trying create a script so I can easily re-run the process.
Thanks so much and keep up the good work!
Hi Jeff,
THanks for sharing.
I tried to import from Windows an Excel file having 18K rows (7MB), as suggested in your post. It was not a success – the SQL developper took over all 4 CPUs for several minutes, then announced shortly “… Task failed”.
I added more memory (4096MB) using conf. Same problem.
Tried with SQL Developer 4.02 or 3.20 – same results.
Have you ever experienced such freeze of the tool? Could you suggest a reason for it?
THanks a lot for your answer.
Dmitry
yes, that’s a pretty big Excel file – save it as a CSV instead and it should only take a few seconds.
With CSV, it works like charm. Difference in speed is remarkable.
I have had a small problem that import mis-estimates size of varchar2 columns, despite all checks initiated by “Verify” button ended up with “SUCESS”. No big deal, I enlarged columns a little bit, then all went through fine.
THanks a lot for the solution!
Dmitry.
The verify only checks the data in preview window, so by default the first 100 records. When defining the column widths, you really want to fall back on your data model to determine how much space to set aside for your strings. Or when in doubt, go big with VARCHAR2’s.
Thank you so much Jeff!!
You’re very welcome, Penelope!
I should also ask if there is a better way to write/run data verification automation in Oracle?
Shell scripting with sqlplus/sqlldr/datapump are all tried and true methods – you should find lots of blogs and examples on this subject in the websphere. ORACLE-BASE would be a good place to start.
I am switching from SQL Server to Oracle, and I am trying to rewrite a SQL Server script that performs the following 3 steps in a row. In pseudo code:
Create Table Stuff (etc…)
Bulk insert a csv file (sqlldr in Oracle)
select * from Stuff where field name = ‘1’
I can’t get sqlldr to work in a script, am I just not finding the correct website? Thanks!
What are you trying to run via sqlldr, and what happens when you try?
In SQL Developer I am running variations of (including the path to sqlldr) :
sqlldr penelope@tgccp/mithril control=C:\Users\Penelope\Documents\BulkInsert\loader.ctl.
The result is “unknown command”. I’d include the contents of the ctl file but I don’t think it’s even getting there.
sqlldr is a separate program – it’s part of the Oracle Client. That’s not a recognizable command in SQL Developer itself.
Bummer! Before I spend a lot of time trying to figure out how to do the following only to find that I can’t, could you tell me if is it possible to run a script from the sqlplus command line that contains the three steps I mention above? Create a table, use sqlldr to populate it, and run queries? Thank you again for your help!
No, but you could write a shell script to do that. So OS script that calls sqlplus and then calls sqlldr.
I am facing problem while loading data as one of the data contains ‘&’ instead of ‘and’. This ‘&’ is being treated as an input parameter and hence is not getting executed .
Have SQL Developer generate the INSERT SCRIPT method instead. And then at the beginning of your script, add a
SET SCAN OFF
This will tell the script engine not to prompt for replacement values on occurrences of & in your data.
Hi,
how can I get the max. size of each column? Or what is the best way to import big csv’s, which I dont know their Length?
And is there a option to change the max. size of import??
>>how can I get the max. size of each column?
A perl script maybe?
>>what is the best way to import big csv’s, which I dont know their Length?
You could cheat and import all text fields as VARCHAR2(4000) (or 32k if 12c) or as a CLOB.
>>And is there a option to change the max. size of import??
Not sure what you mean…there’s generally no max size of an import.
Thx for your answer.
But doesn’t it take much more space if I make all columsn as a VARCHAR2(4000)?
VARCHAR2 defined columns only take what they need. A 1 character string would only take a single byte, assuming single-byte.
It’s lazy design, but I was being lazy.
If this is a real project, then look at your data model.
I cant import excel with more than 528 rows!
my config :
AddVMOption -XX:MaxPermSize=1024M
and my main file has 110 Mb
That’s a really big file. How many rows?
I want to import 5000 rows at first! (I want another file with 65000 rows for importing)
That’s not that many rows why is the excel file so big? I have an excel file of 100,000 rows, and SQL Developer can import it just fine.
If you can’t shrink the size of the file down, you may have to save it to a CSV format and then do the import.
I cant Convert it to CSV, because my data’s Language is “Persian” and thay will change (Like this: ???)
what size can my file be? (max size)
I make a file with 528 rows and 1 MB (for test). It import without any problem. But when add 1 row, it dont dose it! (without any error!)
I understood my problem! My file have 180 columns. when I try to import file with 10 column and 65000 row, it did fine.
How can i import excel file with 180 columns and 65000 rows?
I would try breaking it up into smaller files, try 10,000 rows and 180 columns. Or save your Excel file as a CSV and do the import.
I tried to break my file. but dont import more than 528 rows (with 180 column and 1 mb)!
is it depended to character?
Hi Jeff,
You’re a life saver thanks so much for taking the time to teach us. I have a question regarding the import from Exce to Oracle using SQL Dev, which I love: How do you import an Excel table that has calculated fields? Would that work in Oracle? Thanks so much.
would now teach me on how to import and export from Oracle to Excel sheet. please.
You just read how to do import.
To export, just right-click on your data grid > Export > excel 2003+ (xlsx) – answer the questions.
Jeff,
thank you for sharing your knowledge.
I was trying to import from data a csv file. I didn’t get to import strings containing line breaks.
When previewing data, it looks ok. When importing, sqldeveloper treats line breaks as record delimiters (although the strings are enclosed by ‘”‘ and i configured this in the settings). Am i missing something or is this a bug?
BTW: if i save the file as xls it’s the same: preview is ok, import not.
Thank you
Regards
Martin
use the import script method – then inspect the INSERTs and let me know what you see
Hi,
My Query was ‘Importing data from excel to oracle table through using Oracle Query’.
Will you please help me for this.
Thank you,
Regards,
Nicky
I don’t know of a way to do it strictly by query.
However, if your data was in a delimited text file, such as a CSV – you could create an external table that reads that file, and use SQL to insert the data to another table…
Hi Jeff,
thanks for the fast response.
The script shows the same effect: Parts of the string are treated as separate rows.
I dont understand (yet) why the preview function correctly shows the string including line breaks as one attribute of a row and the import treats parts of the string as separate rows.
Any hints?
Thank you
& Regards
Martin
Can you code me up a test case? Send me an excel file that exhibits this behavior – also, confirm that you’re on the latest version, 4.0.2, as that’s what I’ll be using to replicate what your example.
Jeff
Everything went just as your instructions above showed, no hitches. I wish I could say this was true for so many other procedures given on the Internet. Your tip on Data Model was most helpful for me as this is really my Power Basic ‘DIM TYPE’ keep preaching such things. Thank you for the superb guidance.
Hey Kevin – thanks! Sorry I didn’t reply sooner, I got caught up in conferences and vacation. Let me know how it’s going and how we can help you as you’re getting started with Oracle Database.
Boy I’m glad that I left some notes here as I am back to refresh my memory on what I done 8 months ago…. Data Model is the key item I think. I’m going to give it a go again to import a whole lot more data. I haven’t done anything with Oracle or SQL in the interim but want to pick up where I left off. At a read through it seems clear enough I let you know how it goes Jeff.
Jeff
I like the ‘cut of your jib’ a straight forward approach in explaining what needs to be done in transferring data from .csv files into tables in Oracle. I have confidence in your instructions on the first read through, a few more read read overs and I’ll try something that I’ve been planning to do for many many years. I just downloaded Oracle Ex 11 and SQL Developer yesterday and am keen to see my Power Basic programs interfacing with Oracle.
Hey Jeff,
Thanks so much for such a great blog! I have a query- i have a datasheet containing 7 million rows in CSV format which when opened in excel shows the first one million records due to its limitations…….is there a way to import randomly selected 1 or 2 million rows into oracle developer? (Note: I CAN import the first 1-2 million, but i need to randomly select to get rid off any bias) Any suggestion would be great 🙂 Thanks and cheers for the wonderful job you are doing here!
7 million you say? That’s…a lot. Don’t even bother with Excel. You want to setup a SQL*Loader session instead.
Also, ‘randomly’ and database inserts aren’t phrases I usually hear in the same sentence unless someone is reporting a bug 🙂
My boss reminds me that you could also setup your CSV as an External Table and then use the Create tables as select using the SAMPLE clause
select * From table (20) — this would give you 20% of the rows, now you feed that in your CTAS off of your external table and you got your uploading 1-2M ‘random’ rows to your table.
ahha…sounds good…thanks 🙂 lemme try that out as soon as I can 🙂
BTW….”Random” is a favorite word for statistical analysts like us 😀 Thanks so much!
Hey Jeff I have a problem: I cant seem to get past the column definition step because of my column names. A pop up box with the message “Validation Failed: The following new table columns have invalid names:” Meanwhile, there is no listing of such faulty names under the message. These are the changes I made to the names from my Excel table (with the original names in parentheses):
:ruoul (REVOLVING UTILIZATION OF UNSECURED LINE)
age (untouched)
numoftdpdnw ( Number Of Time 30-59 Days Past Due Not Worse)
Debt Ratio (lowered the captial letters)
MonthlyIncome (untouched)
numofoclal (Number Of Open Credit Lines And Loans)
numtdl (Number Of Times 90 Days Late)
numrelol (Number Real Estate Loans Or Lines)
numoftimedpdnw (Number Of Time 60-89 Days Past Due Not Worse)
Numofdependents (NumberOfDependents)
Please help! Thanks!
Osazee,
Can you email your spreadsheet – just the headers and one row of data will suffice. Then I can take a look at it for myself.
Quick glance, the ‘:’ in the first column is probably the issue. Try quoting it if you really want the ‘:’ in your column name. Also, no space either, so Debt Ratio should be either DebtRation or “Debt Ratio”
You’ll be better off w/o using quoted column headers if you can get away with it.
Thanks,
Jeff
[email protected]
Your email isnt working Jeff? I emailed then, and I emailed now…..getting mail daemon
[email protected] ? Just got a few dozen more emails today…
:ruoul won’t work, RUOUL will though and
Debt ratio – should be DebtRatio — and make sure none of those cols have > 30 characters in the name
Hi,
Data is coming in excel file format for every day.
Example:
abc_19022013.xls
abc_20022013.xls
abc_21022013.xls …. so on.
So I created table and inserting data by using import option in sql developer every day as per blog.
I would like to schedule and automate the insertion process by using sql developer.
Please let me know the solution.
Check out my post on using SQL Developer to create SQL*Loader runs based off spreadsheets. Once you have that going, consider an OS job that kicks off that script on demand or on schedule.
Jeff, congratulations for your blog, it’s very helpful.
One thing I consider would be useful for next versions, is the wizard auto-discovering field size. I often receive large excel data from customers, and have no idea what should be field size because the data varies a lot.
Though I have a workaround using max and length excel functions at the bottom of the columns, nothing is easier than have nothing to do. 🙂
You mentioned a better “guessing” scheme for identifying data type, so if possible please consider my suggestion also.
Thank you.
Hi Jeff. Is there any limitation on the size of the excel that i can import? we are facing an issue for files greater than 3 MB in size. We have around 1.6 Million records, so was wondering the best way to upload it in the Oracle tables.
Yes – you’re probably exhausting the memory available to the JVM. This same limitation exists when writing out the XLS files using SQL Developer. You can tell SQL Developer when it starts to grab more memory using this flag – edit your sqldeveloper.conf file –
AddVMOption -XX:MaxPermSize=2048M
This would give you a full 2 GB of memory and might be enough to read in all that data…BUT
But, there’s a better way.
Save your XLS file as a CSV file.
Then use the wizard. And be sure to use the SQL*Loader option vs INSERT. It will be MUCH more efficient in loading the data, and the database will thank you 🙂
For our next version of SQL Developer we’re looking to upgrade the component we use for the Excel stuff so the memory bottleneck wont’ be there anymore.
I am new to Oracle. Googled the topic of “how to import from excel to new oracle table”. Worked like a charm!! Great instructions and displays. I have passed your site on to a few experienced co-workers who appreciated the tip.
Thanks!! I have bookmarked you for future use.
Thanks Bill! Appreciate the note and appreciate even more you sharing this with your colleagues. If anyone in…Albany?…has any questions about SQL Developer, feel free to drop me a line and I’ll see what I can do!