Ignore fields with Commas

Question

Ignore fields with Commas

Mordred

SSCrazy

Points: 2904
More actions
November 21, 2012 at 12:46 pm

#265144

Hi all,
I have a dataflow task that makes an initial connection to a flatfile csv. There are 4 columns ie: | Ref Date | Geo | Comm | Value |
Within some of the fields in the Geo and Comm columns there are strings of text that are comma separated which gives me truncation errors. What I want is for SSIS to ignore the commas within these fields but still separate the 4 columns properly. As an example of a field: "Iqaluit, Nunavut". I want this and other fields to be loaded as they are into the db but as I stated, I get truncation errors at connection time. I've played around with ignoring truncation errors but SSIS still separates the fields when I do the db load. Is there a workaround that I can use?
Regards:
Mordred
Keep on Coding in the Free World

Viewing 9 posts - 1 through 8 (of 8 total)

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

Mordred SSCrazy Points: 2904 More actions · Answer 1

After all the Googling I've done today on this matter it seems like I am going to have to put each field value into a string, check it for a comma, and then remove it if it has one. Then I'll be able to make a proper connection. This doesn't sound very efficient to me and will no doubt be very time consuming but until I see someone offer something better, this will be my way.

Regards:
Mordred
Keep on Coding in the Free World

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

I'm no SSIS Ninja by any means but I believe there's a check-box associated with the task that will allow you to identify text-qualified columns with a double quote so you don't have to go through the hassle yourself.

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

Mordred SSCrazy Points: 2904 More actions · Answer 3

Hi Jeff, I tried that yesterday and I still received truncation errors because SSIS is still trying to separate the columns where there are commas within the quotes. Thanks though.

Regards:
Mordred
Keep on Coding in the Free World

learning_sql Right there with Babe Points: 727 More actions · Answer 4

Mordred (11/22/2012)
Hi Jeff, I tried that yesterday and I still received truncation errors because SSIS is still trying to separate the columns where there are commas within the quotes. Thanks though.

I am no expert but I have done the following before:

Remove the flat file source from the SSIS and replace it with a SQL Execution task in the control flow - simply use BULK INSERT in the task using the "|" as a delimiter - then you can deal with the commas in the table itself after it has been imported...it isn't as "tidy" as the perfect solution...

BULK INSERT <tablename>

FROM '<filelocation>'

WITH (

FIELDTERMINATOR = '|',

ROWTERMINATOR = '')

GO

Hope it helps...

EDIT - the rowterminator is "\ n" without a space...the code tags don't like it.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

you need to set the Text Qualifier in the File connection editor, then make sure that it has been set on the columns you need under the Advanced Tab, which should have the TextQualified set property set to true.

There is also a chance that the Field length in the file is larger than the definition and can also cause a similar issue.

The final issue is that there could be " marks in side that field itself eg "First Part"hello",secondpart", which can cause SSIS to throw a similar error.

Having had numersous problems like this with CSV format files and SSIS, as a rule I request that files come with a #~ (or #|~) delimiter as its an extremely unlikely combination,( though not entirely unknown!!!) to get in a real data.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Mordred SSCrazy Points: 2904 More actions · Answer 6

@ learning_sql, I am going to give that a try and I thank you for that.

@ Jason, I would love to be able to do a bulk insert but that option has been disabled by the network and db guru's within the company that I work for and I'm not really sure why. If I could do that I would dump the whole csv file into a temp table and deal with the issues but unfortunately that is just not an option for me.

Regards:
Mordred
Keep on Coding in the Free World

Mordred SSCrazy Points: 2904 More actions · Answer 7

Mordred (11/23/2012)
@ learning_sql, I am going to give that a try and I thank you for that.
@ Jason, I would love to be able to do a bulk insert but that option has been disabled by the network and db guru's within the company that I work for and I'm not really sure why. If I could do that I would dump the whole csv file into a temp table and deal with the issues but unfortunately that is just not an option for me.

LoL, I have to wait on approval for the bulk insert and you both said basically the same thing and I'm just realizing that. It's been a long 24 hours!

Regards:
Mordred
Keep on Coding in the Free World

learning_sql Right there with Babe Points: 727 More actions · Answer 8

Mordred (11/23/2012)
Mordred (11/23/2012)
@ learning_sql, I am going to give that a try and I thank you for that.
@ Jason, I would love to be able to do a bulk insert but that option has been disabled by the network and db guru's within the company that I work for and I'm not really sure why. If I could do that I would dump the whole csv file into a temp table and deal with the issues but unfortunately that is just not an option for me.
LoL, I have to wait on approval for the bulk insert and you both said basically the same thing and I'm just realizing that. It's been a long 24 hours!

good luck - couldn't the db guru help?!