Home » RDBMS Server » Server Utilities » Import data error (10.2.0.3, Windows 2003)
Import data error [message #413815] Fri, 17 July 2009 13:43 Go to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Hi all,

I've 2 xls files with data that I need to import into my database. I have Toad 9.5, I'm using the import wizard to import this xls file.

Excel file has data like this:(40000 rows)


Series_No	Series_Date	Ref_no
----------------------------------------------------------
402	        20061120	D975
343         	20061120	75
143	        20061120	G175


I'm importing the data into temp table:

create table temp (
v_no varchar2(40)  ,
v_date   date,
c_no  number(22));


I get this error Invalid Value for field 'v_date'.

Any suggestions, please?

Thanks
Re: Import data error [message #413816 is a reply to message #413815] Fri, 17 July 2009 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any suggestions, please?

Use SQL*Loader and get rid of TOAD.

quoting BlackSwan:
Those who live by the GUI, die by the GUI.


Regards
Michel
Re: Import data error [message #413817 is a reply to message #413815] Fri, 17 July 2009 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
On a properly configured PC, Excel can directly login to Oracle via ODBC & "push" the data into a table.
Re: Import data error [message #413818 is a reply to message #413815] Fri, 17 July 2009 13:51 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
I've used SQl loader with dat files, but I only have xls file, I thought SQL loader is not compatible with xls?

Thanks
Re: Import data error [message #413819 is a reply to message #413818] Fri, 17 July 2009 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Convert you xls file to a csv one. Excel can do it.

Regards
Michel
Re: Import data error [message #413820 is a reply to message #413815] Fri, 17 July 2009 13:56 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Okay. I just saved as the file as 'temp.csv' and tried using sql loader..


test.ctl:

LOAD DATA
INFILE 'c:\temp.csv'
APPEND INTO TABLE temp

C:\>sqlldr userid=test/pwd@test control=c:\test.ctl log=c:\test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Jul 17 14:46:06 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 5.
Expecting "(", found end of file.



For dat file, I usually write the ctl file as :
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'

But for csv file, the data is in columns, do I need to mention any specific format for this?

Thanks much

Re: Import data error [message #413824 is a reply to message #413820] Fri, 17 July 2009 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
csv IS dat file.

Regards
Michel
Re: Import data error [message #413825 is a reply to message #413815] Fri, 17 July 2009 14:14 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
I did save as csv from xls file. But, it tells me that the file is not compatible to save as csv file.

Do I need to find like a conversion tool to convert this into a dat or csv file to use the sql loader?

Thanks for your help
Re: Import data error [message #413827 is a reply to message #413825] Fri, 17 July 2009 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then your file is unloadable... or you did not do it in the correct way.

Regards
Michel
Re: Import data error [message #413829 is a reply to message #413815] Fri, 17 July 2009 14:47 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
The xls file is sent by the client, only has 3 column data with one column as date field with 40000 rows.

For testing purpose, I've created temp.xls with 3 rows from the client's xls file.

temp.xls

Series_No	Series_Date	Ref_no
----------------------------------------------------------
402	        20061120	D975
343         	20061120	75
143	        20061120	G175


I'm trying to load this data into temp table:

create table temp (
v_no varchar2(40) ,
v_date date,
c_no number(22));
[code]

I'm not able to convert this xls into csv file(dat file)..

Is there any other way I can load this data into the table?

Thanks again
Re: Import data error [message #413830 is a reply to message #413815] Fri, 17 July 2009 14:49 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here it is, step by step walkthrough.

I have created an Excel file, containing 3 columns with 3 records (just as your sample data says). Next step is to save it as a CSV file; "Save as type" should be "CSV (Comma delimited) (*.csv)". By default, it is named "Book1.csv". On my computer, it is still to be opened with MS Excel; however, I don't want to do that as I prefer a simple text editor as Notepad. Therefore, I'll rename it to "Book1.txt". If you do that as well and open the file, its contents should be as follows:
402;20061120;D975
343;20061120;75
143;20061120;G175
As you can see, delimiter is semi-colon. Perhaps it will be different on your computer (in a way that you'll really have a "comma" delimited file, not "semi-colon" delimited file). In that case your control file will slightly differ than mine.

OK, let's create a table (that's trivial) and write a control file; it is obvious that data in CSV file are ordered in reverse order if compared to CREATE TABLE statement. In other words, you can't expect to load value "D975" into a NUMBER datatype column.
load data
  infile 'book1.txt'
  replace
into table temp
fields terminated by ";"
(c_no,
 v_date "to_date(:v_date, 'yyyymmdd')",
 v_no
)

Here's the loading session:
c:\temp>sqlldr scott/Tiger control=book.ctl log=book.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pet Srp 17 21:42:49 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3

c:\temp>

Finally, the result:
SQL> select * from temp;

V_NO                                     V_DATE           C_NO
---------------------------------------- ---------- ----------
D975                                     20.11.2006        402
75                                       20.11.2006        343
G175                                     20.11.2006        143

SQL>

Now, try to follow my steps; I guess you shouldn't have too many problems. If you, however, do, do come back, show us what you did (copy-paste!) and we'll provide additional help if necessary.
Re: Import data error [message #413833 is a reply to message #413815] Fri, 17 July 2009 15:09 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Great!!! It worked. When I saved as to csv, I didnt know I need to rename the csv file to .txt.

Thanks for your great help.
Re: Import data error [message #413844 is a reply to message #413833] Fri, 17 July 2009 15:44 Go to previous message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I sincerely doubt that file extension makes the difference; I've just tried to load it with the .CSV extension, and everything went just fine. Therefore, perhaps you should review what we've done and find real cause of the problem (such as data format, for example).
Previous Topic: Not showing the exact language after import
Next Topic: external tables
Goto Forum:
  


Current Time: Mon Dec 05 04:38:02 CST 2016

Total time taken to generate the page: 0.12942 seconds