Avatar of dkochenour
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
 
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

Open in new window

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
dkochenour

8/22/2022 - Mon
EugeneZ

what are you using for bulk insert: SSIS; Command line, ?
what an error did you get?
Vitor Montalvão

Can you post full command that you are using?
Mark Wills

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 ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dkochenour

ASKER
BULK INSERT ITEM_PROMOS_QUEUE_TST
FROM 'C:\ECSJOBS\TDG080229041829001.832'
WITH (FORMATFILE = 'C:\ECSJOBS\PromosPrice.fmt')


(1 row(s) affected)
dkochenour

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)".
dkochenour

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                            ""

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Wills

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 ?
dkochenour

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.
Vitor Montalvão

You should provide a row terminator character because of different number of columns by row.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dkochenour

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




ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dkochenour

ASKER
Awesome solution _ Bless you!!! I have learned mucho today!!!