This is a very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create TestTable
USE TestData
GO
CREATEÂ TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLEÂ CSVTest
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
839 Comments. Leave new
@Satrebla
If you have( ” ) character at fixed length for every value that goes into that column, then you can use substring function and select only those characters that you want ignoring rest of them and concatinate with other values (if needed)
Regards,
IM.
@Imran Mohammed
Thanks for reply, but lenth is variable. For example:
“abc,qwe,zxc”,123,txt
“qwe,asd,fgh,jkl”,456,qqq
“12233,456789”,rty,159
..?
replace commas that aren’t embedded between quotes with a character that won’t be used. change fieldterminator to be that new character.
can probably also use fmt file, but i’ve never used one so not positive
Hi,
I would like to know how do we import a remote text to some other server in sql server 2005 ?
how or where do you write the script that you are publishing. I am new to the import but not asp.net. How do you import into a table that you have already constructed with the gui in asp.net.
Thanks a lot..
It is working fine
How We can Insert The Data in sq l server through Excel Sheet. Here i don’t have ‘,’ field or Line termination.
select *
from
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=c:.xls’, [$])
) AS x
Hi Praveen,
select *
from
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=c:ExcelFileName.xls’, [YourSheetName$])
) AS x
Thanks,
Thanks Tejas
Hi All,
how to join two different tables on different server
Hi Praveen,
Is SQL PORT open for any server?
If yes, then you can use:
SELECT *
FROM table A
INNER JOIN
(
OPENROWSET(‘SQLOLEDB’,’ServerAddress’;’User’;’Password’,
‘select * from
table
‘)
) B
ON A.id = b.Id
Thanks,
Tejas
Hi,
I had implemented this “Bulk Insert” in Sql 2005, i am getting
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘‘’.
my Statement ..
BULK
INSERT ccprocessorstandardpayee
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
please help me..
thnk u..
Just a note that somehow – doesn’t matter to me – I was stymied at first by “fancy” quotes or apostrophes. That is, I was inclined to copy/paste your stuff, and SQL returned syntax errors having to do with the use of NON- straight (up & down) single quotes. I happened to figure out the problem but someone else might get frustrated prematurely. Since there’s some problem having to do with Full Text searching that prevented me from importing those big (and no doubt beautiful) sample DB’s that MS makes available, this post (YOURS) is/was EXTREMELY HELPFUL. Thanks!!
Thank you very much! This was very helpful.
Hi,
I’m trying to upload a file to my database and it wont work at all, I get this message:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘BULK INSERT tblPostcodes FROM ‘c:postcodes.txt’ WITH ( FIELDTERMINATOR’ at line 1
when I run:
BULK
INSERT tblPostcodes
FROM ‘c:postcodes.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘n’
)
GO
Any ideas?
well i try to upload the csv file throgh the below given query
BULK
INSERT CSVTest
FROM ‘c:\CSVTEST.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO
but it is showing error message when i ran this above query
the error message is given below:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
well i try to upload the csv file throgh the below given query
BULK
INSERT CSVTest
FROM ‘c:\CSVTEST.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO
but it is showing error message when i ran this above query
the error message is given below:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
please tell me why this is happening and pls give me the solution for this
ur Bulk insert works as following way: in LAN only
Bulk Insert cv_Test from ‘\\Rahool\SharedDocs\Book2.txt’ with
(
FIELDTERMINATOR= ‘,’,
ROWTERMINATOR= ‘\n’
)
Go
Thanks this is great.
You are a true guru at sql. This has solved a massive problem.
I was trying to insert a csv into sql express 2005.
Which is witout the import functions. this code solved that problem.
Thanks a lots!!!