The most read post on this site? ‘How to Import from Excel to Oracle with SQL Developer‘ – and it has been since I published it almost 3 years ago.
People really like Excel. Or I should put it this way – Excel is the way information is shared in an organization. It makes no matter how much money was invested in BI, reporting, and databases – at the end of the day, it’s going into a spreadsheet.
That being said, sometimes you need to take data in a spreadsheet, or text delimited file, and move it back into Oracle. Either as a new table, or into an existing table.
For version 4.1, we tweaked the existing wizard to save you a bunch of clicks. The wizard works as it’s always done, so don’t freak out that we went to change stuff for the sake of change.
But something bothered us, and we figured it bothered you too.
You couldn’t see the data you were importing once it came to mapping the columns. So you’d have to go back, back in the wizard to the preview window, and then forward, forward to the column definitions.
Or maybe you had the file open in another editor so you could review it as necessary.
Well, that’s nonsense. So let’s make it so you can ALWAYS see the data.
Oh, and we tweaked a bunch of other things too 🙂
Tired of reading? Here’s a 30 minute video overview/demo…
How it works in version 4.1
Get to the data FASTER. Now you can define the input file and see it on the same screen. Also, if this is a file you’ve just created with a SQL Developer export, or it’s a file you’ve recently imported – it’s going to show up on the file-history-drop-down-thingy.
Next we need to know how you want to bring the data in…
Now let’s map the columns.
So for this example, I’m creating a new table. So for each column, I need to define the datatype.
But what if it’s NOT number?
We do give you a drop down list of some date formats. You can choose one, or enter your own. AND, we grab the date format from your NLS preferences and make it the first choice. Now, I happened to build this excel file WITH SQL Developer, so picking that date format just happens to be right for me 🙂
Note a few other things going on in this screen:
- On-screen validation: we used to ASK if you wanted to validate your import at the very end of the wizard. Now we validate for you automatically as you go through each column. Problems are highlighted with the warning or error images in the data preview window. We also add a message to explain to you why we think there is a problem.
- Reviewed columns are marked: each column is italicized until you’ve actually looked at it. So if you’re reviewing 200 columns, you can tell right away which once you’ve looked at – or not.
- SizingIf it’s a number or a string, we look at the preview window amount of rows and best-guess the column sizing and precision. We’re tweaking this for the next update, say +2 on scale based on ‘biggest’ number found. We’re also looking at some pre-defined text sizes for columns, say 10, 100, 256, 4000, 32k…Remember, this is only for NEW tables.
But wait, there’s more!
We’ve made it easier to do the same imports over and over again. And you can now run these via the command-line interface. But those topics will have to wait for another post. Stay tuned!
I have version 20.x
I have my csv files on a Linux server but I also have to save it on the c:drive first in order to preview it first. Is there a way to specify the csv on the server so I can preview it from one location only?
Also how do I schedule sql developer to pickup new csv files from the Linux server and create the tables dynamically on oracle as external tables pointing to the directory location defined in dba_directories?
You’d need a network drive.
But i like your csv/job to do the external tables better. You’d create database jobs to do work on files in the database directories. You’d only use SQLDev to help you write the code, but the database (not sqldev) would do all the work.
Thanks so much for your resources on SQL Developer. I have one important question. How do you load a flat file without delimited columns into a table assigning the columns during upload via SQL Developer.
This task can can done is SQLLDR using position columns (i.e braking the flat file by position. But when I tried SQL Developer, It all came up with one column and can find where you can define the rows into columns of the table to be loaded.
Of course I can save this as text file and break the columns using excel by assigning a delimiter but I am hoping this can be done in SQL Developer without formatting the flat file first.
Thanks for you help
You’ll have to define the control file yourself unfortunately. We’ve built our support for this process assuming that MOST folks would be using delimited data.
THESE ARE GREAT INSTRUCTIONS BUT I CAN’T GET STARTED BECAUSE THE PREVIEW DOES NOT SHOW ANY FILES FOR ME TO SELECT FROM. DATA IMPORT WIZARD STEP 1 OF 5 SHOWS A BLANK SCREEN. WHEN I SELECT TOOLS THEN PREFERENCES THEN DATABASE THEN UTILITIES THEN IMPORT, THE SCREEN DISPLAYS ERRORS. IT SAYS INTERNAL ERROR WHEN ATTEMPTING TO CREATE THIS PANEL. I UNINSTALLED AND RESINSTALLED AND ALSO INSTALLED JAVA JDK 1.8X (JAVA DEVELOPMENT KIT) BUT IT DID NOT WORK. WHAT CAN I DO?
The sqldeveloper ZIP..extract to a FRESH (empty) directory.
Then try again. If it’s still borked, there should be errors in the log panel, copy all of those.
I can’t really help you until Thursday, I’m on vacation w/o my computer. If you need assistance before then, post to the OTN forums.
In excel I have dates saved as dd/mm/yyyy. But when I am importing the excel in SQL Developer the date format needs to be changed as DD-Mon-YY .
How can I do that ? While importing I am changing the format to the desired format, on verifying the data everything coming as Success , but the import is not happening.
It is giving ‘GDK-05043 not a valid month’ error.
Please let me know what should I do to avoid it ?
you supply the date format of the data as stored in the cell in Excel – that way Oracle can understand what the DATE is. it’s stored as a DATE in the database, not as a formatted STRING, e.g. DD-Mon-YY.
describe how the date looks in the spreadsheet, the database will do the rest
This new version of importing is not working as good as the old one. I have excell file with one column NUMBER, which holds numbers like 100258985. If I import this with new version (220.127.116.11) then i get 1,00258985E08, but if i import with old version (18.104.22.168) i get 100258985, which is correct.
When importing with new version i get column definition NUMBER(11) and preview shows 1,00258985E08, with old i get definiton VARCHAR(12), which i change to NUMER(9) which produces correct import.
Changing from NUMER(11) to NUMBER(9) in new verison produces the same result (1,00258985E08).
I wonder if you had a chance at getting a fix to this. I’m running into similar issue when importing into numeric column. Numbers are being converted into engineering notation and last several digits get turned into zeros. I have a workaround to modify the column type to varchar2 but I’d appreciate any pointers to address the issue in SQLDeveloper.
These is very old thread. You should upgrade to version 18.3
It is great that we can choose the date format, but usually to me it is more annoying that I cannot choose the number format. I get delimited files from different sources. Some use dot as decimal separator, others use comma. the only way I found to import them is to change the NLS-settings in the preferences.
Default format for German is 999.999,99
When I try to import a file in American format, a number like 5,429.60 will be generatet into a statement like
INSERT INTO xy (VTR,AVV) VALUES (’79’,5.429); –missing some digits
And the import will fail because of ORA-01722: Ungültige Zahl (invalid number), because when executing the generated script SQL Developer will use the GERMAN settings.
Just run an alter session set… in your worksheet before starting the wizard. They share a connection, so that should give you what you need.
A good idea, still you said above that you want to save us a bunch of clicks 🙂