Optionally Enclosed '"' does not work for some reason

[ISUTri]

Hi, I am very new to Oracle and SQL Loader, this is actually my first sqlldr program that I have written. What I am wondering is why this does not work when it appears to work for everyone that I have found on the web.

My Control File is below
OPTIONS (SKIP = 1)
LOAD DATA
INFILE '/VendorRepository/vendor/test.csv'
REPLACE
INTO TABLE xxhh_ap_suppliers_stg
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(VENDOR_NAME char,
ADDRESS1 char,
CITY char,
STATE char,
ZIP char,
NUM_1099 char
)

My Example data is
VendorName
"Jimmy, Johns", 123 somewhere, TN, 55555, 123456789

Jimmy, Johns should load into Vendor Name but when I run sql loader it ends up in the bad file.

Thanks

 

[Dagon]

There should also be a log file from the SQL*Loader session which will give you an error message explaining why it ended up in the bad file.

For Oracle-related work, contact me through Linked-In.

 

[taupirho]

Could be a conflict because you have a comma in the name which you have also specified as a field delimiter. 2 options

1) Remove the comma in "Jimmy, Johns"
2) delimit your fields by something else such as pipe ('|')
character and alter your FIELDs TERMINATED CLAUSE appropriately


In order to understand recursion, you must first understand recursion.

 

[Gruuuu]

Could be a conflict because you have a comma in the name which you have also specified as a field delimiter

This is exactly what OPTIONALLY ENCLOSED BY was designed for, so no.

Dagon's suggestion should provide the most insight.

 

[ISUTri]

Thanks for the responses.

It looks like there is some dummy data added on to each of my SSN's. So it's nothing to do with what i thought (The Optionally enclosed). I'm guessing these are carriage return values. When I increase the destination field length to 10 it runs fine. I've been looking to see how I can trim my data so I don't have this coming through. Is there a way to trim in the Control file so I just pass the 9 characters I want to?

 

[Gruuuu]

One thing that may be causing problems is that the double quote character may actually be smart quotes.

<---these guys

So maybe check for that?

 

[Gruuuu]

Is there a way to trim in the Control file so I just pass the 9 characters I want to?

Ah, yes.

http://goo.gl/mhhzt

(I had to use a URL shortener because of the * in the URL)

 

[ISUTri]

Thanks for your responses I found a fix to my solution. I added "TERMINATED BY WHITESPACE" after the last column. I've posted my final control file below.

OPTIONS (SKIP = 1)
LOAD DATA
INFILE '/VendorRepository/vendor/test.csv'
REPLACE
INTO TABLE xxhh_ap_suppliers_stg
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(VENDOR_NAME char,
ADDRESS1 char,
CITY char,
STATE char,
ZIP char,
NUM_1099 char TERMINATED BY WHITESPACE
)