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
This whole concept goes to crud if you have identity fields. About as useful as a yacht in the Sahara Desert…
You can use this approach in such case
Thanx.
Hi
Can anybody help me how to import data(email addresses) from sql server2000 in to Microsoft outlook express 6.
My main aim is to send company newsletter to all account holder.Most of them have email address.
Do i need to run a query in server to find out all email address and then export these to a csv file.Or you have a better solution for that.
Can I send it from Mozilla thunder bird.
Please please please give the answer ASAP.
Thanks
Dipu
Hi All,
i am new to DB.I have a question , i want to compare SQL server table values to CSV values. how can i do that?
Thanks,
SN42647
You need to first import csv data to a staging table and then compare. Refer this to know how to import csv to a table
I have an Excel spreadsheet. How can I, using openrowset, or using bulk insert (for CSV), insert only specific columns from that spreadsheet into a table? Like, if I have 10 columns in the XLS/CSV, how can I import only the 5th, 6th and 7th columns? Additionally, how do I import all rows from the 5th one to the last but 4th or 5th one? Please post a reply soon. Thanks in advance!
You need to specify those columns in the SELECT Statement
Hi,
Im trying to execute this sql statement and it gives me an error:
BULK
insert table_name
FROM ‘C:\Documents and Settings\shibammk\My Documents\BrandRefresh\filename.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
All I want to do is load a csv file to update my details using sql.
What is the error you are getting?
hi Dave,
today i m stuck with a problem,
i have 1crore data in csv format,
when i use bulk insert statment it gives following error:
Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 5. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
i checked all fieldelminator, roweliminator,column name every thing is fine.
thanks in advance,
format example of csv:
“123”,”amit”,”mumbai”
bulk insert table1
from ‘d:\test.csv’
with(fieldeliminator=’,’,
roweliminator=’\n’)
sory,
i have spled wrong
revised:
format example of csv:
“123″,”amit”,”mumbai”
bulk insert table1
from ‘d:\test.csv’
with(fieldterminator=’,’,
rowterminator=’\n’)
Can you post the structure of the table table1?
Sorry sir but this code doesnot work it give the error like
Cannot bulk load. The file “C:\csvtest.csv” does not exist.
The file should be in server’s directory
Hi I am a bit rusty with SQL, please can you help with Bulk insert on SQL 2005, getting error .
Thank you very much
Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘Data5’ in the database.
Msg 4861, Level 16, State 1, Line 5
Cannot bulk load because the file “c:\Agent\Agent.txt” could not be opened. Operating system error code 3(The system cannot find the path specified.).
Coding is:
USE [Agentdata]
GO
/****** Object: Table [dbo].[Data] Script Date: 04/06/2011 15:08:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Data5](
[Machine] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTime] [datetime] NOT NULL,
[I/i] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[number] [nchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[process] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[action] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Agentdata]
GO
ALTER TABLE [dbo].[Data] WITH CHECK ADD CONSTRAINT [FK_Data_Machine] FOREIGN KEY([Machine])
REFERENCES [dbo].[Machine] ([Machine name])
BULK INSERT Data4
FROM ‘c:\Agent\Agent.log’
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
my file is 20 MB
contain different records in rows
01^035^0801^11^02^02^C^00001^2010^0021^300^^AAZPY7835N^ VENUGOPALKRISHNA YIDAGUR VENKATARAMAIAH^NO.63/3,BEHIND UNANI^MEDICAL INSTITUTE,^SUNKADAKATTE,^BANGALORE^^15^560091^^000000009664^000000000000^000000000290^INTE^000000000396^^000000000000^^000000000000^000000000000^000000000000^0000000010350^I0246^T^020211^020211^722005^I0309^040211
08^035^0205^10^03^15^R^00008^^0021^^MD^^000000000000^000000000000^000000000000^^000000000000^^000000000000^^000000000000^000000000000^000000000000^0000000000000^0000000010000^-000000010000^I0244^020211^722005^I0309^040211
02^035^0181^035^0061^0020^00002^00000^0000000003105^^C0240^020211^722005^C0309^040211
how i can do this
Hii,
I am importing data from excel file in Sql server 2005
and I tried following query but It returns an error messege
insert INTO aba
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=d:\book1.xls’,
‘SELECT * FROM [Sheet1$]’)
error message is:
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
How to fix it
Make sure the server has EXCEL installed with it
Hey there,
this is a very helpful article
however i wouldlike to ask what is the datatype for ticks?
Hi,
I have 21 million rows and 7 columns of data. Total size of CSV file is 2.3 gigs. Can I use export import wizard for nulk insert?
Nanda Kishore
Use bulk insert or bcp
Hi pinal,
I am getting error while trying to import from excel file to sql server using network path.
Here is my code
insert INTO Temp_TS_Contract_Document_Staging
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=\\computername\PDF Attachment\format_images1.xls’,’SELECT * FROM [Sheet1$]’)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
while it works fine in local
insert INTO Temp_TS_Contract_Document_Staging
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\PDF Attachment\format_images1.xls’,’SELECT * FROM [Sheet1$]’)
Please help me , shold i need to set any permission in the folder as well as in SQL server.
Thanks in advance.
Make sure that the file is closed when running the query and jet provider is installed at the server
Sno Sname
1,2 Sreenivas
3,4 Reddy
5,6 Raja
7,8 Reddy
I want like the following table
Sno Sname
1 Sreenivas
2 Sreenivas
3 Reddy
4 Reddy
5 Raja
6 Raja
7 Reddy
8 reddy
in ssis
Hi Pinal,
I am using BULK INSERT against a SQL Server 2005 Express database on Windows Vista. The authentication is SQL Server using the built in sa account.
When I attempt to run a bulk insert using a file placed in the Windows User’s Temp Folder (C:UsersMichael McDonaldAppDataLocalTemp) I get the following error:
SQLException: Cannot bulk load because the file … could not be opened. Operating system error code 5(Access is denied.)
I can delete the file manually, and my c# code creates the file with no worries. I run the exact same code against a SQL Server 2008 DB using Windows Authentication, and it works just fine?
Is there something I need to do to “map” the sa user in the SQL Server 2005 Express to use the current Window’s user’s temporary folder?
Any insight you could provide would be much appreciated. I search the net high and low…with no success.
@Michael
I guess you have to give permissions to (domain or Local) Account on which SQL Server Service is running to the file located in Temp folder.
You are connecting to SQL Server under sa account, this has nothing to do with file permission on OS to SQL Server. SQL Server Service Account should have access to file in temp folder (According to your example).
Another scenario I could think of is, If you are running this script as a schedule job, then SQL Server Agent Service Account should have access to this file in temp folder.
Another scenario could be, the file you are trying to access is on your local computer and not on the server where SQL Server is installed. SQL Server always look for file on its OS and not on Client OS. In this case you will have to move file to a folder on Server and refer to that path in your sql script.
I would start my debugging by giving public access to the file in temp folder to public and see if it works, and go from there…
~ IM.
I am having values in TABLE X,Y.a is common for 2 tables …how can i update Y table values in X tables
Ex:
Table X :column A B C
1 0 0
Table Y:Column A D E
1 12 12
i need UPDATE table X like this : A B C
1 12 12
table Y column D value should transfer to X column B value
table Y column E value should transfer to X column C value
hi … i used this method mail by you for bulk insertion via ssis
SQL SERVER – Import CSV File into Database Table Using SSIS
but i am unable to understand,how it insert a text file data in sql data table.kindly inform it
thanks
Thanks for posting this! It’s good to know that MS SQL has such a feature.
Another note to learners: notice that the strings in the CSV are _not_ quoted. If this SQL script is used with quoted strings, the quotation marks are interpreted as part of the string itself.