Issues Importing Delimited CSV/Text Files 1

[MCCooper]

We have an unusual problem in that we have had to use special delimiters for fields and rows to export data into text files. This is because some of the data in the fields contain commas, quotes and carriage returns. We have used the tilde (~) to separate fields and the vertical bar/pipe (|) character as a row delimiter.

The export to the text files works fine but when we try to import the text files, using DTS, back into a blank SQL table with the same table structure we sometimes find issues. If the last column in the table is a varchar and the last row to be added has no value (NULL) in that column then the pipe character is added to the field instead of a NULL value. The end of the file looks like this ~|. This behaviour does not occur for rows other than the last row.

If anyone else has encountered this issue I would love to know your workaround/fix.

 

[Glowworm27]

You should use a vertical Bar/Pipe | as a field Delimiter, and a Carrage return, or line feed as a row delimiter.

this works well with text files that contain commas, and other special characters.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[MCCooper]

We can't use the carriage return or line feed as a row delimiter as fields within the table contain these special characters. This makes the import process think that it has found the end of a row when really it has just found these special characters within a field. This makes for unpredictable results.

I have actually stumbled over a solution though. If I change the File Type of the source text file to OEM on the import then it handles the pipe character fine! Weird.