Exclude Columns When Using Bulk Insert

Question

Exclude Columns When Using Bulk Insert

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

anju04n6 SSC Enthusiast Points: 128 More actions · Answer 1

thank you for the advice, I will post might want to start a new thread , how ever it is related to the same issue. I am trying to ignore the columns same time I need to tell my code move to next line for reading the data

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

anju04n6 (4/29/2015)
Help needed please
I am trying to run below query fro bulk insert
SELECT MATNR,CASE WHEN ERSDA='00000000' THEN NULL ELSE CONVERT(datetime, ERSDA, 102) END AS ERSDA,ERNAM, CASE WHEN LAEDA='00000000' THEN NULL ELSE CONVERT(datetime, LAEDA, 102) END AS LAEDA,AENAM,MTART,MBRSH,MATKL,BISMT,MEINS,SPART,EAN11,NUMTP,EXTWG
FROM OPENROWSET(BULK 'D:\Loads_Test\Data\MARA.txt' ,FORMATFILE = 'D:\Loads_Test\Formats\MARA.fmt') as a
and the format file used is as below
1SQLCHAR018":"1MATNR""
2SQLCHAR010":"2ERSDA""
3SQLCHAR012":"3ERNAM""
4SQLCHAR010":"4LAEDA""
5SQLCHAR012":"5AENAM""
6SQLCHAR04":"6MTART""
7SQLCHAR01":"7MBRSH""
8SQLCHAR09":"8MATKL""
9SQLCHAR018":"9BISMT""
10SQLCHAR03":"10MEINS""
11SQLCHAR02":"11SPART""
12SQLCHAR018":"12EAN11""
13SQLCHAR02":"13NUMTP""
14SQLCHAR018":"14EXTWG""
15SQLCHAR04":"0VKORG""
16SQLCHAR018":"0PRODH""
17SQLCHAR02":"0KONDM""
18SQLCHAR02":"0KTGRM""
19SQLCHAR03":"0MEINH""
20SQLCHAR06":"0UMREZ""
21SQLCHAR06":"0UMREN""
22SQLCHAR040"\r"0MAKTX""
I keep getting the truncate error like below, I am assuming it because it is not able to identify the carriage return
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 4, column 1 (MATNR).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 7, column 1 (MATNR).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 8, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 9, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 10, column 1 (MATNR).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 11, column 2 (ERSDA).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 12, column 2 (ERSDA).
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)".
example of data in the MARA.txt file is as below, Colon (:) is the delimiter between the fields
D1122047-001 :20011022:CONVERSION_4:20100323:DELVAV1 :ROH :D:S-BATT : :EA : : : : :0020: : : :EA : 1 : 1 :BATTERY,12V,DURACELL
D1122047-002 :20011022:CONVERSION_4:20140507:MALONJ6 :ROH :D:S-BATT : :EA : : : : :0020: : : :EA : 1 : 1 :BATTERY,12V,ENERGIZER
D1122048-001 :20011022:CONVERSION_4:20140912:RYCROM1 :ROH :D:F-GTELEM : :EA : : : : :0020: : : :EA : 1 : 1 :BATTERY,AAA
D1122049-001 :20011022:CONVERSION_4:20131113:SAMANM2 :ROH :D:F-P511 : :EA : : : : :0020: : : :EA : 1 : 1 :BTRY,RECHARGEABLE, 3V,4.5MAH,ML621S
D1122050-001 :20011022:CONVERSION_4:20110817:MALONJ6 :ROH :D:F-P511 : :EA : : : : :0020: : : :EA : 1 : 1 :IC,MAX1675EUA
D1122051-001 :20011022:CONVERSION_4:20130329:FF-URGENT2 :ROH :D:F-P511 : :EA : : : : :0020: : : :EA : 1 : 1 :IC,5 VOLT CHARGE PUMP
Output of Select Query in the above bulk insert returns just below single row. as it is trying to append the date from last column of previous row and first column of the next row and because the 1st column length is only 18 chars it throws error (eg. BATTERY,12V,DURACELL D1122047-002 , where as it has to return only D1122047-002 for the first column value. I got to know this wen I increased the datafield length from 18 chars to 50 chars)
MATNR ERSDA ERNAM LAEDA AENAMMTART MBRSHMATKLBISMTMEINSSPARTEAN11NUMTPEXTWG
D1122047-001 2001-10-22 CONVERSION_4 2010-03-23 DELVAV1 ROH D S-BATT EA

anju04n6 (4/29/2015)
thank you for the advice, I will post might want to start a new thread , how ever it is related to the same issue. I am trying to ignore the columns same time I need to tell my code move to next line for reading the data

It would be highly unusual for a file to use just CHAR(13) (Carriage return or \r). Use a hex editor and double check. The "normal" formats are \r\ n (without the space) or just \ n (again, without the space). \r will show up as "0d" and \ n will show up as "0a"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anju04n6 SSC Enthusiast Points: 128 More actions · Answer 3

thank you for the response Jeff.

I have tried earlier with \r\ n and . I think just before I posted the question I changed it to \r to test and the same has been copy pasted.

I am confused, in the format file if we see the last column has been excluded. Even if the column is excluded or included , the bulk copy should not consider its value to insert into the 1st column. That means its not able to Identify the new line character?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

anju04n6 (4/30/2015)
thank you for the response Jeff.
I have tried earlier with \r\ n and . I think just before I posted the question I changed it to \r to test and the same has been copy pasted.
I am confused, in the format file if we see the last column has been excluded. Even if the column is excluded or included , the bulk copy should not consider its value to insert into the 1st column. That means its not able to Identify the new line character?

It will certainly recognize the new line character whether or not the last "field" is included or excluded. The question is, what is the actual row terminator. Have you tried with just \ n?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)