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:
- using dts wizard
- 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)
精彩评论