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:
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
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.
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:
- we changed the datatype of the first column to DATE
- 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.
0 comments