DTS Import of Excel with CRLF in column

[jmikow]

We currently need to import data which is sent in from an outside source. The data is sent in an Excel CSV file.

Where we are running into trouble is that some of the rows in the file have a column which contains a CRLF in it. This causes the DTS package to get an error and all of the subsequent rows to not be imported.

What is the best way to have the CRLF characters in the column removed so the import will succeed?

Unfortunately, we are not able to have them change their export to remove the CRLFs that we don't want.

Any help or suggestions would be greatly appreciated.

Josh

 

[dbomrrsm]

Probably not the best sollution but I have had the same problem before myself - I got round it by saving the Excel file as a tab delimited text file and then imported this text file with DTS.

DBomrrsm

 

[jmikow]

After some more investigation we found that it was a TAB character that is causing the trouble. In the DTS we were using an ActiveX transformation to copy the data from the source to the destination. While this worked for all of our other columns and DTS packages, this one did not.

I decided to try a Copy Column instead of an ActiveX transformation and it appears to work. Not sure what the difference is, but we're going to use this for now.

The weird thing is that in the ActiveX transformation if we did a string manipulation on it (left(), trim(), etc.) it would enter spaces into the column. If we didn't it would enter a NULL into the column.

Either way I think we have gotten around it for now.

Thanks for your suggestions, but we need this to be done without any user interaction.

Josh