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
Hi All,
Appreciate help on below
I want to do conditioal sum in an SQL table as follows
Column1 column2 column3 column 4 column5
a b zzz jjj 4
b a yyy rrr 7
a a fff hhh 3
a b ccc kkk 2
b a kkk ttt 7
b a ggg lll 4
a b yyy kkk 3
what i want to do is,
For values of column1 = ‘a’ then add(sum) column 5 by grouping by column3. for values of column2 = ‘b’ then add (sum) co lumn5 by grouping by column4.
please help!
Krish
Replace ` with ‘ (single quotes)
When I run this particular stored proc all of my data comes out in the table with double quotes around it. How would I get rid of these double quotes?
Thanks
Simple and Suberb information.
Thanx.
Hi
Can you tell me if it is possible to execute tis code with sqlcmd in VB.NET.
I am using express editions of VS 2008 and SQL server 2005
I get the error: Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.txt” does not exist.
OR
Cannot bulk load. The file “c:\csvtest.csv” does not exist.
Make sure the file exists in SERVER’s directory
Thanks, it was a very simple solution to meet my requirement.
I’m using the bulk insert statement
BULK INSERT Received
FROM ‘\\Admfs1\users\CPurnell\Responses\C0009348.iff’
WITH
(
FIELDTERMINATOR =’|’,
ROWTERMINATOR =’ {CR}{LF}\n’
)
This works great for one file. But what I really need to do is bulk insert all .iff files from the Responses folder.
Any suggestions?
You may need to run the BULK INSERT for all the files
How many such files do you have in the folder?
Hi
I tried to insert the data in the table using INSERT Bulk Query.
I used the Query like this:
BULK INSERT insertdatdata FROM ‘D:\mani_new\standard.Dat’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘\n’
)
GO
The file is there in correct path.
But i got the following error.
Cannot bulk load because the file “D:\mani_new\standard.Dat” could not be opened. Operating system error code 3(The system cannot find the path specified.).
please give me some solution.
Its urgent.
Thanks,
Sathya.
Double check that the filepath is on the same server as your SQL Server instance. That’s the most likely issue. You might need to check folder permissions too.
I got the error:
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “D:test.txt” does not exist.
Plz give the solution.
Thanks
Sathya.
How do we create a table in SQL server through VB.NET code by importing the schema from a TEXT file?
USE OPENROWSET
Refer this
g8
its a true solution
hi,
sathya
i think u r using sql server client verson. on ur pc
so u have to put the text file on ur data base server’s
“D:\mani_new\standard.Dat” paths.
just do it …………
hi pinale,
While working on it i m getting the error file doesnt exist can u suggest me any thing to be done
Thanks
Hi,
Thank you for sharing your code, it really works well ;-)
I tried to open a text file in web but it did not work. Do you have any idea to work it around?
BULK
INSERT bod.temp
FROM ‘http://www.test.com/test.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO
Thanks!
No. It wont work
You need to have data in the server’s directory to use that
hi,
can u tell how to load the particular field from dastination file into database…
i am using simple notepad files which is contain 46 columns…
so i want to read the input file and insert only three columns (column 1, column 15,column 46) into the tables…
is there any commands existing for doing this type of file handling…
i m using sqlserver 7
plz help me…
I have to import a csv file into an existing table that has different column names from the originating one. How do I match differently named fields ?
Thank you
Different column names doesn’t matter as long as number of columns and datatypes are equivalnet
Hi Madhivanan,
Could you please mail me the query how to extract column headers using BCP command and also column names changes every time .
Thanks in advance :
waiting for your reply (as soon as possible)
hi, i want to upload a cvs file frm asp.dot page and the file should automatically extract into sql database table. the table is created, plz help out…
I just wanted to say that this solution is so quick and easy. I found a ton of other way too complicated examples. This just cuts right to the quick and gets the job done.
Thanks for this excellent code snippet!
Mike
Hi Pinal,
I am attempting to use the bcp utility (via command prompt) in order to create a comma-separated text file named Inside Sales Coordinator. Here is the new table created in the Northwind database:
CREATE TABLE [dbo].[NewEmployees](
[EmployeeID] [int] NOT NULL CONSTRAINT [PK_NewEmployees] PRIMARY KEY,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Notes] [ntext] NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) NULL
) The new comma-separated text file should contains the following columns from the NewEmployees table: EmployeeID, LastName, FirstName, HireDate (date part only; no time), and Extension. Only those employees with a Title of “Inside Sales Coordinator” should be returned.
Here is what I came up with so far:
bcp “select EmployeeID, LastName, FirstName, HireDate, Extension from Northwind.dbo.NewEmployees” out C:\InsideSalesCoordinators.csv –c –t , –T -S SDGLTQATEAM\SQLExpress can you give me a little insight on how to populate the .csv file. Thanks