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!

Solution 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

Solution 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://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017 for more information.

Comments

[Steven]

This is NOT a CSV compliant solution!

[KM.]

@Steven, how so? the title of the linked page is: SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

[Steven]

Does not support line breaks in cells?

[Rohit Vyas]

I am getting You do not have permission to use the bulk load statement. error

[sylverfyre]

It doesn't consider text qualifiers, such as quotation marks around text fields, which are important to sanitize data that may contain commas in it.

[user1477388]

You may not have sufficient permissions to run this command - in which case - it is easy to just copy and paste the rows into SSMS by clicking "edit top 200 rows" on the table.