Toad World® Forums

How to import excel file into Oracle Db

Aloha
Here’s my purpose : I’m trying to import an excel file into my database. But I got this error log :

Import Started [28/01/2010 10:46:43]
Processing “indicateurs_2006_IRIS.xls” into a new table PIERRE.MUCKENSTURM.kjfhkdq
Creating table
CREATE TABLE PIERRE.MUCKENSTURM.kjfhkdq (“VAR_ID” NVARCHAR2(50),
“VAR_LIB” NVARCHAR2(50),
“VAR_LIB_LONG” NVARCHAR2(50),
“ANNEE” NVARCHAR2(50),
“SOURCE” NVARCHAR2(50),
“THEME” NVARCHAR2(50),
“ORD_THEME” NVARCHAR2(50),
“<NullColumn 8>” NVARCHAR2(50),
“<NullColumn 9>” NVARCHAR2(50),

“<NullColumn 256>” NVARCHAR2(50))

Error: False
Creating table
CREATE TABLE PIERRE.MUCKENSTURM.kjfhkdq (FIELD_1 NVARCHAR2(50),
FIELD_2 NVARCHAR2(50),
FIELD_3 NVARCHAR2(50),
FIELD_4 NVARCHAR2(50),
FIELD_5 NVARCHAR2(50),
FIELD_6 NVARCHAR2(50),
FIELD_7 NVARCHAR2(50),
FIELD_8 NVARCHAR2(50),
FIELD_9 NVARCHAR2(50),
FIELD_10 NVARCHAR2(50),
FIELD_11 NVARCHAR2(50),
FIELD_12 NVARCHAR2(50),

FIELD_256 NVARCHAR2(50))

Error: Create table PIERRE.MUCKENSTURM.kjfhkdq generated an error: ORA-00922: missing or invalid option
Error importing data, please check file format options: Create table PIERRE.MUCKENSTURM.kjfhkdq generated an error
Import Finished [28/01/2010 10:46:45]
I’ve tried with a csv file and then I got another kind of error.

What the hell did I miss ?

Thanks in advance for any kind of help, clue, you may give me.

What type of database connection are you using? It looks like you are importing to an Oracle database but I can’t tell if you are using a native Oracle connection or ODBC connection.

I suspect the error is coming from the table name. The script is trying to create a table named PIERRE.MUCKENSTURM.kjfhkdq. Go back to the page of the import wizard and put the Schema in the bottom edit box and just the table name in the top one. Using uppercase names are easier but special characters are allowed. If you really want lowercase or non-standard name you need to quote the name --> “Special Name”.

See screenshots.

Debbie
P.S. I acually this this was different in earlier releases. But this is the current behavior.
NewTableSpecial.png

What type of database connection are you using? It looks like you are importing to an Oracle database but I can’t tell if you are using a native Oracle connection or ODBC connection.

I suspect the error is coming from the table name. The script is trying to create a table named PIERRE.MUCKENSTURM.kjfhkdq. Go back to the page of the import wizard and put the Schema in the bottom edit box and just the table name in the top one. Using uppercase names are easier but special characters are allowed. If you really want lowercase or non-standard name you need to quote the name --> “Special Name”.

See screenshots.

Debbie
P.S. I acually this this was different in earlier releases. But this is the current behavior.
NewTable.png

Hi,I'm using an OraHome92 client to connect to my Oracle DB. Could it be the reason of my issue ?I've tried different tablenames (TEST, IRIS_blablablabla,...). But the resultat is still an error. T.I.A.

I don’t think the client version is the issue. Please post a screenshot of the import wizard page where you state the name of the table.

Debbie

P.S. You are using TDA 2.5 right?

Here are the screenshots of all the different step :I’m using 2.5.

Message was edited by: tobyZiegler
Presse-papiers-3.png

Here are the screenshots of all the different step :I’m using 2.5.

Message was edited by: tobyZiegler
Presse-papiers-5.png

Here are the screenshots of all the different step :I’m using 2.5.

Message was edited by: tobyZiegler
Presse-papiers-1.png

Here are the screenshots of all the different step :I’m using 2.5.

Message was edited by: tobyZiegler
Presse-papiers-4.png

Here are the screenshots of all the different step :I’m using 2.5.

Message was edited by: tobyZiegler
Presse-papiers-2.png

Okay, I see what is occuring. While your user name is all uppercase, the use of the period makes it a special name and we are not recognizing this. Please put quotes around your user name. See screenshot.

Debbie
ImportTest.png

ok I’ll test this.

It seems to work better, but I now got this error message :

Reading from file indicateurs_2006_IRIS.xls
Error: Une exception de type ‘System.OutOfMemoryException’ a été levée.
Error importing data, please check file format options: Une exception de type ‘System.OutOfMemoryException’ a été levée.
Import Finished [01/02/2010 10:35:28]
Do you have any idea ?

I’ll check with my DbAdmin, but we do not know what to do.

How many columns does it have? How many rows? How many sheets?

If you are exporting from one database to Excel and then importing to another, I would suggest using CSV as a format. This is more efficient than Excel and can handle larger number of rows.

Debbie

My excel file has 373 lines and 7 columns. I’ve attached it to this message. It’s a genuine excel file, not an export.
Toby
indicateurs_2006_IRIS.xls (104 KB)

I just tested in TDA 2.5 and there is some kind of error that produces the out of memory exception. It is trying to create a table with way more columns than you need. TDA 2.6 is going to be release pretty soon so I tested this and there are no issues.

You can download the Beta and use or you can create the table prior to the import. This will bypass the bug.

Debbie

Is there any link to o the 2.6 bêta download page ?

http://www.toadworld.com/Products/ToadforDataAnalysis/BetaProgram/tabid/186/Default.aspx

Indeed It solved my problem. Now I’m able to import my excel data in Oracle, with TOAD. Thanks.

Excellent. Good to hear.

Debbie