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 linethe 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
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment