| Import data error [message #413815] |
Fri, 17 July 2009 13:43  |
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 #413820 is a reply to message #413815] |
Fri, 17 July 2009 13:56  |
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 #413825 is a reply to message #413815] |
Fri, 17 July 2009 14:14 |
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 #413829 is a reply to message #413815] |
Fri, 17 July 2009 14:47 |
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 |
 |
Littlefoot
Messages: 21823
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 #413844 is a reply to message #413833] |
Fri, 17 July 2009 15:44 |
|
Littlefoot
Messages: 21823
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
