none
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.

    Friday, March 23, 2007 3:50 PM

Answers

All replies

  • If this is a one-off requirement why not correct the data on the source file to ensure it works.
    Friday, March 23, 2007 3:54 PM
  • This is not a one off thing.  It is going to be a weekly event.  Any suggestions?
    Friday, March 23, 2007 4:19 PM
  • Use a different delimiter -perhaps the pipe symbol [ | ], ascii 124.
    Friday, March 23, 2007 5:33 PM
  • Thank you all for your help.  I have just requested that the supplier give us a different delimiter.
    Friday, March 23, 2007 9:02 PM
  • 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.

    Friday, April 11, 2008 7:46 PM