Bulk Insert not working in SQL Server 2016

Question

Hi,

I am trying to insert the following

CREATE TABLE Palletinfo (
    id int IDENTITY(1,1) PRIMARY KEY,
    Parcel_Id varchar(20),
    Pallet_Id varchar(10),
    Date_Upload datetime,
);

ID,Parcel ID,Pallet_ID,Date_Upload
,459431127400,100009,09/02/2021
,459431127371,100009,09/02/2021
,459431127444,100009,09/02/2021
,459431127466,100009,09/02/2021
,459431127589,100009,09/02/2021

from a csv file into a table call dbo.PalletInfo

id int
Parcel_Id varchar(20)
Pallet_Id varchar(10)
Date_Upload datetime

using

   BULK INSERT [ZoomBI].[dbo].[Palletinfo]
    FROM 'C:\Test\DHL09022021.csv'
    WITH
    (
    KEEPIDENTITY,
    FIRSTROW = 2,
    FIELDTERMINATOR =',',
    ROWTERMINATOR ='\r'
    )

The errors I am receiving are

Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Which isnt very descriptive.. any help appreciated.

Answer 1

Well, did you verify the row terminator? A lone carriage return is not the common one. The default Windows is CRLF (\r\n) and Unix is LF (\n). CR (\r) was used by the old Macs.

Answer 2

Use an editor like VS Code or Notepad++ to display the EOL characters. You need the correct one.

Yup, your CSV does not contain a value for the ID column, thus it is read as NULL. Your destination table has defined this column as IDENTITY. The solution is simple. Use a staging table.

DROP TABLE IF EXISTS #StagingPalletInfo;

CREATE TABLE #StagingPalletInfo (
    ID INT NULL ,
    Parcel_ID NVARCHAR(255) NULL ,
    Pallet_ID NVARCHAR(255) NULL ,
    Date_Upload DATETIME NULL 
);

BULK INSERT #StagingPalletInfo
FROM 'C:\Test\DHL09022021.csv'
WITH ( KEEPIDENTITY ,
       FIRSTROW = 2 ,
       FIELDTERMINATOR = ',' ,
       ROWTERMINATOR = '\r' );

INSERT INTO ZoomBI.dbo.PalletInfo ( Parcel_ID ,
                                    Pallet_ID ,
                                    Date_Upload )
            SELECT SPI.Parcel_ID ,
                   SPI.Pallet_ID ,
                   SPI.Date_Upload
            FROM   #StagingPalletInfo SPI;