Post reply

Error when loading CSV file to SQL Server using SSIS. The Column Delimiter was not found

  • August 29, 2018 at 2:42 pm

    #2003906

    No Jeff the file is comma delimited only.

  • August 29, 2018 at 3:49 pm

    #2003921

    kashyap4007 - Wednesday, August 29, 2018 2:42 PM

    No Jeff the file is comma delimited only.

    Then I'm not sure how just removing quoted identifiers worked for you because you do have extra commas and the means extra "columns".

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

  • August 29, 2018 at 5:02 pm

    #2003934

    Jeff Moden - Wednesday, August 29, 2018 3:49 PM

    kashyap4007 - Wednesday, August 29, 2018 2:42 PM

    No Jeff the file is comma delimited only.

    Then I'm not sure how just removing quoted identifiers worked for you because you do have extra commas and the means extra "columns".

    SSIS still loads the data - and normally (not always) either the next row is missing or the last column contains the last block of data which would normally be split. 
    and all this without any error or warning about the validity of the data - unless after the "incorrect" column there are columns where the datatype is strict on its content (numbers or dates). Reason for this is that the last column separator is not a comma (or whatever delimiter was defined) but the row terminator.

  • August 29, 2018 at 5:15 pm

    #2003936

    Yeah, why bother loading the correct data?

    Main thing - the process does not report any errors.

    _____________
    Code for TallyGenerator

  • August 30, 2018 at 8:03 am

    #2004003

    Am sorry.
    You guys are right. As frederico_fonseca said the data is loading but it is not loading properly.
    Need to findout what I can do to get the data properly.

  • August 30, 2018 at 9:58 am

    #2004027

    What I don't Understand is why some of the data is getting loaded properly and not the other.
    For example the below two records are loading properly
    "33105000040000","25","AMERADA HESS CORPORATION","BEAVER LODGE-DEVONIAN UNIT  G-307","BEAVER LODGE-DEVONIAN UNIT","G-307","AMERADA PETROLEUM CORP.","C. IVERSON #1","09/03/1950","11955","WILLIAMS","155 N","95 W","6","SWSW","660 FSL 553 FWL","BEAVER LODGE","DEVONIAN,MADISON,SILURIAN","324488|33755|0,180041|70622|0,81960|76|0","09/27/1959|236|1|192,12/17/1951|677|3|820,04/04/1951|165|1|939","VERTICAL","48.271397999999998","-102.95471499999999","OG","PA","","08/02/1979"

    "33043000030000","24","MAGNOLIA PETROLEUM CO.","NORTH DAKOTA STATE A  1","NORTH DAKOTA STATE A","1","MAGNOLIA PETROLEUM CO.","NORTH DAKOTA STATE "A" #1","10/21/1950","5609","KIDDER","141 N","73 W","36","SENE","1980 FNL 810 FEL","WILDCAT","","","","VERTICAL","46.990158000000001","-99.864013","OG","DRY","","12/24/1950"

    But the when loaded the below record, it is not loading properly.
    "33009000130000","264","WARD-WILLISTON COMPANY","TEMPLE RESOURCES, INC. GREENE  3","TEMPLE RESOURCES, INC. GREENE","3","MURRY, STENJHEM, MURRY","LOTTIE GREEN #1","01/19/1953","3528","BOTTINEAU","163 N","80 W","23","SESE","656 FSL 665 FEL","NORTH SERGIS","MADISON","1749|17675|0","12/24/1985|8|12|0","VERTICAL","48.924692999999998","-100.996467","OG","PA","","09/04/2003"

  • August 30, 2018 at 10:18 am

    #2004035

    Any time you have delimiter problems, there is generally ONLY ONE WAY to permanently solve that kind of problem:  Put the onus for fixing it on the source of the data.
    Every other solution generally is going to have trouble being automated because the variety of kinds of errors that creep into text files when human beings are involved
    is nearly infinite.   It's rarely consistent and any lack of consistency usually guarantees a need for manual intervention, and usually, having to go to the source to verify
    each problem record.   It's often faster to just store a copy of the original received file, and then edit cautiously another copy and see if the file can be "fixed" correctly.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • August 30, 2018 at 10:26 am

    #2004038

    I have used simple find and replace in the text as a workaround in similar situations (at least until it can be fixed at the source)

  • August 30, 2018 at 10:34 am

    #2004041

    Yes you are right. It needs to be resolved on the source side but i am getting this file from a state government website and I am not sure how to get that corrected on their side.
    And Yes I can do it manually till it is resolved on the source side but I thought of scheduling this job to run daily so that new data gets refreshed without any manually intervention.

  • August 30, 2018 at 11:51 am

    #2004063

    My problems come from the federal gov't and I don't care to try to fix!...this link might be useful for scripting find and replace: https://stackoverflow.com/questions/5477209/how-to-replace-text-in-text-file-using-bat-file-script

  • August 30, 2018 at 3:43 pm

    #2004083

    I don't think senators write those files themselves.

    It must be some IT department or a contractor hired to do the job.

    Whoever they are working for - they must do their job properly.

    And if they don't - raise the issue with the data supplier.

    _____________
    Code for TallyGenerator

  • August 30, 2018 at 6:49 pm

    #2004092

    kashyap4007 - Thursday, August 30, 2018 9:58 AM

    What I don't Understand is why some of the data is getting loaded properly and not the other.
    For example the below two records are loading properly
    "33105000040000","25","AMERADA HESS CORPORATION","BEAVER LODGE-DEVONIAN UNIT  G-307","BEAVER LODGE-DEVONIAN UNIT","G-307","AMERADA PETROLEUM CORP.","C. IVERSON #1","09/03/1950","11955","WILLIAMS","155 N","95 W","6","SWSW","660 FSL 553 FWL","BEAVER LODGE","DEVONIAN,MADISON,SILURIAN","324488|33755|0,180041|70622|0,81960|76|0","09/27/1959|236|1|192,12/17/1951|677|3|820,04/04/1951|165|1|939","VERTICAL","48.271397999999998","-102.95471499999999","OG","PA","","08/02/1979"

    "33043000030000","24","MAGNOLIA PETROLEUM CO.","NORTH DAKOTA STATE A  1","NORTH DAKOTA STATE A","1","MAGNOLIA PETROLEUM CO.","NORTH DAKOTA STATE "A" #1","10/21/1950","5609","KIDDER","141 N","73 W","36","SENE","1980 FNL 810 FEL","WILDCAT","","","","VERTICAL","46.990158000000001","-99.864013","OG","DRY","","12/24/1950"

    But the when loaded the below record, it is not loading properly.
    "33009000130000","264","WARD-WILLISTON COMPANY","TEMPLE RESOURCES, INC. GREENE  3","TEMPLE RESOURCES, INC. GREENE","3","MURRY, STENJHEM, MURRY","LOTTIE GREEN #1","01/19/1953","3528","BOTTINEAU","163 N","80 W","23","SESE","656 FSL 665 FEL","NORTH SERGIS","MADISON","1749|17675|0","12/24/1985|8|12|0","VERTICAL","48.924692999999998","-100.996467","OG","PA","","09/04/2003"

    I cannot find any issue with thse 3 strings.
    You must have tweaked your upload procedure to deal with faulty lines up to the point when it cannot parse correctly proper lines.

    My parser returns 17 columns from each row, no quotes in any of them:
    RowNo    Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8    Col9    Col10    Col11    Col12    Col13    Col14    Col15    Col16    Col17
    1    33105000040000    25    AMERADA HESS CORPORATION    BEAVER LODGE-DEVONIAN UNIT G-307    BEAVER LODGE-DEVONIAN UNIT    G-307    AMERADA PETROLEUM CORP.    C. IVERSON #1    09/03/1950    11955    WILLIAMS    155 N    95 W    6    SWSW    660 FSL 553 FWL    BEAVER LODGE
    2    33043000030000    24    MAGNOLIA PETROLEUM CO.    NORTH DAKOTA STATE A 1    NORTH DAKOTA STATE A    1    MAGNOLIA PETROLEUM CO.    NORTH DAKOTA STATE     10/21/1950    5609    KIDDER    141 N    73 W    36    SENE    1980 FNL 810 FEL    WILDCAT
    3    33009000130000    264    WARD-WILLISTON COMPANY    TEMPLE RESOURCES, INC. GREENE 3    TEMPLE RESOURCES, INC. GREENE    3    MURRY, STENJHEM, MURRY    LOTTIE GREEN #1    01/19/1953    3528    BOTTINEAU    163 N    80 W    23    SESE    656 FSL 665 FEL    NORTH SERGIS

    _____________
    Code for TallyGenerator

Viewing 12 posts - 16 through 26 (of 26 total)

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