CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012bulkinsert
TheDoc
TheDoc

Reputation: 718

T SQL Bulk Insert skipping first row with or without header

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?

Upvotes: 3

Views: 15034

Answers (1)

Mads Nielsen
Mads Nielsen

Reputation: 644

I think you need to look at the formatting of your data again. Maybe some invisible white space snug in somewhere?

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 'D:\User\Documents\test.csv' --change to CSV file location
WITH 
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)

SELECT * FROM #tempTable

This code works with this sample csv file

StartDate,EndDate,TransactionItemsMigrated,TransactionSizeBytes,CurrentItemsFailed
2016-03-17,2016-03-17,0,1,2
2016-03-18,2016-03-17,1,1,2
2016-03-19,2016-03-17,2,1,2
2016-03-20,2016-03-17,3,1,2

With this output

StartDate   EndDate TransactionItemsMigrated    TransactionSizeBytes    CurrentItemsFailed
2016-03-17 00:00:00.000 2016-03-17 00:00:00.000 0   1   2
2016-03-18 00:00:00.000 2016-03-17 00:00:00.000 1   1   2
2016-03-19 00:00:00.000 2016-03-17 00:00:00.000 2   1   2
2016-03-20 00:00:00.000 2016-03-17 00:00:00.000 3   1   2

Upvotes: 4

Related Questions