SQL Server 2000 DTS Issue of a csv file

Question

I am trying to import a csv file that has a text qualifier of " and a column delimiter of comma.  There are also commas within some of the data of the field. For example, "Fred Willam Shop, Inc.".  When I try to use SQL Server 2000 DTS import wizard to import the file specifiying that it has a text qualifier of " and a delimiter of comma it gives the error below:

   Error Source: Microsoft Data Transformation Services Flat File Rowset Provider

   Error Description: Invalid delimited data: text qualifier must be followed by a column delimiter(except the last column).

  Context: Error calling OpenRowSet on the provider.

 

Has anyone run into this?  Is there a fix for it or should I have the supplier of the file change the column delimiter to tab delimited?

 

Thanks.

Answer 1

If this is a one-off requirement why not correct the data on the source file to ensure it works.

Answer 2

This is not a one off thing.  It is going to be a weekly event.  Any suggestions?

Answer 3

Use a different delimiter -perhaps the pipe symbol [ | ], ascii 124.

Answer 4

Thank you all for your help.  I have just requested that the supplier give us a different delimiter.

Answer 5

crusso

Make sure your row delimiter is correct. Typically its {CR}{LF}, {CR}, {LF}. Its depending upon from which environment you are generating csv file. I got same kind of error before and it was because I was selecting incorrect row delimiter.