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
how to register plzz suggest…
It’s very helpful .
Many thanks
Hi guyz
I have this problem. I want to read from “test.csv” file. This file is a Microsoft Excel Comma Seperated Values file. The content is seperated by columns and rows.
Every time I want to read from this I want to read the last row (not the whole file).
Please help me guyz,this is due by end of this month.
You can make use of firstrow option in bcp
Post is useful.
Thanks
Sir I Want To Table Data Write In Text File
Sir I Want to write Text File From My Query
Use bcp
TRY THIS SCRIPT -))
SELECT * FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source=C:\temp\P00.xls;Extended Properties=Excel 8.0’)…[Parts$]
Hi all,
Sorry for writing a very big question. I have a file which has both commas and ” being used as delimiters. So I created a format file to input the data into my table. I created a table dbo.Testing for this and tried using bcp format file to input the data into it but after several attempts, and applying many permutations combinations it doesnt seem to work. Every time it gives the error
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file
CREATE TABLE [dbo].[Testing](
[Company] [varchar](2) NULL,
[Item] [varchar](15) NULL,
[Item Description] [varchar](80) NULL,
[IssueUOM] [varchar](2) NULL,
[PackSize] [numeric](11, 4) NULL,
[ShippingFormat] [varchar](10) NULL,
[CartonHeight] [numeric](9, 4) NULL,
[CartonWidth] [numeric](9, 4) NULL,
[CartonDepth] [numeric](9, 4) NULL,
[CartonVolume] [numeric](9, 4) NULL,
[CartonWeight] [numeric](9, 4) NULL,
[ShipperHeight] [numeric](9, 4) NULL,
[ShipperWidth] [numeric](9, 4) NULL,
[ShipperDepth] [numeric](9, 4) NULL,
[ShipperVolume] [numeric](9, 4) NULL,
[ShipperWeight] [numeric](9, 4) NULL,
[CartonsPerShipper] [numeric](9, 0) NULL,
[ShipperQuantity] [numeric](9, 0) NULL,
[PalletTi] [numeric](9, 0) NULL,
[PalletHi] [numeric](9, 0) NULL,
[PalletWeight] [numeric](9, 0) NULL,
[PalletQuantity] [numeric](9, 0) NULL,
[PrimaryStockroom] [varchar](2) NULL,
[Supplier] [varchar](8) NULL,
[CountryCode] [varchar](3) NULL,
[GTFamily] [varchar](15) NULL,
[MinOrderQty] [numeric](11, 3) NULL,
[FOBPurchasePrice] [numeric](15, 5) NULL,
[FobCurrency] [varchar](3) NULL,
[StandardCost] [numeric](15, 5) NULL,
[StandardCostCur] [varchar](3) NULL,
[JSP] [numeric](15, 5) NULL,
[JSPCurrency] [varchar](3) NULL,
[TariffCode] [varchar](8) NULL,
[TariffCodeDesc] [varchar](30) NULL,
[UPNBarcode] [varchar](17) NULL,
[EANBarcode] [varchar](28) NULL
)
In the format file I have used the following field delimiters as per the field having or not having double quotes,
Field Starts With Field Ends With Next Field Starts Field Terminator Example
1. “ “ “ “\”,\”” “AB”,”CD”
2. “ “ After Comma(Without “) “\”,” “AB”,123
3. After Comma (Without “) Comma With “ “\,”” 123,”AB”
4. After Comma Comma After Comma “,” 123,123
The format file is as follows:-
10.0
37
1 SQLCHAR 0 2 “\”,\”” 1 Company Latin1_General_CI_AS
2 SQLCHAR 0 15 “\”,\”” 2 Item Latin1_General_CI_AS
3 SQLCHAR 0 80 “\”,\”” 3 ItemDescription Latin1_General_CI_AS
4 SQLCHAR 0 2 “\”,” 4 IssueUOM Latin1_General_CI_AS
5 SQLCHAR 0 41 “,” 5 PackSize “”
6 SQLCHAR 0 10 “\”,” 6 ShippingFormat Latin1_General_CI_AS
7 SQLCHAR 0 41 “,” 7 CartonHeight “”
8 SQLCHAR 0 41 “,” 8 CartonWidth “”
9 SQLCHAR 0 41 “,” 9 CartonDepth “”
10 SQLCHAR 0 41 “,” 10 CartonVolume “”
11 SQLCHAR 0 41 “,” 11 CartonWeight “”
12 SQLCHAR 0 41 “,” 12 ShipperHeight “”
13 SQLCHAR 0 41 “,” 13 ShipperWidth “”
14 SQLCHAR 0 41 “,” 14 ShipperDepth “”
15 SQLCHAR 0 41 “,” 15 ShipperVolume “”
16 SQLCHAR 0 41 “,” 16 ShipperWeight “”
17 SQLCHAR 0 41 “,” 17 CartonsPerShipper “”
18 SQLCHAR 0 41 “,” 18 ShipperQuantity “”
19 SQLCHAR 0 41 “,” 19 PalletTi “”
20 SQLCHAR 0 41 “,” 20 PalletHi “”
21 SQLCHAR 0 41 “,” 21 PalletWeight “”
22 SQLCHAR 0 41 “\,”” 22 PalletQuantity “”
23 SQLCHAR 0 2 “\”,\”” 23 PrimaryStockroom Latin1_General_CI_AS
24 SQLCHAR 0 8 “\”,\”” 24 Supplier Latin1_General_CI_AS
25 SQLCHAR 0 3 “\”,\”” 25 CountryCode Latin1_General_CI_AS
26 SQLCHAR 0 15 “\”,” 26 GTFamily Latin1_General_CI_AS
27 SQLCHAR 0 41 “,” 27 MinOrderQty “”
28 SQLCHAR 0 41 “\,”” 28 FOBPurchasePrice “”
29 SQLCHAR 0 3 “\”,” 29 FobCurrency Latin1_General_CI_AS
30 SQLCHAR 0 41 “\,”” 30 StandardCost “”
31 SQLCHAR 0 3 “\”,” 31 StandardCostCur Latin1_General_CI_AS
32 SQLCHAR 0 41 “\,”” 32 JSP “”
33 SQLCHAR 0 3 “\”,\”” 33 JSPCurrency Latin1_General_CI_AS
34 SQLCHAR 0 8 “\”,\”” 34 TariffCode Latin1_General_CI_AS
35 SQLCHAR 0 30 “\”,\”” 35 TariffCodeDesc Latin1_General_CI_AS
36 SQLCHAR 0 17 “\”,\”” 36 UPNBarcode Latin1_General_CI_AS
37 SQLCHAR 0 28 “\”\r\n” 37 EANBarcode Latin1_General_CI_AS
Sample of Data to be inserted
“AB”, “530002 “,”2 x Coffee Mugs, Lid and Spoon “, “MD” , 12.0000 ,” “,12.8346,12.2047 ,9.6457 ,1510.9260 ,4.1447 ,.0000 ,.0000,.0000 ,.0000 ,.0000 ,0 ,0 ,0 ,0,.0000,0 ,”NJ”,”PMRTYS50″,”HK “,”JCL “,12000.000 ,.00000 ,”HKD”,.00000 ,”USD”,.00000 ,”USD”,”00123008″,” “,”987654321123 “,”(24)11223344556677(99)00 ”
“CD”,”530004 “,”TT Crystal Bowls x 4 “, “MK” , 12.0000 ,” “,12.9921,12.2047 ,11.8110,1872.8075 ,5.7320 ,.0000 ,.0000 ,.0000 ,.0000 ,.0000,0 ,0 ,0 ,0,.0000,0 ,”NJ”,”DHTSK50″,”CN “,”JCL “,6000.000 ,.00000 ,”HKD”,.00000 ,”USD”,.00000 ,”USD”,”12341123″,” “,”123456789987 “,”(23)12345678911223 (88)00 ”
H:\>bcp Trial.dbo.Testing in E:\Trialfiles\Sample.txt -f Test3-c.fmt -S uklesq01 –T
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file
Please help. Thanks in advance.
DC
I have a csv file of 4 columns. but the table contains 6 colums (2 columns extra added as per requirmrnt) now i am getting error in inserting…Is it possible to do this when we have different columns in csv and target table..reply asap
This will help you
I have to auto import a monthly file. I cannot edit the incoming file before the import. The file is pipe delimeted. I have done this but its messy and takes forever. I want a more elegant solution.
The challenge is that the file contains records with varying numbers of columns. Account records have 15 columns and Transaction records have 9 columns. All in the same file! The record types are distinguishable in the third column of the file (A or T).
Using this approach export data to single table
Then based on the third column export data to respective tables
Thank you so much for this wonderful lesson.
is It possible to insert only few Columns from a CSV File to SQL table?
Ex : i have id,name,email in My CSV. and i need to insert only id,name to my Table.
is It possible to insert only few Columns from a CSV File to SQL table?
Ex : i have id,name,email in My CSV. and i need to insert only id,name to my SQL Server table
This post explains how to do it
abhi / Jay
Create a view with the four columns in the import file and then import into the view. Lots of people will tell you to use a format file but I prefer a view because it moves with the database. i.e. if you move the database to a new server, you don’t have to remember to move the format file and change your sproc to the new location.
eg
Table – TestTbl: col1, col2, col3, col4, col5, col6
Create view vw_TestTbl
as
Select t
col1, col2, col3, col4
from
TestTbl
Then import into vw_TestTbl
You can also use this technique if your target table has an identity column i.e. create a view without the identity and import into that. This is brilliant because it guarantees your import will be in the same order as the incoming file – even if there are page splits.
Hope this helps
Very helpful, thank you. However, how would you modify this script if you have a CSV 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 was able to load the data but the data didn’t load correctly. I have been searching for a long time and cannot make this work. Can anyone please help?
Use this approach
Please Help me
I want to execute SQL Query automatically every day midnight,
i would like the results to be in text file with semicolon delimiter ,
the text file name must be the same as the date of saving i.e. the text file of 12-09-2011 mus be 12-09-2011.txt and so on
thanks in advanced
Create the procedure specified in this blog post
Schedule it as a job to run periodically
Brig,
See my earlier post. I haven’t had an expert give me an answer. The way I do it is to Bulk Insert into a temporary table without the delimiter so I get one column with the full row text including the commas. I then added script to the proceedure to count the commas and insert accordingly, In my case, I had to write to different tables depending on the number of columns in the rows.
Something like this (not tested).
INSERT Table1
(
Col1,
Col2,
Col3
)
SELECT
dbo.SplitString (DataColumn, 1, ‘,’),
dbo.SplitString (DataColumn, 2, ‘,’),
dbo.SplitString (DataColumn, 3, ‘,’)
FROM #TempTbl
WHERE LEN(DataColumm) – LEN(REPLACE(DataColumn,’,’,”) = 3
INSERT Table2
(
Col1,
Col2,
Col3,
Col4,
)
SELECT
dbo.SplitString (DataColumn, 1, ‘,’),
dbo.SplitString (DataColumn, 2, ‘,’),
dbo.SplitString (DataColumn, 3, ‘,’),
dbo.SplitString (DataColumn, 4, ‘,’)
FROM #TempTbl
WHERE LEN(DataColumm) – LEN(REPLACE(DataColumn,’,’,”) = 4
ETC
SplitString is a function. This is an example of what you can do but is limited to a maximum of 4 columns. There are other scripts on the internet you will find which will work with unlimited columns.
Anyway, here’s a taster: Copy it into query analyser and you’ll have all the pretty colours and formatting:
CREATE FUNCTION dbo.SplitString
(
@String VARCHAR(MAX),
@Position INT,
@Delimeter CHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@Data1 VARCHAR(MAX),
@Data2 VARCHAR(MAX),
@Data3 VARCHAR(MAX),
@Data4 VARCHAR(MAX),
@Result VARCHAR(MAX)
SELECT
@String = @String + @Delimeter,
@Data1 = LEFT(@String, CHARINDEX(@Delimeter, @String)-1),
@String = REPLACE(@String, @Data1 + @Delimeter, ”),
@Data2 = CASE
WHEN CHARINDEX(@Delimeter, @String) > 0 THEN
LEFT(@String, CHARINDEX(@Delimeter, @String)-1)
ELSE NULL
END,
@String = REPLACE(@String, @Data2 + @Delimeter, ”),
@Data3 = CASE
WHEN CHARINDEX(@Delimeter, @String) > 0 THEN
LEFT(@String, CHARINDEX(@Delimeter, @String)-1)
ELSE NULL
END,
@String = REPLACE(@String, @Data3 + @Delimeter, ”),
@Data4 = CASE
WHEN CHARINDEX(@Delimeter, @String) > 0 THEN
LEFT(@String, CHARINDEX(@Delimeter, @String)-1)
ELSE NULL
END
SET
@Result = CASE
WHEN @Position = 1 THEN @Data1
WHEN @Position = 2 THEN @Data2
WHEN @Position = 3 THEN @Data3
WHEN @Position = 4 THEN @Data4
ELSE NULL
END
RETURN @Result
END
Hope this helps
Thanks Madhivanan – awesome. Based on the premise; “don’t use code if you don’t understand it”, I pulled it to bits and learned a couple of very useful techniques. Not sure I would call it elegant though – I tend to view dynamic SQL as a necessary evil, doubling up on all those quotes and turning everything red just because In (@Pivot) doesn’t work. I liked the technique in the link from the link. A while exists without actually deleting anything – cool!
07/30/2011 4:20:33 PM
Trans.: 8621 Store: 05609
Reg.: 006
Cashier: 1926995 Valid No:3621
SALE
STRAIGHT JEAN 15.00
T482651 3332 1 @ 25.00
Item Discount Amt. -10.00
521 – Groupon
Total Discount -10.00
Subtotal 15.00
T1 Taxable Amount 15.00
T1 (9.0000%) Tax 1.35
Total Tax 1.35
Total 16.35
Gift Card 10.00
Account: 6003870494682016
Entry: Manual
Auth: AUTH 000000 (A)
GC Remaining Balance: 0.00
AMEX (S) 6.35
Account: XXXXXXXXXXX1005
Auth: AUTH 563008 (A)
Total Tender 16.35
Change Due 0.00
i need to import all the details into my sql database. can anyone help me out in this inssue
Do you have seperate tables created? You can use BULK INSERT statement to import data to respective tables from each text file
You should probably write some code to convert your info to a class or xml or csv or table and then try to sync with db