开发者

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

I am trying to import a csv file t开发者_如何学运维o 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!


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


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.


We do have multiple options:

  1. using dts wizard
  2. by programming

to know dts path or code in c# go through it

http://sqlcopy.blogspot.in/2012/07/bulk-sql-to-sql-sql-to-csv-csv-to-sql.html (dead link)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜