Re: sqlldr terminated by ',' with a ',' in one of my fields!

ok .. do some homework ...

method 1) hint: "filler" fields in sql*ldr control file.

method 2) Your file if it looks exactly (in format) like this then its a fixed format file. read doco (tahiti.oracle.com -> FM -> utilities).

method 3) use a simple utility like sed/awk/perl to get the data file in your format .. and then load it.

method 4) if you are on 9i: load it by utilizing "external table"

method 5) create a view (3 cols) in the database and write instead of trigger on it to load the data in the real table.

Anurag

"JohnL" <jmfl54_at_hotmail.com> wrote in message news:3e707d7f.0306111359.769c0caa_at_posting.google.com...
> given this table
> create table date_test (
> DATE_TESTER DATE,
> scrap varchar2(50) );
>
> this data in my load file
> 02-02-2002,12:12:24,kjkjjh
> 01-02-2002,12:12:24,lkjh
> 03-02-2002,12:12:24,oiuy
> 04-02-2002,12:12:24,mnb
> 05-02-2002,12:12:24,uyui
>
> how do I use sqlldr to ram this into my data base?
>
> I've tried all kinds of variations of this:
> LOAD DATA
> INFILE date.csv
> REPLACE
> INTO TABLE date_test
> FIELDS TERMINATED BY ','
> (
> DATE_TESTER "to_date(:DATE_TESTER,'DD-MM-YYYY,HH24:MI:SS')",
> scrap
> )
>
> and
>
> LOAD DATA
> INFILE date.csv
> REPLACE
> INTO TABLE date_test
> FIELDS TERMINATED BY ','
> (
> d_day FILLER ,
> t_time FILLER ,
> scrap,
> DATE_TESTER "to_date(:REF(d_day) ||' ' ||:REF(t_time),'DD-MM-YYYY
> HH24:MI:SS')"
> )
>
> and
>
> LOAD DATA
> INFILE date.csv
> REPLACE
> INTO TABLE date_test
> FIELDS TERMINATED BY ','
> (
> a FILLER ,
> b FILLER ,
> DATE_TESTER "to_date(:a ||' ' ||:b,'DD-MM-YYYY HH24:MI:SS')",
> scrap
> )
>
> and
>
> LOAD DATA
> INFILE date.csv
> REPLACE
> INTO TABLE date_test
> FIELDS TERMINATED BY ','
> (
> a FILLER CHAR(10),
> b FILLER CHAR(8),
> DATE_TESTER "to_date(:REF(a) ||' ' ||:REF(b),'DD-MM-YYYY
> HH24:MI:SS')",
> scrap
> )
>
>
> I've also messed with the table a bit so some of my control files
> (above) may not match the table at the beginning.
>
> I have been able to get it to work by adding another column and using
> it to build the date field...but I don't want a column of junk in my
> table.
>
>
> Help help help help.
>
>
> Thanks in advandce
Received on Wed Jun 11 2003 - 20:40:40 CDT