commas in column on CSV file

Question

Post reply

commas in column on CSV file

jyoti_bhatt

SSCommitted

Points: 1950
More actions
January 28, 2010 at 2:55 am

#138446

Hi,
I am struggling to extract data correctly from CSV file. The commas on a column is causing the data to extract incorrectly.
How can I get round this problem?
Please help.
Many Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Provide some sample data please.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 2

Thanks for your response.

I am trying to import data from Company Name column in CSV file that contains commas in the value.

E.g. Company name could be = Orange, Plc

When I try to import the above example it splits and treats it as two separate values?

Please help

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Obviously no program can reliably tell the different between an content comma and a field separating comma.

The csv needs to be exported correctly , either use fixed lengths or a text identifier ie "

Clear Sky SQL
My Blog[/url]

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Is there a delimiter (eg "") around company name? Or is it just

Field1, co, name, here, field2

Field1, another company name here, field3

?

If there is no field delimiter, you are snookered.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 5

Ok so for example I have the below column with values:

Company Name

Orange, Plc

Vodafone

O2, Plc

If I extract the above, Orange, Plc and O2, Plc values with commas will be treated as two separate values and Plc will move to the next cell/ column.

Do I need to have double quotes round my values like below:

Company Name

"Orange, Plc"

"Vodafone"

"O2, Plc"

Is that what u meant?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Yup , exactly that , be careful that you filter out an quotes within the data as you will get the same issue.

Clear Sky SQL
My Blog[/url]

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

Yes

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 8

If I put double quotes around my values. I'm get double double quotes when I review the data.

Eg: When I have value "Orange, Plc" when I preview this in flat file source I get ""Orange Plc"" in the next column?

What am I doing wrong?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

1) Have you set the "Text Qualifier" in the Connection manager to " ?

2) Does the data look ok when opened with notepad ?

Clear Sky SQL
My Blog[/url]

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 10

Hi,

Yes I did work out it was the text qualifier that I didn't set.

Thank you so much for all your help.

Much appreciated.

Many Thanks.