Bulk insert CSV file with text qualifier (") and comma within

• kai-753082

  Old Hand

  Points: 301

  More actions

  November 15, 2016 at 3:48 pm

  #401060

  Hi,

  I am trying to insert from a csv file using bulk insert with a format file. The source file has 65 columns of which I need 11, so I use the format file to select which columns should be imported. The file contains two columns that have a text delimiter (") within which commas may occur. when using "," as field terminator the script executes, but the columns right of the text delimiter columns will not align in any record that has a comma in the text field - even though these columns are not selected.

  I found some article where it was suggested to use "\",\"" as field terminator - see format file records below, but I am getting an error message:

  Msg 4832, Level 16, State 1, Line 1

  Bulk load: An unexpected end of file was encountered in the data file.

  Msg 7301, Level 16, State 2, Line 1

  Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

  41SQLCHAR0100"\",\""0Registration_DescriptionSQL_Latin1_General_CP1_CI_AS

  43SQLCHAR0100"\",\""0Rev_Col_DescSQL_Latin1_General_CP1_CI_AS

  any idea how I can accomplish this? What is the proper string to use as field delimiter to indicate double quotes as delimiter?

  thanks,

  Kai