T SQL Bulk Insert skipping first row with or without header
<p>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.</p>

<pre><code>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
</code></pre>

<p>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?</p>