October 5, 2017 at 10:01 am
Dear all,
I receive from a provider a csv file.
Sometimes this file end on column 3 but other times after the column 3 I have two more commas. Example:
Column1 , Column 2, Column 3
Pedro, Paula, Maria
Column1 , Column 2, Column 3,,
Pedro, Paula, Maria,,
Problem is that when it was two additional commas, even if they never have data, then I receive a message: "There is no column delimitator for column 3". this is on the flat file source level
How can I overcome this and say that after column3, nothing should be considered?
Thank you
October 5, 2017 at 10:35 am
You might try "ragged right" and see if that makes a difference, but the best alternative is to review your agreement(s?) with the provider(s?) and see if there's any mention of the correct file format, and if so, then refer the provider in question to the agreement. Ideally, such agreements should specify some form of SLA with regard to handling and processing of correctly formatted files as well as incorrectly formatted files, such as this.
Another alternative would be to write a script task and validate that the file meets the proper formatting conditions, or alternatively, lops off everything beyond a certain point, and then saves a new copy of the incoming file, and the rest of the SSIS package then always uses the new copy, even if there's nothing wrong with the original. A lot of extra work is involved of all of that, but if you don't have an agreement on file format that specifies what happens when a file isn't formatted correctly, you might be hard pressed to argue the point. Just be sure your script task recognizes quoted strings so that you don't accidentally lop off part of a quoted string that was quoted because it has a comma in it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 11:32 am
But if I had two additional columns just to guarantee that the file is able to be read. Would this be possible? For example at connection level or source level create two additional columns that I know will not be used. Would this let me read the file?
October 5, 2017 at 12:43 pm
This is a typical MDFP or multi document format problem, better import it line by line and then do the parsing and processing based on the number of delimiters in each row. Since the last column doesn't have any data, you can simply replace the double delimiters with a blank string!
😎
October 5, 2017 at 2:37 pm
Eirikur Eiriksson - Thursday, October 5, 2017 12:43 PMThis is a typical MDFP or multi document format problem, better import it line by line and then do the parsing and processing based on the number of delimiters in each row. Since the last column doesn't have any data, you can simply replace the double delimiters with a blank string!
😎
Right up until some numbnut comes along and provides no value for the 2nd field.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 2:23 am
river1 - Thursday, October 5, 2017 10:01 AMDear all,I receive from a provider a csv file.
Sometimes this file end on column 3 but other times after the column 3 I have two more commas. Example:
Column1 , Column 2, Column 3
Pedro, Paula, MariaColumn1 , Column 2, Column 3,,
Pedro, Paula, Maria,,Problem is that when it was two additional commas, even if they never have data, then I receive a message: "There is no column delimitator for column 3". this is on the flat file source level
How can I overcome this and say that after column3, nothing should be considered?
Thank you
I have a similar issue with an import package, where csv files can have any number of columns.
I wrote an article about how I solved it. It may not be ideal but it works for my situation. Importing CSV files that have a different number of columns
Edit: corrected link (thank you sgmunson)
October 6, 2017 at 8:41 am
FYI, that link just goes back to this topic. Your article link is here: https://sqlrambling.net/2017/07/28/ssis-importing-csv-files-that-have-different-number-of-columns/
I went to your blog from your signature and the article had a link there.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply