| loading text with carriage returns into a varchar2 [message #69308] |
Thu, 06 December 2001 19:05  |
Todd Peterson
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
I would like to use sqlldr to load text data that spans multiple lines into a table with a varchar2 column. Have not seen an example anywhere of doing this. Tried things like using line1nline2 or line1 || chr(10) || chr(13) line2. They haven't worked, they just store it literally. Looked at concatenate operator, but don't understand how it will help me. Have seen an external file loaded into a CLOB example. But, this would require me to create over 10,000 text files and modification of the column type.
Thanks for any help.
Todd Peterson
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: loading text with carriage returns into a varchar2 [message #69312 is a reply to message #69310] |
Fri, 07 December 2001 06:17  |
Todd Peterson
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
Tried that, doesn't do what I desired (I want to keep the carriage returns, the loader removes them). So, how do you represent a 'special' character? Are there 'escape sequences' which allow hex chars to be specified inline?
For example, if I wanted the record to contain:
line1
line2
line3
It ends up with line1line2line3.
Here is what I am using:
tabledef:
create table temp (int id, val varchar2(512));
load ctl file:
LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF THIS (1:2) = '%%'
PRESERVE BLANKS
INTO TABLE temp.temp
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"' (id, val)
BEGINDATA
%%1,line1
%%line2
%%line3
line4
what ends up in table:
SQL> select * from temp;
ID VAL
---------- ------------------------------
1 line1line2line3line4
----------------------------------------------------------------------
|
|
|
|
| Ah ha!! [message #69313 is a reply to message #69312] |
Fri, 07 December 2001 06:43 |
Todd Peterson
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
This works:
ctl file:
LOAD DATA
INFILE 'example.dat' "str '|'"
TRUNCATE
INTO TABLE temp.temp
FIELDS TERMINATED BY ","
(id, val CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>')
example.dat:
1,<startlob>line1
line2
line3<endlob>
|2,<startlob> "Brokaw",
"in Dallas"<endlob>
what I get in table:
ID VAL
---------- ------------------------------
1 line1
line2
line3
2 "Brokaw",
"in Dallas"
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
