Re: SQLoader and CR/LF

From: Stuart Turton <sturton_at_maderich.demon.co.uk>
Date: Fri, 17 Aug 2001 05:45:34 GMT
Message-ID: <3B7CB118.1000803_at_maderich.demon.co.uk>

Load the data as VAR format
Specify the length of the logical line + (1 or 2 for the terminating LF or CR/LF) as a 5 digit number the start of each logical record.

e.g.

LOAD DATA
INFILE "*" "VAR"
APPEND
INTO TABLE APP_CHEMICAL
FIELDS TERMINATED BY ',' ENCLOSED BY '"' (
  "PDOC_COUNTRY" CHAR

,"PDOC_SERIAL" CHAR
,"PDOC_KIND" CHAR
,"VERSION" CHAR
,"CATEGORY" CHAR
,"CODE" CHAR -- LONG format

)
BEGINDATA
01464"~~","847098","A1","0","EP","[01]
<001> 018 | R00868 G1105 G1092 D01 D19 D18 D31 D50 D76 D86 F31 F30 |
H0328 | L9999 L2391 | L9999 L2108 L2095 | M9999 M2108 M2095 | S9999 S1047 S1014 | H0000
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999
Q9289 Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | N9999 N7078 N7034 N7023 | N9999 N7147 N7034 N7023 | K9552
K9483 | K9687 K9676 | K9676-R | K9712 K9676 | N9999 N6780-R N6655 | N9999 N6600 | N9999 N6177-R | N9999 N6224 N6177 | N9999 N6315 N6268
<004> 018 | R05268 D01 D11 D10 D23 D22 D31 D41 D50 D75 D85 F71 | A999
A475 | A999 A771
<005> 018 | R01669 D00 D09 C- 4A | A999 A135 | S9999 S1456-R | A999 A771
[02]
<001> 018 | R00363 G0555 G0022 D01 D12 D10 D51 D53 D58 D69 D82 F- 7A |
H0000 | S9999 S1058 S1014
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999
Q9289 Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | N9999 N7078 N7034 N7023 | N9999 N7147 N7034 N7023 | K9552
K9483 | K9687 K9676 | K9676-R | K9712 K9676
<004> 018 | R05268 D01 D11 D10 D23 D22 D31 D41 D50 D75 D85 F71 | A999
A475 | A999 A771
<005> 018 | R01778 D00 D09 C- 4A | A999 A135 | S9999 S1456-R | B9999
B5209 B5185 B4740 | A999 A771
[03]
<001> 018 | R00326 G0044 G0033 G0022 D01 D02 D12 D10 D51 D53 D58 D82 |
H0000 | S9999 S1285-R | P1150 | P1161
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999
Q9289 Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | B9999 B5221 B4740 | B9999 B5243-R B4740
"
00156"~~","847112","A1","0","EP","[01]
<001> 018 | P0000 | S9999 S1434 | S9999 S1649-R
<002> 018 | ND01 | ND07 | K9392 | N9999 N6440-R | Q9999 Q7330-R | K9416
"
00135"~~","9710289","A1","0","RN","200001U0610U0760S0822S0886U0927U1020S1278S1536U1547U1677U1681U1726U1732U1762U1794U1801U1903U1939U1966S "

bob sullivan wrote:

> My kneejerk reaction to this problem would be to write a 
> Perl script to scrub the data before trying to feed it to 
> SQL*Loader.
> 
> ~bob
> 
> Jacek Pichla wrote:
> 
>>Hi
>>
>>   Please anybody tell me how to import string data by means of SQLLoader in
>>which the the newline character is included.
>>The line in a text file looks like below:
>>
>>10, 001, "The line which includes
>>newline character", 1998
>>
>>I tried to use CONTINUEIF 0x0A or 0x0D but SQLoader doesnt take it.
>>
>>Thanks in advance
>>
>>--
>>Jacek Pichla
>>

Received on Fri Aug 17 2001 - 07:45:34 CEST