SQL Server 2012 Bulk Insert from CSV into temp table

Question

As the title says, I am attempting to insert a CSV into a temporary table. I am unfortunately encountering errors.

Here is the query:

USE DATABASE5000

CREATE TABLE #tempTable1
(
    ID INT,
    CD VARCHAR(50), 
    ESD DATETIME,
    EED DATETIME, 
    MiscDate DATETIME, 
    SQ SMALLINT
) 

BULK INSERT #tempTable1
FROM 'C:\Dir\Folder\BestFile.csv';

And here are the errors I get:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any ideas? Thanks yall.

Solution

You didn't specified any FIELDTERMINATOR. The default value is actually tab. Please refer to BULK INSERT documentation.

BULK INSERT #tempTable1
FROM 'C:\Dir\Folder\BestFile.csv'
WITH
(
    FIELDTERMINATOR = ','           -- add this
);

According to documentation, there is a FORMAT = CSV

WITH (FORMAT = 'CSV')

You may try that. I did a quick test, there are some limitations it seems like does not support string with double quote in it

Comments

[Squirrel]

You didn't specified any FIELDTERMINATOR. The default is tab. Please refer to docs.microsoft.com/en-us/sql/t-sql/statements/…

[Fishcakes]

Thanks again! Just for the sake of documentation, (FORMAT = 'CSV') throws an error for me in SQL Server. Omitting this part worked fine.

[ner0]

FIELDTERMINATOR does not work for me at all. If I replace all the commas with tabs beforehand then it works. I don't have a clue why this is the case. Also, I only have good results with ROWTERMINATOR in hexadecimal format.

[ner0]

Answering my comment/rant above, using DATAFILETYPE = 'char', as one of the arguments solves the issue.