none
Bulk Insert remove extra quotes

    Question

  • I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

    Example 1: """"This is a just a test."""

    Results should be "This is just a test"

    Example 2: """This is only a test"" and nothing more"

    Results should be:"This is only a test" and nothing more.

    Now.

    If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

    Question: Is there a way or a switch in Bulk Insert  that I could use to get rid of the extra quotes?

    Below is my syntax

    SET @sql ='BULK INSERT '+ @table_name + ' FROM "' + @conversion_data_in + '\' + @source_file + '"
          WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'


    Thanks
    Conrad...

    Tuesday, August 08, 2006 5:10 PM

Answers

  • There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.
    Tuesday, August 08, 2006 5:21 PM

All replies

  • There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.
    Tuesday, August 08, 2006 5:21 PM
  • Thanks for the information.

    When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

    On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert. 

    Keep in mind, that the quotes may not appeared in all of the fields.

    Thanks

    Conrad...

    Tuesday, August 08, 2006 5:55 PM
  • As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.
    Tuesday, August 08, 2006 6:42 PM
  •  Umachandar Jayachandran - MS wrote:
    As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

    Hello,

    I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

    Any links to documentation or examples would be welcome!

    Thanks a lot

    Detlef

    Wednesday, January 17, 2007 9:54 PM