CODEWITHSUNDEEP
sql-servercsvimport
Richard Gale
Richard Gale

Reputation: 1952

SQL Server Import from csv file

I'm trying to import data from a .csv file into a SQL Server table.

Using the code below, I can read from the file:

BULK INSERT #TempTable 
FROM '\\Data\TestData\ImportList.csv' 
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR ='\n', FIRSTROW = 2, Lastrow = 3)
GO

(I added LastRow = 3 so I was just getting a subset of the data rather than dealing with all 2000 rows)

But I am getting multiple columns into a single column:

enter image description here

If I use the Import/Export wizard in SSMS, with the below settings, I see the expected results in the preview:

enter image description here

Can anyone give me some pointers as to how I need to update my query to perform correctly.

Here is a sample of what the CSV data looks like: enter image description here

TIA.

Upvotes: 2

Views: 4738

Answers (1)

Danny_ds
Danny_ds

Reputation: 11406

You probably need to specify " as Text qualifier.

Your fields seem to be quoted and most likely contain comma's, which are currrently splitting your fields.

Or, if it works fine using <none> as Text qualifier, try to use FIELDQUOTE = '' or FIELDQUOTE = '\b' in your query. FIELDQUOTE defaults to '"'.

It's hard to tell what's really wrong without looking at some raw csv data that includes those quotes (as seen in your first screenshot).

Upvotes: 1

Related Questions