none
remove double quotes on bulk insert RRS feed

  • Question

  •  The import data is bar(|) delimited and has double quotes on the fields. When I import to Staging table all fields need fixed to remove double quotes. I can do that with SP, but is there a way to remove during Import?

       Sample Data:

     "7763"|"abc comp"|"644.98"

        -- Insert statements for procedure here
    BULK INSERT invheader_stg from 'C:\invhead.txt' WITH
    (
            DATAFILETYPE='char',
            FIELDTERMINATOR='|',
            ROWTERMINATOR = '\n',
            FirstRow=1
    )

     Thanks.

    Thursday, March 14, 2019 2:55 PM

Answers

  • You may use a format file to see whether you can get a correct result.

     Do you have a fixed format or variable format for your import files? It is hard to work with changeable format file import.

    • Marked as answer by hart60 Thursday, March 14, 2019 11:48 PM
    Thursday, March 14, 2019 4:15 PM
    Moderator

All replies

  • I have a solution but it needs one fix:

    ...
      WITH
         (
            FIELDTERMINATOR ='"|"',
            ROWTERMINATOR ='"\n"',
            FirstRow=1
         );

    There is a catch for this code: you need to get rid of an extra double quote at the end of the last column.

    There is another way if doing it manually in SSMS by using Import and Export Wizard.

    The key part is to use a ” as Text qualifier for the import

    Thursday, March 14, 2019 3:15 PM
    Moderator
  •  It actually had an extra double quote on the first column and last column(first record\last record) loaded. If that
    will always be the case I can modify my SP to remove that from first and last column in table.


     

    Thursday, March 14, 2019 3:34 PM
  • Yes, you can use a code to fix the extra double quotes after the bulk insert.
    Thursday, March 14, 2019 3:41 PM
    Moderator
  •  Any way with this method to handle a few fields that don't have the double quotes?

     "7763"|"abc comp"|"644.98"|0|"88.99|1|

      Thanks.

    Thursday, March 14, 2019 4:09 PM
  • You may use a format file to see whether you can get a correct result.

     Do you have a fixed format or variable format for your import files? It is hard to work with changeable format file import.

    • Marked as answer by hart60 Thursday, March 14, 2019 11:48 PM
    Thursday, March 14, 2019 4:15 PM
    Moderator
  • There is no good way to do what you want with BCP.  You need to import the data into a "stage" table, replace the " and then insert/update to your target table.

    The other option is to use SSIS which will handle the double quotes natively.

     
    Thursday, March 14, 2019 6:02 PM
    Moderator
  • I agree with Tom. Import your file with | delimiter into a stage table and clean the stage table by removing these extra double quotes.
    Thursday, March 14, 2019 6:20 PM
    Moderator
  •  I used the stage table load and cleanup option.

     Thanks ALL.

    Thursday, March 14, 2019 11:48 PM