SQL Developer 4.1: Easier Excel Imports

thatjeffsmith SQL Developer 8 Comments

Tell Others About This Story:

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.

Looks for this in SQL Developer whenever you're prompted to save or open a file.

Looks for this in SQL Developer whenever you’re prompted to save or open a file.

Next we need to know how you want to bring the data in…

Note the preview window remains, no more forgetting what the data looks like.

Note the preview window remains, no more forgetting what the data looks like.

Now let’s map the columns.

Yup, data is still there.

Yup, data is still there.

So for this example, I’m creating a new table. So for each column, I need to define the datatype.

We now take a 'best guess' on the datatype - if it looks like a number, we put in number. And, I can see the values that are being proposed for this column.

We now take a ‘best guess’ on the datatype – if it looks like a number, we put in number. And, I can see the values that are being proposed for this column.

But what if it’s NOT number?

OK, we THINK this is a date, but we're not able to guess the date format.

OK, we THINK this is a date, but we’re not able to guess the date format.

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.
Ok, I picked the right date format, all is well now.

Ok, I picked the right date format, all is well now.

Last step…

...review and go!

…review and go!

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!

Tell Others About This Story:

Comments 8

  1. 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?

    1. thatjeffsmith Post
      Author

      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.

  2. Hi Jeff,

    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 ?

    1. thatjeffsmith Post
      Author

      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

  3. 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 (4.1.0.18) then i get 1,00258985E08, but if i import with old version (4.0.3.16) 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).

  4. 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.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *