September 16, 2011 at 4:52 am
I am having a csv file which I have to upload on my database. The table is as follows:-
CREATE TABLE [dbo].[Out_orders_Table2](
[CompanyID] [nchar](3) NULL,
[ItemCode] [nchar](9) NULL,
[Description] [nchar](38) NULL,
[SupplierAccount] [nchar](9) NULL,
[PONumber] [nchar](8) NULL,
[POLineNumber] [numeric](3, 0) NULL,
[OutstandingQty] [numeric](6, 0) NULL,
[DueDate] [date] NULL,
[StockRoom] [nchar](3) NULL,
[PriorityFlag] [nchar](3) NULL
) ON [PRIMARY]
GO
The data file is like this:-
MK,12345678,ABC 123lr RK Finish abcde abcdef ,ABCDEFGH,A123456,1,1234,18/09/2010,W1,
CD,98765432,RR CTR APPLE JAM AND JELLY KITS ,ABCDEFGH,A654321,1,25000,08/06/2011,W1,
AB,123456,Ab1 Class AB2 3pk R/Ctn M&K Eu Oct07 ,ABCDE007,P123456,8,1200,11/09/2011,CD,
EF,123456,2 x Tennis balls, Rackets and bags ,TNSRCKBL,T763781,3,2800,10/08/2011,DL,
Please note,
1. In row 4 there is a comma in the field. And I have to accomodate them and also they are not having double quotes.
2. I have used a comma and space delimited file as well. to allow the fields having comma in them.
3. Also the last column 'Priority Flag' doesnt have any data in the CSV file, so the column before it ends like this.. ,CD,
4. I am using SQL SERVER 2008. I am using bcp format file with version 8.0 because it wasnt working for 10.0
Format File :-
8.0
10
1 SQLNCHAR 0 3 "" 1 CompanyID ""
2 SQLNCHAR 0 9 "" 2 ItemCode ""
3 SQLNCHAR 0 38 "" 3 Descritpion ""
4 SQLNCHAR 0 9 "" 4 SupplierAccount ""
5 SQLNCHAR 0 8 "" 5 PONumber ""
6 SQLNUMERIC 0 3 "" 6 POLineNumber ""
7 SQLNUMERIC 0 6 "" 7 OutstandingQty ""
8 SQLDATE 0 10 "" 8 DueDate ""
9 SQLNCHAR 0 3 "" 9 StockRoom ""
10 SQLNCHAR 0 3 "LF" 10 PriorityFlag ""
This format file I manually created. I have already tried:-
1. Using an auto generated format file using bcp utility. It gave errors.
2. In the above file using SQLCHAR instead of SQLNCHAR.
3.using "\rLF" as field terminator.
Its not printing end of line character here,.. God knows why. so I have put LF instead of <\> <n> backslash n
using bcp in command prompt:
bcp TestDB.dbo.Orders_Table2 in E:\Testing\OSOTest1.csv -f E:\Testing\OrdersF1.fmt -T -F 2 -b 100
Errors:-
Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Numeric value out of range
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
BCP copy in failed
==================================================================================
I used script to do the same thing. The stored procedure is as follows:-
USE [TestDB]
GO
CREATE PROCEDURE [dbo].[example_load_OS_file]
AS
DECLARE @filename varchar(30) = 'OSOTest1.csv'
DECLARE @TSQL varchar(2000)
SET @TSQL = '
BULK INSERT [Orders_Table2]
FROM '''+'E:\Testing\'+@filename+''' '
SET @TSQL = LTRIM(RTRIM(@TSQL))
-- PRINT @TSQL
EXEC(@TSQL+' WITH (formatfile = '''+'E:\Testing\OrdersF1.fmt'+''')')
Errors I receive when I execute this stored procedure:-
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)".
Should I use openrowset or SSIS for this. I have never worked with them before, and all other CSV files I have uploaded are using a stored procedure.. but this one doesnt seem to work.
Plz advice.. Thanks in advance.
September 16, 2011 at 5:04 am
8.0
10
1 SQLNCHAR 0 3 "," 1 CompanyID ""
2 SQLNCHAR 0 9 "," 2 ItemCode ""
3 SQLNCHAR 0 38 "," 3 Descritpion ""
4 SQLNCHAR 0 9 "," 4 SupplierAccount ""
5 SQLNCHAR 0 8 "," 5 PONumber ""
6 SQLNUMERIC 0 3 "," 6 POLineNumber ""
7 SQLNUMERIC 0 6 "," 7 OutstandingQty ""
8 SQLDATE 0 10 "," 8 DueDate ""
9 SQLNCHAR 0 3 "," 9 StockRoom ""
10 SQLNCHAR 0 3 "/r/n" 10 PriorityFlag ""
?
in last row change slash
I Have Nine Lives You Have One Only
THINK!
September 16, 2011 at 8:39 am
handkot (9/16/2011)
8.010
1 SQLNCHAR 0 3 "," 1 CompanyID ""
2 SQLNCHAR 0 9 "," 2 ItemCode ""
3 SQLNCHAR 0 38 "," 3 Descritpion ""
4 SQLNCHAR 0 9 "," 4 SupplierAccount ""
5 SQLNCHAR 0 8 "," 5 PONumber ""
6 SQLNUMERIC 0 3 "," 6 POLineNumber ""
7 SQLNUMERIC 0 6 "," 7 OutstandingQty ""
8 SQLDATE 0 10 "," 8 DueDate ""
9 SQLNCHAR 0 3 "," 9 StockRoom ""
10 SQLNCHAR 0 3 "/r/n" 10 PriorityFlag ""
?
In last row change slash
Thanks handkot, I have changed it still the error is the same.
September 16, 2011 at 9:09 am
SQL server and csv file are on a same mashine?
I Have Nine Lives You Have One Only
THINK!
September 16, 2011 at 9:20 am
try to use simple "bulk insert"
bulk insert [dbo].[Out_orders_Table2] from 'path to file' with (fieldterminator = ',')
I Have Nine Lives You Have One Only
THINK!
September 16, 2011 at 9:50 am
Yes they are.. I even tried using comma as field seperator and \t as field separator. When i use either in the stored procedure the error remains the same :-
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)".
while when I used it on command prompt using bcp the error for comma as field separator was:-
Error = [Microsoft][SQL Server Native Client 10.0]Invalid field size for datatyp
e
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid field size for datatyp
e
while with \t as the field separator the error was:-
Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number f
ound in BCP format-file
I event tried with ' -t, ' using bcp as field terminator. Do you think it can be because of the absence of any data in the last column of the csv file. Because it does not come across any characters?
September 16, 2011 at 10:00 am
@handkot, I have used it and it works the problem is when I get two rows which have commas in the fields. it doesnt work for them. and strange thing is if I open this file in Excel, and open it as a delimited file with both commas and tab as delimiters, it shows the two rows with the commas in them. And it doesnt terminate the field(which have commas inbuilt) as and when it encounters a comma.
September 16, 2011 at 11:56 pm
open it as a delimited file with both commas and tab as delimiters
is there both commas and tab as delimiters in each line in the file?
in this case? you should use ',\t' as delimiters
I Have Nine Lives You Have One Only
THINK!
September 17, 2011 at 1:01 am
can you upload csv-file to the forum?
I Have Nine Lives You Have One Only
THINK!
September 17, 2011 at 2:59 am
handkot (9/17/2011)
can you upload csv-file to the forum?
Hi Handkot,
The samole of data in csv file is as follows:-
MK,12345678,ABC 123lr RK Finish abcde abcdef ,ABCDEFGH,A123456,1,1234,18/09/2010,W1,
CD,98765432,RR CTR APPLE JAM AND JELLY KITS ,ABCDEFGH,A654321,1,25000,08/06/2011,W1,
AB,123456,Ab1 Class AB2 3pk R/Ctn M&K Eu Oct07 ,ABCDE007,P123456,8,1200,11/09/2011,CD,
EF,123456,2 x Tennis balls, Rackets and bags ,TNSRCKBL,T763781,3,2800,10/08/2011,DL,
There will be rows which have a comma in the field as well. For ex.:-
EF,123456,2 x Tennis balls, Rackets and bags ,TNSRCKBL,T763781,3,2800,10/08/2011,DL,
I tried using the \t as field terminator and commas as well. the errors I am getting are as follows:-
Using \stored procedure:-
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
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)".
2. Using bcp on command prompt
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file
September 19, 2011 at 1:09 am
Hi, dcs09
I have changed fmt-file and received result (see attachments)
CREATE TABLE [dbo].[Out_orders_Table2](
[CompanyID] [nchar](3) NULL,
[ItemCode] [nchar](9) NULL,
[Description] [nchar](38) NULL,
[SupplierAccount] [nchar](9) NULL,
[PONumber] [nchar](8) NULL,
[POLineNumber] [numeric](3, 0) NULL,
[OutstandingQty] [numeric](6, 0) NULL,
[DueDate] [nchar](30) NULL,
[StockRoom] [nchar](3) NULL,
[PriorityFlag] [nchar](3) NULL
) ON [PRIMARY]
GO
truncate table [Out_orders_Table2]
bulk insert [dbo].[Out_orders_Table2] from 'test.txt'
WITH (formatfile = 'testfmt.txt')
select * from [dbo].[Out_orders_Table2]
i changed type DueDate I work with another days format
does it that you expect?
P.S: if there is a bug, please send a few lines of a file as an attachment
I Have Nine Lives You Have One Only
THINK!
September 19, 2011 at 3:01 am
Hi Handkot,
Thanks for your response. I tried the bulk insert with your coding, but I got the following error:-
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (SupplierAccount).
Just that the last row has a comma in item description and I need to insert the data with the comma that is in there. The problem is it considers it as a field terminator. and starts to insert the data after it into the next column.
Do you have any suggestion.
I really need to sort out this thing.
Do you think SSIS will help?
September 19, 2011 at 9:54 am
Just that the last row has a comma in item description and I need to insert the data with the comma that is in there. The problem is it considers it as a field terminator. and starts to insert the data after it into the next column.
Did you use my fmt-file that i uploaded to the forum?
Do you have any suggestion.
would be better if I have a real file
alternatively, you can download the file into a temporary table with one field and then parse
I really need to sort out this thing.
Do you think SSIS will help?
I think SSiS will not help you 🙁
I Have Nine Lives You Have One Only
THINK!
September 19, 2011 at 10:09 am
I changed my format file to match exactly with urs.. changed the table as well to use date in nchar format. So as good as using ur version. I will send you a detailed sample file but its more or less of the same type. When you inserted the data was the last row getting inserted as well?
September 19, 2011 at 10:52 pm
Last row
[CompanyID] - EF
[ItemCode] - 123456
[Description] - 2 x Tennis balls, Rackets and bags
[SupplierAccount] - TNSRCKBL
[PONumber] - T763781
[POLineNumber] - 3
[OutstandingQty] - 2800
[DueDate] - 10/08/2011
[StockRoom] - DL
[PriorityFlag] - empty string
I Have Nine Lives You Have One Only
THINK!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply