14

I have a CSV file - it has many values with comma as a part of them. The commas within the fields will mislead my SSIS package making it seem like the row has more columns than previously.

How to resolve this?

Eg:

Name,Amount,Address
Me,20,000,My Home,India
you,23,300,Your Home,Where

here only 3 columns exist but SSIS assumes all commas used to separate fields. However, that is not the case: Amount column and Address column have extra commas.

Improve this question

4 Answers 4

Reset to default
31

Use Text Qualifier in Flat file Connection Manager as ". This will separate only values out of quotes by commas. Your data from db will be enclosed in quotes.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

just in case anyone is wondering, that's a double quote, not a single quote :)
4

You will need to ensure that all values in the file that have commas as part of the text are enclosed in double quotes ("). Then you can set the text qualifier in your flat file source, and the commas will be loaded as part of the string fields.

Improve this answer

4 Comments

@N West I cannot add " to all values since there are hundreds of rows with many columns.Its painstaking job to do.The source of these files does delimiting when they are created(I cant change it!!!).Any other approaches?
Whoever is generating these files needs to fix their process to include " delimiters. Computers don't know the context of the data!! If you can't have that done, You would have to come up with some kind of rule that decides when a comma represents a delimiter, and when it is just part of the text. I think a custom script component would be necessary to pre-process the data, identify where a comma is part of a number, and then treat that as one field. Of course, what do you do with large numbers like 1,234,567....
lenashore.com/2012/04/…
@kravits88 nice! Only other thing to do would be to ensure that quotes are properly escaped tools.ietf.org/html/rfc4180
2

In the flat file connection manager editor, change the text quantifier to double quotes (") instead of <none>.

Flat File Connection Manager

This will take the content from one column as a single entity without splitting when there is a comma (,) in between text.

Hope this helped.

Improve this answer

Comments

0

I feel that a better way of doing this is by using a Derived column Transformation Editor in SSIS Data Flow, and changing the expression to surround the string with double quotes.

For instance if the column "Address" has records with commas, then create an expression such as

"\""+Address+"\""

in the Derived Column Transformation Editor.

I think this is a good option if you are not willing to transform the source.

Improve this answer

1 Comment

I have used above expression and notice mutile columns has combined into single column. For example - ",\N,,,6139,MA,Food Preparation,Hood,JH,608,"Traditional Hood".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.