Re: Importing CSV's to Oracle

"Russell Smith" <rsmith_at_serck-controls.co.uk> wrote in message news:f191bfd4.0201080259.a4cd811_at_posting.google.com...
> I have alot (3gb+) of CSV files to import into oracle, there are
> however a few problems. The data contains dates, in differing
> formats;
>
> 9/10/98 0:11
>
> or
>
> 8-SEP-1998 22:17:00
>
>
> I have used the following but need to be able to do both types at once
> (i don't want to have to pre-process the data before import), any
> suggestions?
>
> LOAD DATA
> INFILE 'C:\test.CSV'
> APPEND
> INTO TABLE SCXDOWNLOAD.HISDAT_A
> FIELDS TERMINATED BY ","
> (cbarow ,
> cbagranule,
> cbarecord,
> cbatime DATE "dd-MON-yyyy/hh24:mi:ss",
> token,
> hisflt,
> status,
> alarm_flag,
> modify_flag,
> insert_flag)
>
> Many thanks
>
> Russell Smith
> Software Projects

You can define file in which SQL*Loader stores all data that had some error loading. So in that file you will have all the data which has the date format 9/10/98 0:11. After loading your original file, you could load this file, with other ctl file for SQL*Loader.

Other idea is to have one temporary table which has filed cbatime not as date, but as varchar2. So after loading, you could call stored procedure which will determin what is the format of the date, convert it into a date and insert it into your main table. Received on Tue Jan 08 2002 - 15:10:52 CET