T SQL Bulk Insert skipping first row with or without header

Viewed 12857

I've used BULK INSERT before, but I've just noticed it's having problems skipping the first row. I'd rather not have it skip anything, so here's the code I've been using.

IF OBJECT_ID('tempdb.dbo.#tempTable', 'U') IS NOT NULL
DROP TABLE #tempTable;

CREATE TABLE #tempTable
(
StartDate datetime,
EndDate datetime,
TransactionItemsMigrated bigint,
TransactionSizeBytes bigint,
CurrentItemsFailed bigint
)

BULK INSERT #tempTable
FROM 'C:\csv\xxxxx.csv' --change to CSV file location
WITH 
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
)

INSERT INTO Transactions 
(
    ProjectID, 
    StartTime, 
    EndTime, 
    TransactionItemsMigrated, 
    TransactionSizeBytes,
    TransactionTimestamp,
    CurrentItemsFailed
)
SELECT 
    4, --change to projectID
    StartDate,
    EndDate,
    TransactionItemsMigrated,
    TransactionSizeBytes,
    GETDATE(), --sets TransactionTimestamp to current datetime
    CurrentItemsFailed 
FROM #tempTable

The insert works fine, but it skips the first row of data after the header. If I take the headers out and use FIRSTROW=1, FIRSTROW=0, or even comment FIRSTROW out entirely, it still skips the first row. I've looked at the carriage return \n or \r\n, but all the other rows work fine. I suspect this is the root of the problem, but I'm not sure how to get that first row pulled in. The source data is a straight csv file with no formatting applied. Thoughts?

1 Answers
Related