Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader Inserts chr(13) and chr(10) in the first column of every row.

RijuApr 15 2013 — edited Apr 15 2013

Hi,

I have exported a data in a pipe delimited file using TOAD in one database. Now I want to load the data in my local database using SQL Loader. However every time I try to load the data a double quote followed by a new line is entered for the first column of each row. Unfortunately the delimited file is very big and hence can't be posted here. However I tried the same with a customized table and its data and found the same problem. Below are the table structures and control file that I used.

create table test_sql
(
a varchar2(30),
b date
) ;

insert into test_sql values('51146263',sysdate-3);
insert into test_sql values('51146261,sysdate-1);

EXPORTED PIPE DELIMITED FILE_

A|B|!##!
51146261|04/14/13 4:55:18 PM|!##!
51146263|04/12/13 4:55:32 PM|!##!

create table test_sql1 as select * from test_sql where 1=2;

CONTROL FILE_

OPTIONS(SKIP=1)
LOAD DATA
INFILE 'C:\Users\Prithwish\Desktop\Test.txt' "str '!##!'"
PRESERVE BLANKS
INTO TABLE TEST_SQL1
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
A CHAR(2000),
B DATE "MM/DD/YYYY HH12:MI:SS AM"
)

select * from TEST_SQL1;

After this when I paste it in notepad I get the following result

A B
"
51146261" 14-APR-0013 16:55:18
"
51146263" 12-APR-0013 16:55:32.

I have no idea how the quotes or the newline appear. Is this a Toad bug? Any help would be greatly appreciated. Really urgent.

Thanks in advance

Regards