none
Bulk Insert Failing with Data Conversion Error for DateTime column RRS feed

  • Question

  • Hello,

    The folloiwng bulk insert statement is being used to load a .csv file nightly into a SQL Server 2016 database:

    BULK INSERT powerbi.pbi_audit_log
    FROM 'E:\PowerShell\output\20191109.csv'
    WITH
    (
      FIRSTROW = 1,
      DATAFILETYPE='char', -- UTF-16
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      TABLOCK,
      KEEPNULLS -- Treat empty fields as NULLs.
    );


    It was running fine until the last few loads.  The following rows from a file failed to load due to a data conversion error on the 'CreationTime' column:

    11/9/2019 21:00
    11/9/2019 14:30
    11/9/2019 14:15
    

    The error received is as follows:

    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CreationTime).
    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (CreationTime).
    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (CreationTime).

    The other columns in the files are all VARCHAR.

    The 'CreationTime' column on the table was set to 'datetime', but after Googling fixes I changed it to 'smalldatetime' to no avail.

    Any ideas on why the formatting in the .csv file is now failing?

    Thanks,

    Frank



    Tuesday, November 19, 2019 1:32 PM

Answers

  • You will be much happier if you always bulk insert into a "Stage" table with all varchars, and then use insert/update/merge commands to move that data from "Stage" to target tables.

    The bulk insert has limited functionality for data conversion and error messages.  You will spend too much time pulling your hair out trying to get this to work.

    • Marked as answer by anelliaf Tuesday, November 19, 2019 6:11 PM
    Tuesday, November 19, 2019 1:39 PM
    Moderator

All replies

  • You will be much happier if you always bulk insert into a "Stage" table with all varchars, and then use insert/update/merge commands to move that data from "Stage" to target tables.

    The bulk insert has limited functionality for data conversion and error messages.  You will spend too much time pulling your hair out trying to get this to work.

    • Marked as answer by anelliaf Tuesday, November 19, 2019 6:11 PM
    Tuesday, November 19, 2019 1:39 PM
    Moderator
  • Check your csv file to see whether there is any change. Do you include header row for the file which errors out?
    Tuesday, November 19, 2019 2:55 PM
    Moderator
  • That makes sense!  I'll setup a staging table.

    Thanks!

    Tuesday, November 19, 2019 6:11 PM