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.

"An unexpected end of file was encountered in the data file" - your file is "corrupt" - i.e. a comma in the wrong place, not enough fields, not consistent field delimiters. Something like that. — Nick.McDermaid
You didn't specified any FIELDTERMINATOR. The default is tab. Please refer to docs.microsoft.com/en-us/sql/t-sql/statements/… — Squirrel
@Squirrel is right. Also make sure the last line doesn’t have a terminator leaving you with an extra row which is blank — scsimon
@Squirrel Thank you! Setting fieldterminator to "," did the trick. You'd think they'd include that in the "BULK INSERT from CSV" documentation. If you make this an answer, I'll happily accept it. — Fishcakes

Squirrel Squirrel · Accepted Answer · 2018-10-11T01:22:45

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

SQL Server 2012 Bulk Insert from CSV into temp table

1 Answers