Post reply

Exclude Columns When Using Bulk Insert

  • April 29, 2015 at 7:29 pm

    #1794553

    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

  • April 30, 2015 at 12:45 am

    #1794572

    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

    1 SQLCHAR 0 18 ":" 1 MATNR ""

    2 SQLCHAR 0 10 ":" 2 ERSDA ""

    3 SQLCHAR 0 12 ":" 3 ERNAM ""

    4 SQLCHAR 0 10 ":" 4 LAEDA ""

    5 SQLCHAR 0 12 ":" 5 AENAM ""

    6 SQLCHAR 0 4 ":" 6 MTART ""

    7 SQLCHAR 0 1 ":" 7 MBRSH ""

    8 SQLCHAR 0 9 ":" 8 MATKL ""

    9 SQLCHAR 0 18 ":" 9 BISMT ""

    10 SQLCHAR 0 3 ":" 10 MEINS ""

    11 SQLCHAR 0 2 ":" 11 SPART ""

    12 SQLCHAR 0 18 ":" 12 EAN11 ""

    13 SQLCHAR 0 2 ":" 13 NUMTP ""

    14 SQLCHAR 0 18 ":" 14 EXTWG ""

    15 SQLCHAR 0 4 ":" 0 VKORG ""

    16 SQLCHAR 0 18 ":" 0 PRODH ""

    17 SQLCHAR 0 2 ":" 0 KONDM ""

    18 SQLCHAR 0 2 ":" 0 KTGRM ""

    19 SQLCHAR 0 3 ":" 0 MEINH ""

    20 SQLCHAR 0 6 ":" 0 UMREZ ""

    21 SQLCHAR 0 6 ":" 0 UMREN ""

    22 SQLCHAR 0 40 "\r" 0 MAKTX ""

    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 AENAM MTART MBRSH MATKL BISMT MEINS SPART EAN11 NUMTP EXTWG

    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)
    Intro to Tally Tables and Functions

  • April 30, 2015 at 9:31 am

    #1794743

    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?

  • April 30, 2015 at 1:00 pm

    #1794823

    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)
    Intro to Tally Tables and Functions

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

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