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
can any one help me from this……. Get the new values in with format utf-8 from the .flat-files
Nice one.I couldnt sleep well last night cos of this.Istumbled om this today and solved the prblm within 2min.Thanks
Hi i easy to understand in bulk insert thank u
hello pinal
can you please tell me how to create xml format files using bcp command
i have one csv file with 20 colimns and i have to dump the csv to one table with 22 columns so i have used one format file by creating manually.
i want to know how to create it by using some commands.
Please help me in this regard.
Pinal,
I’m needing to replace a very simple SSIS package and your BULK INSERT seems to fit the bill.
The SSIS package redirects error rows to a file at a few different steps.
What is the SQL for redirecting bad rows to a file?
Thanks,
Mike
hie to all..
I am doing a task which is similar to this.i want to import CSV files into Ms Sql database but here the problem is it shoul be in a generic way.i have to import anything which is of different format..but note that that structure will be in database..so any code or any information regarding this will be great help to me
THanks
Not sure you have faced this issue.. I have .csv file for bulk insert. I have created temp table for stoting the data from flat file. the columns and columns data has some extra spaces like [column1] whould be like [column1 ]. due to this my bulk insert is failing. I cant always format the .csv. the same issue with values.
This issue i came to know only when i tried sql import export wizard. find the fidderence below between two different file values.
sample1:
RGN SOIU TN DUE_DATE STATE CENTRAL_OFFICE ACTION
MODB 1066594212 7024517296 8/15/2012 NV LVE2NV N
sample2:
RGN SOIU TN DUE_DATE STATE CENTRAL_OFFICE ACTION
NEDB 1105440282 9195564725 8/31/2012 NC WKFSNC N
I am able to fix this by removing extra spaces in column names and also column values.
The error i am getting when i do with out correctign the file..
Bulk insert data conversion error (truncation) for row 2, column 8
(CENTRAL_OFFICE)
Let me know if something can be done for this.
Thanks in advance.
thanks for the useful info on this page. works for me
Does anyone know how to export selected rows of a table to a CSV format file using a query.
bulk insert with field terminator with ‘þ’ failing and the same working if i change the field terminator to some other special character like comma or semicolon.
i want to know is they anything need to be modify to work fieldterminator with ‘þ’
BULK INSERT dbo.LS_OI_CTD_INVOICE_NF
FROM ‘D:\fmt\BulkImportFiles\LS_OI_CTD_INVOICE_NF.dat’
WITH
(
FIELDTERMINATOR = ‘þ’,
ROWTERMINATOR = ‘\n’,
FORMATFILE = ‘D:\fmt\LS4.fmt’
)
go
10.0
42
1 SQLCHAR 0 10 ‘þ’ 1 ID “”
2 SQLCHAR 0 30 ‘þ’ 2 OIC_CONTRACT_NUMBER Latin1_General_CI_AI
3 SQLCHAR 0 30 ‘þ’ 3 OIC_INVOICE_NUMBER Latin1_General_CI_AI
4 SQLCHAR 0 19 ‘þ’ 5 OIC_CUSTOMER_NUMBER “”
5 SQLCHAR 0 10 ‘þ’ 6 OIC_INVOICE_DATE “”
6 SQLCHAR 0 10 ‘þ’ 7 OIC_AGING_BUCKET Latin1_General_CI_AI
7 SQLCHAR 0 8 ‘þ’ 8 OIC_REMIT_TO_CODE Latin1_General_CI_AI
8 SQLCHAR 0 1 ‘þ’ 9 OIC_DAILY_LC “”
9 SQLCHAR 0 0 ‘þ’ 10 OIC_MISC_MEMO Latin1_General_CI_AI
10 SQLCHAR 0 12 ‘þ’ 11 OIC_FACTURA_ISSUED “”
11 SQLCHAR 0 41 ‘þ’ 12 OIC_ACCUM_STATE_RATE “”
12 SQLCHAR 0 41 ‘þ’ 13 OIC_ACCUM_CNTY_RATE “”
13 SQLCHAR 0 41 ‘þ’ 14 OIC_ACCUM_CITY_RATE “”
14 SQLCHAR 0 41 ‘þ’ 15 OIC_ACCUM_TCNTY_RATE “”
15 SQLCHAR 0 41 ‘þ’ 16 OIC_ACCUM_TCITY_RATE “”
16 SQLCHAR 0 12 ‘þ’ 17 OIC_SINGLE_MISC_ASSETS “”
17 SQLCHAR 0 41 ‘þ’ 18 OIC_ACCUM_MISC_STATE_RATE “”
18 SQLCHAR 0 41 ‘þ’ 19 OIC_ACCUM_MISC_CNTY_RATE “”
19 SQLCHAR 0 41 ‘þ’ 20 OIC_ACCUM_MISC_CITY_RATE “”
20 SQLCHAR 0 41 ‘þ’ 21 OIC_ACCUM_MISC_TCNTY_RATE “”
21 SQLCHAR 0 41 ‘þ’ 22 OIC_ACCUM_MISC_TCITY_RATE “”
22 SQLCHAR 0 12 ‘þ’ 23 OIC_SPLIT_PARENT “”
23 SQLCHAR 0 12 ‘þ’ 24 OIC_RECON_DATE “”
24 SQLCHAR 0 6 ‘þ’ 25 OIC_FROM_AU Latin1_General_CI_AI
25 SQLCHAR 0 8 ‘þ’ 26 OIC_BATCH_NUM Latin1_General_CI_AI
26 SQLCHAR 0 12 ‘þ’ 27 OIC_MISC_REV_ORIG “”
27 SQLCHAR 0 12 ‘þ’ 28 OIC_GROUPED “”
28 SQLCHAR 0 2 ‘þ’ 29 OIC_OVERAGE_OI Latin1_General_CI_AI
29 SQLCHAR 0 8 ‘þ’ 30 OIC_INVOICE_SEND_DATE “”
30 SQLCHAR 0 1 ‘þ’ 31 OIC_INVOICE_PRINTED “”
31 SQLCHAR 0 41 ‘þ’ 32 OIC_VAT_REFUND_AMT “”
32 SQLCHAR 0 41 ‘þ’ 33 OIC_LC_FEE_AMT “”
33 SQLCHAR 0 41 ‘þ’ 34 OIC_CHRG_HIST_KEY “”
34 SQLCHAR 0 41 ‘þ’ 35 OIC_LS_AGGR_INV “”
35 SQLCHAR 0 41 ‘þ’ 36 OIC_AGGR_INV “”
36 SQLCHAR 0 41 ‘þ’ 37 OIC_SERV_INT_RENT_AMT “”
37 SQLCHAR 0 41 ‘þ’ 38 OIC_SERV_CONC_AMT “”
38 SQLCHAR 0 8 ‘þ’ 39 OIC_ACT_DUE_DATE “”
39 SQLCHAR 0 41 ‘þ’ 40 OIC_INCOME_AMOUNT “”
40 SQLCHAR 0 41 ‘þ’ 41 OIC_WH_TAX_PCT “”
41 SQLCHAR 0 41 ‘þ’ 42 OIC_WH_TAX_AMT “”
42 SQLCHAR 0 78 “\r\n” 43 OIC_USAGE_ID Latin1_General_CI_AI
Thanks in advvance for your support/help.
Thanks
Surendra
while importing excel data to sql server is it possible to know error at each line/ column ?I am using vb.net as front end.
Any suggestion?
i want to import csv file to database sql server 2005 please help
There are many options like BCP, BULK INSERT and SSIS package
one table dynamically create and store the csv data to that table
any stored procedure avilable please help me
Do you mean you want to pass csv name and table name as parameter and store vsc data to table? You can use BULK INSERT
thanks pinal..
i have errors with auto increment id but i resolve this issue using field terminator. FIELDTERMINATOR = ‘|’
|89426|GILBUELA, MIRALUNA|9/1/2012|A
I’ll put the FIELDTERMINATOR at first and experiment and it works..,
I want to load data from excel file into sql tables which are created by myself.
how can i do??
I want to import data from .csv file which contains about 1200 rows. I have no problem to import few rows with “Bulk Insert” but for 1200 rows I recieve the message “(0) rows affected”.
Please help!
regards
good work
Great job! I am looking an SQL job to import multiple text files into SQL Server. I have 1 million text files with change in prefix, can i automate this import process.
i executed the same script.but there is one error
“Cannot bulk load. The file “C:\csvtest.txt” does not exist.”
though i have already created the file csvtest.txt
Amruta,create your csvtest.txt to Notepad then save to C:\
thank you..it works
i created the following procedure,which table name and file name from user..
create PROCEDURE [dbo].[pr_csv_to_table]
@tablename varchar(255)
,@filename varchar(255)
as
DECLARE @sql AS NVARCHAR(1000)
SET @sql = ‘BULK INSERT ‘ + @tableName + ‘ FROM ”’ + @filename + ”’ WITH (rowterminator=”\n”, FIELDTERMINATOR=”,”)’
EXEC (@sql)
but i want to change the code such that the filepath remains same but the filename changes…. so how to hardcode the filepath and take filename from user?