26 October 2009

Importing an Excel into an Oracle database

There are several ways to do this, but I will only show you how to do this using SQL Developer. Why? Because I just came across this functionality the other day.
However there are some things you have to pay attention to, you might get frustrated with it otherwise.

Let's start with an Excel file.

This (very) simple Excel sheet shows three columns. The "A" column is a DATE; the "B" column is a number with a Financial Format; the "C" column is a string.

In the navigator of SQL Developer, when you right click on the Tables tab, you will get a context menu. One of the options is "Import Data ..."

An dialog is shown to locate your file.


After you locate you file, the Data Import Wizard is shown. In this Wizard, you can specify which page in your Excel you want to impot. Remember that our first column was a DATE?
Excel has it's own definition of what a DATE is. It's a numeric representation.
As you can see in the screenshot below, the Import Wizard doesn't recognize this Date format. Let's see the effect of this, for now let's continue.

Notice that you can also choose to have a script generated for you to create an External Table or to have a SQL*Loader file.

In the second step of the Import Wizard you can choose which columns you want to include in the destination table. For this simple test, we will select all of the columns.



Step three of the Wizard is to define the destination table. Notice two things here:
  1. we changed the datatype of the first column to DATE

  2. we changed the column name of the first colunmn to "date_col " in lowercase




The fourth and last step includes a verification step

Using the "Verify" button will do a sanity check of the data which is going to be imported. As you can see in the screenshot above, it does signal a mismatch between the data in the Excel sheet and the datatype of the destination column.
Also notice that the "Voltooien" (Dutch for "Finish") is greyed out. You can reactivate this button by navigating back to the third page and forward to the fourth page in the wizard.

We'll continue with "Voltooien" (="Finish"). The Message log will show you:


The table that we wanted is created. Inserting data into the didn't succeed though. If you look closely at the generated INSERT statement, you will notice that the column name is in lowercase. Hmmm...
The table definition changed the lowercase to an uppercase columnname - and rightfully so: "Thou shalt never create case sensitive objects"


Let's correct the situation and use a CSV instead of a native Excel format.
First save the Excel sheet as a CSV. But... first check your delimiter and change it if necessary to a comma.
To change this you need to Customize the Regional Settings. As you can see in the screenshot below, the default list separator is a semicolon. Change this to a comma.



After this step save your Excel file as a CSV.
Use the Data Import Wizard as before now selecting the newly saved CSV file. The first column will now show you a "real" date instead of a number.


Continuing with the Import Wizard, we now will use an uppercase column name, use the correct datatype and provide the format mask. For the number column in the CSV you will need to provide the appropriate Scale and Precision in order to load the data correctly. This is not shown in the screenshot.

When we verify the data in the next tab, we will get a "SUCCES" on every check. "Voltooien" (= "Finish") and we're set.


The table is created, the data is loaded into the table.


One last catch: The transaction is not automatically ended. You need to explicitly end it by either a COMMIT or a ROLLBACK.

Just to recap:
Next time to need to import data from an Excel sheet into an Oracle database, pay attention to the following points

  • Verify you delimiter, change if necessary: Control panel --> Regional setting --> Customize --> List Separator

  • Save the Excel as a CSV

  • Import the Data using the newly created CSV

  • Rename columns if needed and enter these in Uppercase

  • Provide the correct format mask when importing dates

  • Provide the correct format mask when importing numbers

  • End your transaction (COMMIT or ROLLBACK)



UPDATE 28-10-2009

A colleague of mine just pointed out that you can supply the delimiter when you import a CSV file. For whatever reason I completely missed this, first signs of aging? In the screenshot above I've circled this setting. Thank you, Peter for pointing this out.

11 comments:

  1. I export data from one database table and try to import in another database's table. The date is always exported as something like "23-SEP-1999 12.00.00.000000000 AM". Can you please let me know what the formatting expression would be for the date at the time of importing? I have tried all possible format strings and it fails to import the date with the value formatting as I indicated before. The formatting strings that I have used are "DD-MON-RRRR HH.MI.SSXFF AM" as well as "DD-MON-RRRR HH.MI.SSXFF AM TZR".
    Your help will be really appreciated.
    Email me at trooptony [at the rate] yahoo [dot] com

    ReplyDelete
  2. Thank you for your comments, Champ! ;)
    There are better ways to export and import data than using an Excel... having said that, it seems you are exporting a Timestamp not a Date. In the wizard you define the target column of datatype Timestamp and set the format mask to "dd-mm-yyyy hh.mi.ss.ff9 am" and off you go.

    ReplyDelete
  3. nice one thanks, but is it possible to directly load excel sheet with date to table, instead off converting excel to CSV and oracle table?

    ReplyDelete
  4. The Excel format is quite complicated, and also varies from version to version (afaik). Unless you write code to do that for you, there is no way to do that directly in the database. The APEX Listener has functionality which allows to upload an Excel file directly into the database, you might want to check that out (http://www.oracle.com/technetwork/developer-tools/apex-listener/overview/index.html).

    ReplyDelete
  5. How to update(not insert) data in tables using excel?

    I don't want rows to be repeated instead the specific columns should be updated..Please reply soon

    ReplyDelete
    Replies
    1. you could create an external table and use MERGE to update/insert the content of the external table into your "real" table... at least that's what I would do.

      Delete
    2. Hello! I have a problem... When I check the settings with Verify parameters before import I got: Verifying if source columns have target columns mapped Failed - Source columns are mapped to a previously mapped target column. In the Column Definition panel, each of my Source Data columns is mapped to a Target Table Columns. If I you have a tip, thank you. Horia

      Delete
    3. Hello again! It seems that I find the solution. In my csv file, exported from Excel, the columns were separated by ';'. After the last column, there was a ';' sign, of course. Ora Dev expected another column data after the last ';' sign and try to map the first set of data on the next line to that fake column -> error :). If I remove the last ';' in csv file, the import works. Horia

      Delete
    4. Hi Horia,
      I was just setting up a testcase to attempt to reproduce the problem you encountered... I see that I can stop doing that. Thank you for posting the solution, very much appreciated.

      Alex

      Delete
  6. Horia- I also want to thank you for posting this reply. I had the same symptom and underlying issue. You saved me a lot of time. Thanks, Elisabeth

    ReplyDelete
  7. You sir are awesome!!!!

    ReplyDelete