Error when loading CSV file to SQL Server using SSIS. The Column Delimiter was not found
-
No Jeff the file is comma delimited only.
- kashyap4007 - Wednesday, August 29, 2018 2:42 PM
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) - Jeff Moden - Wednesday, August 29, 2018 3:49 PM
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. -
Yeah, why bother loading the correct data?
Main thing - the process does not report any errors.
_____________
Code for TallyGenerator -
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. -
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"
-
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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)
-
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. -
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
-
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 - kashyap4007 - Thursday, August 30, 2018 9:58 AMWhat 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