BULK INSERT messing up import of CSV file with a comma in it

  • Hi everyone

    I am using BULK INSERT to load a CSV.  I have noticed a problem when a value has a comma in it.  The value is:

    "1,926.7%"

    This value is for ColumnA.  Instead of "1,926.7%" going into ColumnA what is happening is that the string value is being split.  "1" is going to ColumnA and "926.7%" is going to ColumnB (ie the next column to ColumnA).  This is wrong.  The entire value "1,926.7%" should be going to ColumnA.  How can I fix this?

    ( 'BULK INSERT #REPORT_NEW
    FROM ''' +
    @FILEPATH +
    ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);'
    )

    Thank you

  • This is one of the main problems with CSVs. (Horrible things)

    If you can get the CSV generated differently then either get every field enclosed in "" or use something like ~, instead of a comma, as the field separator.

    If you have no control over the CSV generation you will have to try and preprocess the file yourself to change the field separator.

    Good luck.

    • This reply was modified 1 month, 1 week ago by Ken McKelvey.
  • if the file is a valid CSV file, then use the FORMAT=CSV and it will load that record correctly - example you have above is not using the format keyword so that is why it fails.

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply