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
good day sir,
tried the above script and it worked..thanks..
i have one issue though..is there any way i can verify if the source file is in the correct format or if the file is really a csv file before running the script..
thank you sir..
i would like to ask how can i insert the csv file created in linux..?
this had some issue in row terminator…
tnx in advance…
What is the row terminator? Specify it when you use bulk insert statement
Hi
Bulk insert by itself seems to work fine, but combined with IDENTITY option set, its not working for me as expected…
I have a CSV file with a few columns, lets keep the example simple:
Row 1: x,a
Row2: y,b
Row3 z,c
I use the following to create a table:
CREATE TABLE testTable
(testTableID int PRIMARY KEY IDENTITY(1,1),
frameNo VARCHAR(40),
relTime VARCHAR(40),)
I then try and populate the table with the
BULK
INSERT testTable
FROM ‘C:datatshark_csvtest.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘n’
)
It doesn’t work. I want it to generate the first column with unique numbers per row with increment 1. The first column (x for the first row) should be placed in the second column of the table (as it should do because of the IDENTITY feature… instead it fails. If however I put a comma in front of every row so the row one for example looks like ,x,a then it works.
Any ideas how I can fix this. I don’t want to first have to manipulate the CSV file…lets face it – CSV files don’t start with commas….they only use them to separate columns…
Any ideas?
You need to specify to the columns. Make sure to read this which exactly does what you need
Many thanks Dave !
hi sir.., hope you can help me on this…
if found out that the address is a unicode “unicode string”
it has an error executing the below code how to declare data types unicode specially “Address VARCHAR(MAX)”
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
Address VARCHAR(MAX),
BirthDate SMALLDATETIME)
GO
BULK
INSERT CSVTest
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO
thank you and more power…
How can I import a .csv file from a local PC where run vb.Net aplication to a remote SQL Server where I need to up the .csv data on a SQL Server Table.
Use BULK INSERT with unc path
hi i’m getting the error message “The Bulk Insert construct or statement is not supported.” I’m using SQL Server 2005.
Can you post the exact code you used?
How to export #table data to comma seperated csv?
Please help me,
Thanks
Use Import/export wizard and choose destination as flat file
Buenas tardes tengo casi el mismo problema que todos comence una plicacion en visual net 2008 en donde pretendo importar archivos txt a una base de datos en sql server. son 90 archvios de 45 tiendas es decir tienda1entrada
tienda1salida
tienda2entradas
tienda2salidas
y asi hasta llegar a la
tienda45 con sus entradas y salidas.
las 45 tiendas mandan 2 archvios de txt cada una
haciendo un total de 90 txt que se guardan en c:\Impotacion
de alli tengo que tomar los 90 archvios y por medio de mi aplicacion cargarlos en un base de datos de sql que tiene las tablas de entradas y salidas de cada una de las tiendas es decir serian 90 tablas ya echas y con una estructura que debo respetar el ejemplo de los txt es
08;1743;27;03;9311;00005;13012011;0;70;10 tengo que ver la manera de mandar esos 90 txt a cada una de sustablas en la base de datos algun codigo para mandar todo de la carpeta a cada uno de sus tablas.
Thanks!
Is there a way to set it to do this automatically 2-3 times a day? I’m trying to to figure out how to import a set of data into my database 2-3 times a day.
Nice article.
Worked perfect, thanks. How about if you need to import multiple files with different names. Can you use some kind of wildcard? For example if I have textfile.01, textfile.02..etc, is there a way to import all files in the folder who’s name starts with textfile?
Thanks!
Hi i want to know that how can i assign a value dynamically to the bulk copy method, following is the code
using (CsvDataReader csvData = new CsvDataReader(FileUpload_participant.PostedFile.InputStream, Encoding.Default))
{
value = Convert.ToInt32(ddl_select_Participant.SelectedValue);
csvData.Settings.HasHeaders = true;
csvData.Columns.Add(“nvarchar”); // Rater Name
csvData.Columns.Add(“nvarchar”); // Rater EmailID
csvData.Columns.Add(“varchar”); //Participant ID
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(“Data Source=development;Initial Catalog= Program;User ID=sa;Password=test456”))
{
bulkCopy.DestinationTableName = “CEE_form_Table_Rater”;
bulkCopy.ColumnMappings.Add(“Rater Name”, “Rater_Name”); // map Rater Name to Rater_Name
bulkCopy.ColumnMappings.Add(“Rater EmailID”, “Rater_Email”); // map Rater EmailID to Rater_Email
bulkCopy.ColumnMappings.GetType(“value”);//, “Participant_id”);
bulkCopy.WriteToServer(csvData);
}
}
bulkCopy.ColumnMappings.GetType(“value”);//, — for this thing i want to pass a paramater…. to obtain the participant ID.
Please assist me am trying to import data from an excell file with the name template.csv to a sql server 2008 database but am having problems that is “There was an error parsing the query. [ Token line number = 1,Token line offset = 10,Token in error = INTO ]” i have also tried using BULK but still the same error that is “There was an error parsing the query. [ Token line number = 1,Token line offset = 10,Token in error = BULK ]” My code is as follows
SELECT * INTO template
OPENROWSET
(
MSDASQL,DRIVER={Microsoft Excel Driver(AccountType.csv)},
DBQ=D:\CSV\AccountType.csv,
SELECT * FROM AccountType
)
Also the second code i have used is…
BULK INSERT
INTO AccountType
FROM D:\CSV\AccountType.csv
WITH
(
BATCHSIZE=7
ROWS_PER_BATCH=7
CHECK_CONSTRAINTS
CODEPAGE=RAW
FIELDTERMINATOR=’,’,
ROWTERMINATOR=’\n’
)
Please Assist
Are you using Mysql or SQL Server?
what if my db table has an identity column (not present in the .csv file) ?
thanks
You need to specify the columns and omit the identity column.
Refer this to know how to do it
Hi… Nice solution Pinal.
Thanks a lot…
it really slove my probelm.
Hi.
When i am using given bulk import command in SQL 2005 srever. I got the error msg.
BULK INSERT test FROM ‘d:\db\AreaCode.txt’
WITH
(
ROWTERMINATOR = ‘\n’
)
GO
Error Shows:
Msg 4861, Level 16, State 1, Line 5
Cannot bulk load because the file “d:\db\AreaCode.txt” could not be opened. Operating system error code 21(The device is not ready.).
Kindly provide the solution what’s the issue and how to fix this.
sure the file is in server’s directory and not in your local system
Hi, do you mind to help me on this?
I was wondering how am I going to get some values from a text file. Here is an example from a TEXT file.
666,666,666.00 555,555,555.00 444,444,444.00
999,999,999.00 888,888,888.00 777,777.00
I am using “BULK INSERT TBTest2 FROM ‘c:\testfile.txt’ WITH (FIELDTERMINATOR = ‘ ‘, ROWTERMINATOR = ‘\n’)” to get value from this text file.
It is successful when it read and insert into database for first row because every value has a gap with a “space”.
But when it read to second row, it wont work for value 777,777.00 because it detected more than one “space” between 888,888,888.00 and 777,777.00.
I have a requirement to export from SQL Server Table to .CSV file can any body help me in this regard.
Refer thi post