Split(row, ",") when some values contain comma

[Nicolae Fieraru]

Hi All,

I have a few CSV files and their content is like this:

"1","ABC","DEF,GHI","FGH"

You can notice that a cell contains the value "DEF,GHI" with a comma

I would like to split this row into values. If I use Split(row,",") then the
above value will be splited into "DEF" and "GHI" and I don't want this. How
can I split the row into correct values?

Regards,
Nicolae

[Manohar Kamath [MVP]]

You could split on "," instead of just ,

Split(row, Chr(34) & "," & Chr(34))

Then handle the leading " for the first array element, and trailing " for
the last array element.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Nicolae Fieraru" <nospam@please.com> wrote in message
news:%23zMRL8ncDHA.616@TK2MSFTNGP11.phx.gbl...

> Hi All,
>
> I have a few CSV files and their content is like this:
>
> "1","ABC","DEF,GHI","FGH"
>
> You can notice that a cell contains the value "DEF,GHI" with a comma
>
> I would like to split this row into values. If I use Split(row,",") then

the

> above value will be splited into "DEF" and "GHI" and I don't want this.

How

> can I split the row into correct values?
>
> Regards,
> Nicolae
>
>
>
>

[Nicolae Fieraru]

Hi Manohar,

Thank you very much for your tip, I already found an earlier message of
yours and it helped me.

Regards,
Nicolae


"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:#gb5IgocDHA.2672@tk2msftngp13.phx.gbl...

> You could split on "," instead of just ,
>
> Split(row, Chr(34) & "," & Chr(34))
>
> Then handle the leading " for the first array element, and trailing " for
> the last array element.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Nicolae Fieraru" <nospam@please.com> wrote in message
> news:%23zMRL8ncDHA.616@TK2MSFTNGP11.phx.gbl...

> > Hi All,
> >
> > I have a few CSV files and their content is like this:
> >
> > "1","ABC","DEF,GHI","FGH"
> >
> > You can notice that a cell contains the value "DEF,GHI" with a comma
> >
> > I would like to split this row into values. If I use Split(row,",") then

> the

> > above value will be splited into "DEF" and "GHI" and I don't want this.

> How

> > can I split the row into correct values?
> >
> > Regards,
> > Nicolae
> >
> >
> >
> >

>
>

[Ken Schaefer]

What if it's not "several values" but a piece of text that happens to
contain a comma?

Cheers
Ken


"Harag" <harag@softhome.net> wrote in message
news:4judlvg0vi0ti6qcsqhfk7u44m1qt6nki4@4ax.com...
: what I normally do when I store several values in one field is to use
: a PIPE character "|"
:
: eg:
: "1","ABC","DEF|GHI","FGH"
:
: HTH
: Al.
:
: On Thu, 4 Sep 2003 10:55:08 +1000, "Nicolae Fieraru"
: <nospam@please.com> wrote:
:
: >Hi All,
: >
: >I have a few CSV files and their content is like this:
: >
: >"1","ABC","DEF,GHI","FGH"
: >
: >You can notice that a cell contains the value "DEF,GHI" with a comma
: >
: >I would like to split this row into values. If I use Split(row,",") then
the
: >above value will be splited into "DEF" and "GHI" and I don't want this.
How
: >can I split the row into correct values?
: >
: >Regards,
: >Nicolae
: >
: >
: >
:

[Don Verhagen]

In news:jikelvgl2pte5d3a88ctfkrvm8k1lhv4v3@4ax.com,
Harag <harag@softhome.net> typed:
: Yeah thats the problem. It does really depend what the developer is
: going to store. I was just giving them something else to think about
: :) since the "|" (pipe) character is not used when typing normal text.

However the *standard* comma separate values (CSV) file allows quoted text
to contain commas (the delimiter). So unless you're going to change all
those applications (including Excel, etc.) the OP will have to deal with it.

However, I understand your point also, when I have a choice I use tabs to
separate field values. It wasn't a criticism, just pointing out the fact
that the OP may not have control of the application producting the CSV file
and the CSV is in a standard format.

Don Verhagen


: It was kind of you to point this out as I did forget to mention it
: earlier :)
:
: thanks.
: Al
:
: On Thu, 4 Sep 2003 20:49:19 +1000, "Ken Schaefer"
: <kenREMOVE@THISadOpenStatic.com> wrote:
:
:: What if it's not "several values" but a piece of text that happens to
:: contain a comma?
::
:: Cheers
:: Ken
::
::
:: "Harag" <harag@softhome.net> wrote in message
:: news:4judlvg0vi0ti6qcsqhfk7u44m1qt6nki4@4ax.com...
::: what I normally do when I store several values in one field is to
::: use
::: a PIPE character "|"
:::
::: eg:
::: "1","ABC","DEF|GHI","FGH"
:::
::: HTH
::: Al.
:::
::: On Thu, 4 Sep 2003 10:55:08 +1000, "Nicolae Fieraru"
::: <nospam@please.com> wrote:
:::
:::: Hi All,
::::
:::: I have a few CSV files and their content is like this:
::::
:::: "1","ABC","DEF,GHI","FGH"
::::
:::: You can notice that a cell contains the value "DEF,GHI" with a
:::: comma
::::
:::: I would like to split this row into values. If I use
:::: Split(row,",") then the above value will be splited into "DEF" and
:::: "GHI" and I don't want this. How can I split the row into correct
:::: values?
::::
:::: Regards,
:::: Nicolae