Home » RDBMS Server » Server Utilities » SQL*LOADER - Inserting values in wrong column
SQL*LOADER - Inserting values in wrong column [message #226188] Thu, 22 March 2007 15:05 Go to next message
bswarmer
Messages: 5
Registered: December 2006
Junior Member
Howdy,

I am attempting to load a file into Oracle via sqlldr. The input file is a tab delimited file that is made up of the follwing columns: CUST_NBR ,FIRSTNAME,LASTNAME,ADDRESS,ADDRESS2,CITY,STATE,ZIPCODE,ATT_CODE

The problem is that ADDRESS2 is often null and rather than inserting a null into the ADDRESS2 column, it is shifting all remaining columns over 1. So Address2 is actually populated with the CITY and CITY is populated with the STATE...

I have tried with and without the NULLIF statements and am out of ideas. Below is my control file.

OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE "CustInfo.txt"
BADFILE "C:\NCOA\test.bad"
DISCARDFILE "C:\NCOA\test.dsc"

TRUNCATE
INTO TABLE 70322_ALL_CUST
FIELDS TERMINATED BY " " OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS


(CUST_NBR NULLIF (CUST_NBR=blanks)
,FIRSTNAME NULLIF (FIRSTNAME=BLANKS)
,LASTNAME NULLIF (LASTNAME=BLANKS)
,ADDRESS NULLIF (ADDRESS=BLANKS)
,ADDRESS2 NULLIF (ADDRESS2=BLANKS)
,CITY NULLIF (CITY=BLANKS)
,STATE NULLIF (STATE=BLANKS)
,ZIPCODE NULLIF (ZIPCODE=BLANKS)
,ATT_CODE NULLIF (ATT_CODE=BLANKS))




Thank you in advance for any help with this.

Brad

Re: SQL*LOADER - Inserting values in wrong column [message #226191 is a reply to message #226188] Thu, 22 March 2007 15:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
See if any of these work.
http://www.orafaq.com/forum/m/71421/42800/?srch=blanks#msg_71421
http://www.orafaq.com/forum/m/2128/42800/?srch=blanks#msg_2128
Re: SQL*LOADER - Inserting values in wrong column [message #226193 is a reply to message #226191] Thu, 22 March 2007 15:57 Go to previous messageGo to next message
bswarmer
Messages: 5
Registered: December 2006
Junior Member
Thank you for the suggestions, I had not found those particular posts. However I am still having the same issue. Any more ideas?

Thanks Brad

Re: SQL*LOADER - Inserting values in wrong column [message #226199 is a reply to message #226193] Thu, 22 March 2007 16:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> The input file is a tab delimited file
Quote:
FIELDS TERMINATED BY " " OPTIONALLY ENCLOSED '"'

If the file is a true tab delimited file, you might want to use
FIELDS TERMINATED BY X'09' FIELDS TERMINATED BY 
Re: SQL*LOADER - Inserting values in wrong column [message #226410 is a reply to message #226188] Fri, 23 March 2007 10:10 Go to previous message
bswarmer
Messages: 5
Registered: December 2006
Junior Member
It appears that OPTIONALLY ENCLOSED '"' was causing the issue. As soon as I remove this the file loads correctly. I did not notice a difference between X'09' and "<tab>".

Thank you so much for your help... Case closed.

Brad
Previous Topic: Full DB Export Hang
Next Topic: Oracle impdp
Goto Forum:
  


Current Time: Fri Dec 09 02:23:15 CST 2016

Total time taken to generate the page: 0.23653 seconds