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.

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

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

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

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

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

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

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.