dkochenour
asked on
SQL 2008 Bulk Insert issues
I am having issues with with csv file that works in sql 2000. I have identified why but not how to solve. If a row does not have data for the right most columns it just puts a '\n\r' without commas as placeholders. Bulk insert then ignores the row terminator.
Here is a sample of a typical file - It is the code snippet
Here is a sample of a typical file - It is the code snippet
20,00021B,N,12.56,,,,,12.34,12/31/2008,12.34,12/31/2008,12.34,12/31/2008,,,12.34,12/31/2008
20,01595J,N,218.07
20,01596J,N,257.99
20,01597J,N,439.83
20,01598J,N,331.91
20,02332I,N,9716.31
20,02333I,N,1505.44
20,02334I,N,1904.18
20,028030,Y,139.23,136.53,04/30/2008
20,028061,Y,349.39,342.61,04/30/2008
20,030028,Y,121.72,119.36,04/30/2008
20,03049I,N,3371.04
20,03051I,N,2527.22
20,03064I,N,5964.34
20,03065I,N,5964.34
20,038086,N,34.03,,,,,33.15,12/31/2008,33.15,12/31/2008,33.15,12/31/2008,,,33.15,12/31/2008
Can you post full command that you are using?
so what has changed - is it the generation of the CSV file, reading it, what creates the CSV without the placeholders for the detailed rows ?
Do you want a routine to read that file regardless ?
Does it have a header row ?
Why arent the commas there ? Is it a multiformat line ?
Do you want a routine to read that file regardless ?
Does it have a header row ?
Why arent the commas there ? Is it a multiformat line ?
ASKER
BULK INSERT ITEM_PROMOS_QUEUE_TST
FROM 'C:\ECSJOBS\TDG08022904182
WITH (FORMATFILE = 'C:\ECSJOBS\PromosPrice.fm
(1 row(s) affected)
FROM 'C:\ECSJOBS\TDG08022904182
WITH (FORMATFILE = 'C:\ECSJOBS\PromosPrice.fm
(1 row(s) affected)
ASKER
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
ASKER
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
10.0
18
1 SQLCHAR 0 2 "," 1 CORP_CODE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "," 2 IC_ITEM_NO SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "," 3 PROMO_IND SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 30 "," 4 COLUMN_PRICE_AMT ""
5 SQLCHAR 0 30 "," 5 PROMO_PRICE_AMT ""
6 SQLCHAR 0 30 "," 6 PROMO_EXPIRATION_DTE ""
7 SQLCHAR 0 30 "," 7 EF_PRICE_AMT ""
8 SQLCHAR 0 30 "," 8 EF_EXPIRATION_DTE ""
9 SQLCHAR 0 30 "," 9 EH_PRICE_AMT ""
10 SQLCHAR 0 30 "," 10 EH_EXPIRATION_DTE ""
11 SQLCHAR 0 30 "," 11 EL_PRICE_AMT ""
12 SQLCHAR 0 30 "," 12 EL_EXPIRATION_DTE ""
13 SQLCHAR 0 30 "," 13 FE_PRICE_AMT ""
14 SQLCHAR 0 30 "," 14 FE_EXPIRATION_DTE ""
15 SQLCHAR 0 30 "," 15 FG_PRICE_AMT ""
16 SQLCHAR 0 30 "," 16 FG_EXPIRATION_DTE ""
17 SQLCHAR 0 30 "," 17 ST_PRICE_AMT ""
18 SQLCHAR 0 30 "\r\n" 18 ST_EXPIRATION_DTE ""
OK your format file is likely to be specifying a column for every field in the CSV file. When it encounters a new line instead of the anticipated columns it create the error.
Just as a test, what happens if you specify just a single column terminated by \n\r (copy your format file first - or create a new test one)
Now, is this a legitimate CSV file ? What created it ? Do you have to handle variable length columns ?
Just as a test, what happens if you specify just a single column terminated by \n\r (copy your format file first - or create a new test one)
Now, is this a legitimate CSV file ? What created it ? Do you have to handle variable length columns ?
ASKER
This file is produced by our mainframe. Yes, we want to insert the row even if it only has 4 columns. It has always left the commas out if it just finds a price, but no govt pricing ehich what the other columns are for.
You should provide a row terminator character because of different number of columns by row.
ASKER
Mainframe creates it and yes there are variable length fields. When I pare down the format to the first 4 columns(which are aleways present) and end it with the \rn\ I get
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 17, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 27, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 30, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 43, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 44, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 53, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 56, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 63, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 94, column 4 (COLUMN_PRICE_AMT).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
which looks like it is complaining about the rows that hve the additioal columns
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 17, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 27, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 30, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 43, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 44, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 53, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 56, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 63, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 94, column 4 (COLUMN_PRICE_AMT).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
which looks like it is complaining about the rows that hve the additioal columns
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome solution _ Bless you!!! I have learned mucho today!!!
what an error did you get?