DTS - Catching an invalid csv file?

[stuartgmilton]

Hey folks,

I have regular imports of csv files running overnight. Anyway my main problem is that, if there is a corrupt row, ie a record with a double quote in it. The import just doesn't work! My delimiter is a double quote.

Is there a way to trap this error, and run some other code prehaps?

Stuart

 

[TomKane]

Stuart,

What method are you using to import your files?

Regards,
Tom

 

[stuartgmilton]

A standard datapump. I pass the whole contents of the file into a temp table, and then run my 'Import' stored proc off that data. However the datapump task fails with an error along the lines of 'row delimiter error' or something like that!

 

[TomKane]

Hi Stuart,

Is the 'row delimiter error' occurring at the load into the temp table or at the 'Import' stored procedure point?

Regards,
Tom

 

[stuartgmilton]

Within the load into temp table. As I say its at some when the dts job tries to open the csv file or very close to that, as the job doesn't transfer any rows at all.

 

[SQLSister]

why not use a | or a ~ as your delimiter - far less chance of having a record containing one of those characters.

If that is not an option you may need to write a preprocessin VB.net or someother laguage that can use regular expressions and find and clean up the data problem using regular expressions.

Questions about posting. See faq183-874

 

[AlexCuse]

I think your problem here is with the text qualifier, not the column/line delimiter. If you get a record with a double quote in it, your package is looking for your column delimiter immediately after the double quote. What is the exact error message when you try manually to run your datapump task?

It would be ugly, but you may be able to import to varchar fields in a staging table without using any text qualifier, and then do your 'cleansing' in this staging table, and then import to production.

Ignorance of certain subjects is a great part of wisdom