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.

Answer 1

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