Importing csv file to SQL Server Management Studio - No tables available

Question

I am trying to import a csv file to insert data into an existing table on my database. I go through the wizard and when it comes to select source tables and views for the destination, there are none to choose from. It just thinks I am trying to create a new table.

Any suggestions? Thanks!

Answer 1

Skip the wizard and use just BULK INSERT, here is an example:

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Full example : SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

Answer 2

Cyril's answer is along the right track. However, a true CSV-compliant solution now exists since SQL Server 2017 (14.x) CTP 1.1.

Use this

BULK INSERT destinationtable
FROM 'filepath'
WITH
(
FORMAT = 'CSV'
)
GO

There is an issue though. If your data uses NULL to indicate nulls, then you'll need to remove them as MSSQLSMS will not accept NULL as a valid NULL value. Do a search/replace for ,NULL to ,.

For example (4 columns):

1,NULL,test,"Escaped text with comma, this works"

must be formatted like this:

1,,test,"Escaped text with comma, this works"

See SQL won't insert null values with BULK INSERT for information on NULL insertion problems.

You can go to https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017 for more information.