bulk insert csv problems. 1) dd-mm-yyyy dates, 2) column shift in csv

[emccormick]

Have a pipe-delimited csv with 340k+ rows I'm importing into a staging table. It has 5 date fields, all in euro format dd-mm-yyyy, which I've defined as varchar in a staging table. I've written some t-sql which selects the data into a production table and converts the varchar dates into datetime. This works fine but around 60k+ rows into the insert it hits a shifted column (exists in the source csv) where there's some random text instead of a date. The convert fails, and the insert stops.

I can't do anything about the source csv. The percentage of these rows is very low, so I'd be happy to discard them, though I don't know how, and it would be nice to save them somewhere else.

My test t-sql looks like this:

bulk insert mystagingtable
from 'D:\mydir\myfile.csv'
with
(
firstrow=2,
fieldterminator = '|',
rowterminator = '\n',
datafiletype = 'char'
)

go

TRUNCATE TABLE myprodtable

BEGIN TRY

insert into myprodtable
(
column1
,column2
,column3
,column4
)
select
column1
,convert(datetime,column2,103) as gooddate
,column3
,column4
from mystagingtable
END TRY

BEGIN CATCH
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
PRINT ERROR_NUMBER()
END CATCH

Is there anything I can do in the catch block to continue on error?

Or do I have to loop over every one of the 340k rows one at a time, doing an isdate()?

Any suggestions greatly appreciated.

 

[AlexCuse]

You can do isdate() in a set-based fashion.

        insert into myprodtable
        (
        column1
        ,column2
        ,column3
        ,column4
        )
        select
        column1
        ,convert(datetime,column2,103) as gooddate
        ,column3
        ,column4
        from mystagingtable
        [b]where isDate(someCOlumn) = 1[/b]

If you've got a smalldatetime, check out the FAQ's

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page