-
I'm trying to use sqlldr to load a pipe-delimited flatfile into a table with a LONG datatype. That's not hard, except that the text to be loaded into the LONG column is full of cr/lf (they're resumes, and the data owner wants the formatting preserved).
Control file looks like this:
LOAD DATA
INFILE 'MD029candidateinfo.txt'
BADFILE 'MD029candidateinfo.bad'
APPEND
INTO TABLE candidates
FIELDS TERMINATED BY "|"
(
candidateid,
active,
resumertf char(200000) ENCLOSED BY '"' AND '"')
Datafile looks like this:
12345|1|"Resume of Larry Ellison...^M
resumetext....."^M
67890|2|"Resume of Bill Gates...^M
resumetext......"^M
The '^M' characters are the way Unix displays the cr/lf values. I've tried substituting them with various other characters, however my log and bad files from SQLLDR indicate that SQLLDR thinks the '^M' character signals the start of the next record. Please help!
Thanks,
Joe
-
are you using Unix or NT ??
-
I'm using Compaq Tru 64 Unix.
-
I know that the LONG datatype defaults to 80 charachters unless otherwise specified.
MH
-
first thing to do is to get rid of the ^M, you can either make your FTP again (since I guess you did a FTP between a NT and a Unix Box) using binary mode (simply type binary), or you can vi your datafile and do something like :
:%s/^M//
(to get ^M, you type Ctrl-V and then Enter)
-
Thanks. For future reference in the forums, I removed the '^M' and added a '#\n' at the end of each record (the # was chosen arbitrarily, and could be any symbol combined with the '/n' which indicates a newline in Unix. Control file then looks like this:
LOAD DATA
INFILE 'MD029candidateinfo2.txt' "str '#\n'"
BADFILE 'MD029candidateinfo2.bad'
APPEND
INTO TABLE candidates
FIELDS TERMINATED BY "|"
(
candidateid INTEGER EXTERNAL,
active INTEGER EXTERNAL,
resumertf char(200000)ENCLOSED BY '"' AND '"')
Each field is delimited by pipe, with resume text further enclosed in quotes.
