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 Procedure can help in ASP.NET
CREATE PROCEDURE ImportFile
@File VARCHAR(100)
AS
EXECUTE (‘BULK INSERT TableName FROM ”’ + @File + ”’
WITH
(
FIELDTERMINATOR = ”;”
, ROWTERMINATOR = ”\n”
, CODEPAGE = ”RAW”
)’ )
/*
EXEC dbo.ImportFile ‘c:\csv\text.csv’
*/
C# Code
SqlConnection cnn = new SqlConnection(
string.Format(@”Data Source=.\SQLEXPRESS;Initial Catalog=DB_Name;Persist Security Info=True;User ID=;Password=” ));
SqlCommand cmd = new SqlCommand(“ImportFile”, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@File”, SqlDbType.VarChar).Value = txtFile.text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
Hi,
Most of the time, I search this site for answers of my SQL questions. And here I have one more….
I am using SQL Server 2005 Enterprise Edition on Windows Server 2003.
I wanted to load Double quote enclosed, comma delimited text file into SQL Server 2005. There was no header record in the file.
I used “SQL Server Import Export Wizard” to perform this task. I chose Data Source = Flat File Source, selected .csv file using Browse button, specified Text qualifier as double quote (“). There are total 17 fields in the upload file and under Advanced section they were named from “Column 0” to “Column 16”. The default length of each column was 50. I specified length of the “Column 5” as 200 because I know the length data in that column was more than 50. I have checked rest of the columns and made sure that length 50 was enough for other fields.
I clicked on Finish to start uploading and I got following error:
————————————-
Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column “Column 4” (26) to column “Column 4” (136). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “Column 4″ (136)” failed because error code 0xC020907F occurred, and the error row disposition on “output column “Column 4″ (136)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 1” (112) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0” has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – Giftlink_2008_csv” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “SourceThread0” has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
————————————-
I have checked “Column 4” length and it was 50 which was much enough for data in that column.
Is anyone has any idea on this?
Any help would be appreciated.
Thank you in advance.
Please help me
Iam using sql server 2000,7.0 ..I want to do a similar operation..
Hi there
I’m facing an issue in Bulk insertion. Whenever i query the command –
BULK INSERT vijay FROM ‘c:\vijay.csv’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’);
sql throwing an error “ORA-00900: invalid SQL statement”.
Could anyone help me out in this issue?
Thanks in advance.
Note that the informations or Queries given in this site are for MS SQL Server and not for ORACLE
i am facing an issue that is i have text file like below format
AAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBB CCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDD EEEEEEEEEEE
AAA BBB CCC DDD EEE FFF
121 john Albert 1-1-08 ok No
121 john Albert 1-1-08 ok No
121 john Albert 1-1-08 ok No
in this format AAA,BBB,CCC…FFF this are heads of column Now
i want to insert this text data of each head in Sqlserver database table in separate column mens AAA head data sould be save in AAA column in SqlServer Databse
hi..my text file is quite complicated, the field terminator only can use by ‘length’ to seperate…please refer the text file i copy paste as example
VENDORID INVNO INVDATE PONO PODATE
S0016 TB080767924/07/08BD-01/07/08
the fieldterminator surely cannot use by space bar…can i use the length to seperate the field? if yes…please guide how to…
thanks and regards
Pinal, nice and simple code, but I cannot solve the problem
——————————————————————————
Meldung 4860, Ebene 16, Status 1, Zeile 2
Massenladen ist nicht möglich. Die Datei “c:\csvtest.txt” ist nicht vorhanden.
(0 Zeile(n) betroffen)
FILE DOES NOT EXIST
——————————————————————————
There are many users with the same issue, can you advice what I need to do
many thanks
Copy the File to Server’s directory and try it will work
Hi,
How to import from CSV to new table? I tried with OPENROWSET . but it shows error if there are any special characters in the file name(say for example ‘-‘).
When you use OPENROWSET you should specify files names within double qutes
I was getting the same problem as other here with the error reading
The file “c:\Test.txt” does not exist.
Looks like the the SQL commands will only work when the file itself is on the actual SQL server box. I was getting this error using SQL Admin on another box and as soon as I moved the file to the server itself, I could run the command from any machine but would still reference the file on the actual SQL box. Enjoy!
protected void Button1_Click(object sender, EventArgs e)
{
// import data from txtfile to sql server
SqlConnection con = new SqlConnection(“Data Source=DEVELOPMENT\\SQLEXPRESS;Initial Catalog=my data;Integrated Security=True”);
string filepath = “C:\\Documents and Settings\\Developer\\Desktop\\txtFilesFolder\\data1.txt”;
StreamReader sr = new StreamReader(filepath);
string line = sr.ReadLine();
string[] value = line.Split(‘,’);
DataTable dt = new DataTable();
DataRow row;
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(‘,’);
if (value.Length == dt.Columns.Count)
{
row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
con.Open();
cmd = new SqlCommand(“insert into dbo.text_data1 (ID,Name,Gender,Age ) values (” + row.ItemArray[0] + “,'” + row.ItemArray[1] + “‘,'” + row.ItemArray[2] + “‘,” + row.ItemArray[3] + “)”, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
I’m trying to do an import from a text file using the BULK INSERT. The text file is separated by fixed width (no delimiters).
Can anybody give me an example query for this?
Hi Pinal,
How to create a CSV or Text file from a Stored Procedure?
Thanks
Sultan
I found how to do this by inserting all the data into one column and then using the substring function to split the columns. It worked…
hi pinal
i have got the same problem as charles. my text file has no spcific delimiter. the columns can be only separated in terms of length.how can such text filebe loaded?
thanks
I’m wanting to upload a csv file into phpmyadmin, but you last me after you said, “February 6, 2008”!
Can you give me any help on a 5th grade level?
Ron
Firstly, thanks for this article, it is great to see the whole code posted rather than individual bits all around.
Please could you assist me with this scenario:
I have 6 columns created in the CSVTemp table, the last two columns could have null values so I have specified this using LastName VARCHAR(40)NULL
When I execute the query, the next record is placed in the first of the NULL columns instead of the next line e.g.:
File contains information: 1,2,3,4,5
20,30,40,50,60,70
CSVTemp table Col1 Col2 Col3 Col4 Col5 Col6
1 2 3 4 5 20
I am using your example the ROWTERMINATOR = ‘\n’ but yet sql still reflects this record as above. How to I inform SQL to insert that 20 into the column 1, rather than continuing?
Okay here is my dilemma…
I have multiple csv with data in each file. They are connected via foreign keys (with each table having its on primary key). I want to load these tables into an Oracle database using SQL. If i know the order in which the tables should be loaded how do i go about loading the tables into the Oracle db.
E.g.
// CSV 1
Fields = ID_NUMBER | Name | Date
Data = 12345 (PK)| Hafiz | 12-12-2008 |
// CSV2
Field = ID_PRODUCT_ NUMBER | ID_NUMBER | Colour | Type
Data = 54321 (PK) | 12345 (FK) | Black| Car |
The Oracle DB has the tables setup with the same field name. I want to load CSV 1 first into Oracle Table 1 and then CSV 2 into Oracle Table 2 (must be in this order or will run into referential integrity issues.
The next step is to make this applicable to csv files with 100 lines of data each (probably using some sort of iterative process). Can you help??????????
IF OBJECT_ID(‘tempdb..#temp1’)IS NOT NULL
BEGIN
DROP TABLE #temp1
END
CREATE TABLE #temp1
(
Output VARCHAR(MAX)
)
SELECT * FROM #temp1
INSERT INTO #temp1(Output)
EXEC master..xp_cmdShell ‘type C:\1.csv’
DECLARE @NoOfColumns INT,
@SQL NVARCHAR(MAX),
@IntCount INT
SELECT
TOP 1
@NoOfColumns =
LEN(OUTPUT)-LEN(REPLACE(OUTPUT,’,’,”))+1,
@IntCount = 1
FROM
#temp1
IF OBJECT_ID(‘temp’)IS NOT NULL BEGIN DROP TABLE temp END ;
SELECT
@SQL =
N’ create table temp ( ‘
WHILE(@IntCount <= @NoOFColumns)
BEGIN
SELECT @SQL =
@SQL +
N' F'+CAST(@IntCount AS VARCHAR(5)) + N' VARCHAR(500) ,'
SELECT @IntCount = @IntCount + 1
END
SELECT
@SQL = LEFT(@SQL,LEN(@SQL)-1)+N' ) '
EXECUTE (@SQL)
BULK INSERT temp FROM 'c:\1.csv' WITH ( FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
SELECT * FROM temp
@MD
The data you imported is stored and sorted based on the collation setting you choose when sql server was installed or when the database was created.
Read more about collation settings, then you will be able to troubleshoot the problem.
@MB
The data you imported is stored and sorted based on the collation setting you choose when sql server was installed or when the database was created.
Read more about collation settings, then you will be able to troubleshoot the problem.
Hi,
This article was very helpful to me. However I do have one question, how would you modify this script if you have a text file that doesn’t have set number of columns. E.g.:
row1: col1,col2,col3
row2: col1,col2
row3: col1,col2,col3,col4
row4: col1,col2,col,3,col4,col5
I tried to load this file using the above script, i was able to load the data but the data couldn’t be loaded correctly.
Help please.
Thanks
Hi ,
those who are facing problem that the text file does not exists
copy ur file to server where ur sql server exists
nice article
Thank you, Harminder! This fixed my issue immediately!!
Very good article!!