You Asked
I get a .csv log file dropped from a microsoft server to a linux server using winSCP. The column REASON, is enclosed with "" and includes LF. Here is a 2 record snippet of the file:
5826431,5826431,,,New,17255483,,,,2e4cf893-078d-45b3-b548-2dc3c970ff25,5826431,1021715,"The following errors occurred:
Error while cancelling backordered line(s) 10
Sales document overall status is completed. Item cannot be Updated",909652,S2323358
5826431,5826431,,,New,17255481,,,,719e566e-f143-4126-8827-303d3e7d1aab,5826431,1021715,"The following errors occurred:
Error while cancelling backordered line(s) 10
Sales document overall status is completed. Item cannot be Updated",909652,S2323358
Here is the control file I use:
load
infile 'data.csv'
APPEND into table example_table
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(
LOG_COL_1 nullif LOG_COL_1=BLANKS "to_number(:LOG_COL_1)"
,LOG_COL_2
,LOG_COL_3
,LOG_COL_4_TIME nullif LOG_COL_4_TIME=BLANKS "to_date(:LOG_COL_4_TIME,'MM/DD/YYYY HH24:MI:SS')"
,LOG_COL_5
,LOG_COL_6
,LOG_COL_7
,LOG_COL_8
,LOG_COL_9
,LOG_COL_10
,COL_1 nullif COL_1=BLANKS "to_number(:COL_1)"
,COL_2 nullif COL_2=BLANKS "to_number(:COL_2)"
,COL_3
,COL_4
,COL_5
,REASON
,COL_6
,COL_7
)
When I load, just as the ctl file is written, I get this error:
Record 1: Rejected - Error on table example_table , column REASON.
second enclosure string not present.
I have tried adding "str X'4C46'" (and variations to remove the LF code) after the csv file and then it says the record is too long.
I have also tried changing the def REASON to REASON char(4000) "replace(:REASON,'\n',' ')" enclosed by '"'
also trying variations of '\n':('LF','chr(10)','chr(13)||chr(10)', 'CRLF') etc.
All with no luck.
My unix machine is rhel 8. Please help me understand why this isn't working.
Thanks
and Connor said...
If your data spans "rows" in the file, then we need something to let us know what the logical end of a row is.
eg
SQL> create table t ( d varchar2(1000), n1 int, n2 int);
Table created.
and a data file of
"The following errors occurred:
Error while cancelling backordered line(s) 10
Sales document overall status is completed. Item cannot be Updated",909652,2323358
which I try to load it with
load data
infile "c:\tmp\x.dat"
into table t
replace
fields terminated by "," optionally enclosed by '"'
(d ,
n1 integer external,
n2 integer external
)
gives me failed rows because only the last row meets the logical format.
However, something like
"The following errors occurred:
Error while cancelling backordered line(s) 10
Sales document overall status is completed. Item cannot be Updated",909652,2323358~
with
load data
infile "c:\tmp\x.dat" "str '~'"
into table t
replace
fields terminated by "," optionally enclosed by '"'
(d ,
n1 integer external,
n2 integer external
)
works (ie, 1 row loaded) because now we know where the row ends
An added complexity in your case might be the Unix/Windows conversion - they have differing perceptions of what is an end of line (LF vs CR/LF). So perhaps take a look at the file in an editor that can reveal what end of line codes are present. You might need a 'dos2unix' command or equivalent.
If all else fails, using an external table instead of SQL Loader would open up all the power of SQL to manipulate the data.
Rating
(1 rating)