Loading multi-line text fields using SQL*Loader 1

[slicendice]

Hi
I have a scenario whereby I need to load data into a table using SQL*Loader. The fields are pipe seperated and one of the fields contains text that may be split onto several lines.

The target table is defined as:

COL1	NUMBER(8)
COL2	VARCHAR2(10)
COL3	VARCHAR2(500)
COL4	NUMBER(1)

Each record in the source file has a blank field at the end and therefore there is a pipe character at the end of every record. So an example of how the data looks in the source file is:

1|abc|one

two


three
four|1|
2|def|five six seven|2|

So the first record has field three split over several lines. I'm using CONTINUEIF in the control file in order to handle this, as follows:

LOAD DATA
INFILE 'test1.csv'
BADFILE 'test1.bad'
DISCARDFILE 'test1.dsc'
TRUNCATE
CONTINUEIF LAST <> '|'
INTO TABLE table1
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
 col1		INTEGER EXTERNAL,
 col2        	CHAR,
 col3		CHAR,
 col4		INTEGER EXTERNAL
)

Now this is working OK up to a point - it successfully reads the multi-line field and loads it into the appropriate field in the target table, but in doing so it loses the CR/LF's and hence the data is all crammed onto one line.

Does anyone know how I can preserve the CR/LF characters so that they get imported into the table? I've tried using the PRESERVE keyword but it doesn't make any difference

Thanks very much

 

[taupirho]

My system is on VMS so your str field definition ( part of the INFILE line) may need amending.

$ type tom.txt
1|abc|one

two


three
four|1|
2|def|five six seven|2|
$

$ type tom.ctl
LOAD DATA
INFILE 'tom.txt' "str X'7C0A'"
BADFILE 'tom.bad'
DISCARDFILE 'tom.dsc'
TRUNCATE
INTO TABLE tom3
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
col1 INTEGER EXTERNAL,
col2 CHAR,
col3 CHAR,
col4 INTEGER EXTERNAL
)

$

$ sqlplus /
SQL> desc tom3;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER(8)
COL2 VARCHAR2(10)
COL3 VARCHAR2(500)
COL4 NUMBER(1)

SQL> select * from tom3;

no rows selected

SQL>

$ sqlloader / control=tom.ctl

SQL*Loader: Release 9.2.0.2.0 - Production on Wed May 21 14:56:08 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2
$ sqlplus /
SQL> select * from tom3;

COL1 COL2
---------- ----------
COL3
--------------------------------------------------------------------------------
COL4
----------
1 abc
one

two


three

COL1 COL2
---------- ----------
COL3
--------------------------------------------------------------------------------
COL4
----------
four
1

2 def
five six seven
2


SQL>

$

Hope this helps


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

 

[slicendice]

Many thanks for your response....it *nearly* works perfectly!

I tried using the STR clause you mention above, and it loads the first record OK (the one with the multi-line field in it) but it doesn't load the second record. From looking at the log file it appears like it doesn't even recognise the second record as being there. This is from the log file:

Control File:   test1.ctl
Data File:      test1.csv
  File processing option string: "str X'7C0A'"
  Bad File:     test1.bad
  Discard File: test1.dsc 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   Last non-white character != 0X7c(character '|')
                Preserving continuation characters as data
Path used:      Conventional

Table TABLE1, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   |       CHARACTER            
COL2                                 NEXT     *   |       CHARACTER            
COL3                                 NEXT     *   |       CHARACTER            
COL4                                 NEXT     *   |       CHARACTER            


Table TABLE1:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  66048 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

I wondered if this was anything to do with the character set on your machine (VMS) being different to mine (Win XP). I checked out the ASCII codes for LF and it's x0A as you've put above. I've tried adding a CR (x0D) instead, and combining the two but it still doesn't load the second record. Any ideas??

Thank you again...

 

[taupirho]

This

"str X'7C0D0A'"

works for me in windoze


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

 

[karluk]

The following minor modification of taupirho's control file works for me on Windows. Did you try this this specific combination for your record terminator?

LOAD DATA
INFILE 'tom.txt' "str X'7C0D0A'"
BADFILE 'tom.bad'
DISCARDFILE 'tom.dsc'
TRUNCATE
INTO TABLE table1
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
 col1        INTEGER EXTERNAL,
 col2            CHAR,
 col3        CHAR,
 col4        INTEGER EXTERNAL
)

 

[slicendice]

Sorted! When adding the STR clause to my control file, I'd forgotten to remove the CONTINUEIF clause...and they obviously don't get on too well together!

Thanks very much taupirho and karluk, your help is very much appreciated.