Flatfile delimiters

[John Rossitter]

Hi All,

I’m working with a rather unpredictable client at present.
They provide us CSV files from a Linux based platform, which sometimes
conform to good CSV rules and sometime don't.

Most of the time their data looks like:

"TeleohoneNumber","TimeOfCall","City","Customer"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"

When it does I'm able to set a (") qualifier and remove the quotes from the
file.
However, sometimes they send data that looks like:

"TeleohoneNumber","TimeOfCall","City","Customer"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"

(Note the extra comma in the customer name)
They claim that they have no control over this data, and cannot strip it out.

My question is, how can I use CoulumnDelimiter field in the advanced tab of
the Flat File Connection Manager Editor to tell SSIS to ignore these extra
commas?

Thanks in advanced.

John Rossitter

[Atul Thakor]

I'm not sure I understand the problem correclty but if you've setup the flat
file connection with a column delimeter as comma and a text qualifier the
names will be classed a single element?
eg:"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"

Post by John Rossitter
"3185551234"
"01/01/2009 01:01:00"
"Shreveoport"
"John,Smith"
Hi All,
I’m working with a rather unpredictable client at present.
They provide us CSV files from a Linux based platform, which sometimes
conform to good CSV rules and sometime don't.
"TeleohoneNumber","TimeOfCall","City","Customer"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John Smith"
When it does I'm able to set a (") qualifier and remove the quotes from the
file.
"TeleohoneNumber","TimeOfCall","City","Customer"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
"3185551234","01/01/2009 01:01:00","Shreveoport","John,Smith"
(Note the extra comma in the customer name)
They claim that they have no control over this data, and cannot strip it out.
My question is, how can I use CoulumnDelimiter field in the advanced tab of
the Flat File Connection Manager Editor to tell SSIS to ignore these extra
commas?
Thanks in advanced.
John Rossitter