Skip to Main Content
  • Questions

  • SQL loader not loading if column value in multiple lines and value enclosed in double quotes.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, nnv.

Asked: March 01, 2017 - 1:01 pm UTC

Last updated: March 07, 2025 - 1:06 am UTC

Version: Oracle 11 G database

Viewed 10K+ times! This question is

You Asked

hi ,

when I am loading data for one column value speeded in multiple columns and its failed.
Here is the example and syntax for control file,record failing due to values in multiple lines for DESCRIPTION column

"0","test0","123235","testitem0","test","sample0"
"1","test1","2323","testitem","test ,part
size 6
size 9","sample"
"2","test2","12323","testitem2","test","sample2"

LOAD DATA
CHARACTERSET 'AL32UTF8' LENGTH SEMANTICS CHAR
INFILE 'C:\Export.csv'
BADFILE 'Export.bad'
DISCARDFILE 'Export.dis'
INTO TABLE Export
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
,JOB_ID CHAR(255) NULLIF JOB_ID = ""
,JOB_NAME CHAR(255) NULLIF JOB_NAME = ""
,JOB_CODE CHAR(255) NULLIF JOB_CODE = ""
,ITEM_NAME CHAR(255) NULLIF ITEM_NAME = ""
,DESCRIPTION CHAR(2000) NULLIF DESCRIPTION =""
,TYPE CHAR(255) NULLIF TYPE = ""
)

and Connor said...

You need *something* that tells us when a line is 'complete' and when the next one starts. Otherwise the default will be the standard new line characters, hence your problem.

From one of the AskTom publications


Use the STR Attribute

This is perhaps the most flexible method of loading data with embedded newlines. Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line—the newline is no longer “special.”
I prefer to use a sequence of characters, typically some special marker, and then a newline. This makes it easy to see the end-of-line character when viewing the input data in a text editor or some utility, as each record still has a newline at the end of it. The STR attribute is specified in hexadecimal, and perhaps the easiest way to get the exact hexadecimal string we need is to use SQL and UTL_RAW to produce the hexadecimal string for us. For example, assuming we are on UNIX where the end-of-line marker is CHR(10) (linefeed) and our special marker character is a pipe symbol (|), we can write this:

SQL> select utl_raw.cast_to_raw( '|'||chr(10) ) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
-------------------------------------------------------------------------------
7C0A

which shows us that the STR we need to use on UNIX is X'7C0A'.
Note On Windows, you would use UTL_RAW.CAST_TO_RAW( '|'||chr(13)||chr(10) ).

To use this, we might have a control file like this:

LOAD DATA
INFILE demo.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS
)

So, if our input data looks like this:

$ cat demo.dat
10,Sales,Virginia,This is the Sales
Office in Virginia|
20,Accounting,Virginia,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,This is the Consulting
Office in Virginia|
40,Finance,Virginia,This is the Finance
Office in Virginia|

where each record in the data file ends with a |\n, the previous control file will load it correctly.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 02, 2017 - 8:47 pm UTC

after trying this option its failing with the below error
SQL*Loader-510: Physical record in data file (C:\Export.csv) is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

I tried readsize,but its giving error
could you please help right syntax to avoid above error?
Connor McDonald
March 03, 2017 - 5:26 am UTC

This means we were constructing a "row" and we got to 1048576 bytes before we got to the end-of-line character you nominated.

So there is an inconsistency between the data and the special end-of-line character you put in the control file.

are you missing the part that says enclosed by ""

A reader, March 06, 2025 - 3:00 pm UTC

The comment is enclosed by "", so how do you get SQLLDR to read until the end ". I am having the same issue. I have the
Infile data.csv
append into tablename fields terminated by ',' optionally enclosed by "

my data.csv file looks like this:
5826431,5826431,,,New,17270000,,,,fd6eda3d-79c8-4d99-8507-8afbd4148bf9,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,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

but I am still getting the error
SQL*Loader-510: Physical record in data file (data.csv) is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Connor McDonald
March 07, 2025 - 1:06 am UTC

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
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.