Splitting a data row with mixed COMMA and QUOTE COMMA elements

[Ed]

My csv database contains lines of data that look like this
"Anderson, David",23987,ANDERSON,7,Individual,1/2/2003,"Anderson,
Mary",23347
"Jones, Robert",23988,JONES,13,Joint,4/23/2006,"Jones, Angela",23988
etc.

I need to break each line up into separate pieces, and would ordinarily read
each line into a variable (let's say called "client") and use the vba
command ''split" to create an array called nextclient, thusly:
nextclient=split(client, ",")

However, as you can see, the some of the elements are QUOTE COMMA separated,
and that messes everything up when the split on just on a comma.

Is there a way that can I get this data properly 'split up'? (I do want the
text within quotes to stay together, with the comma.)

Tx.

Ed (in Virginia)

 

[macropod]

Hi Ed,

Have you tried opening the file in Excel? From there, you could save the file as a tab-delimited text file.

 

[Ed]

Well, I frequently work with other peoples data sent in csv files. It is a
real pain to have to convert them, massage them and then convert them back
to what the original author sent.

Besides, what on earth as Microsoft thinking in allowing us to create .csv
files and then not allowing us to efficiently use them in VBA?

The mixing of COMMA and QUOTE COMMA QUOTE is a real problem. If I were to
adopt your solution, but wanted to keep csv, is there a way to tell Excel
that if one field is QUOTE COMMA QUOTE separated, they all should be? Then
at least I can 'split' on a QUOTE COMMA QUOTE.

Tx.

Ed (in Virginia)

Hi Ed,

Have you tried opening the file in Excel? From there, you could save the
file as a tab-delimited text file.

--
Cheers
macropod
[Microsoft MVP - Word]

My csv database contains lines of data that look like this
"Anderson, David",23987,ANDERSON,7,Individual,1/2/2003,"Anderson,
Mary",23347
"Jones, Robert",23988,JONES,13,Joint,4/23/2006,"Jones, Angela",23988
etc.

I need to break each line up into separate pieces, and would ordinarily
read each line into a variable (let's say called "client") and use the
vba command ''split" to create an array called nextclient, thusly:
nextclient=split(client, ",")

However, as you can see, the some of the elements are QUOTE COMMA
separated, and that messes everything up when the split on just on a
comma.

Is there a way that can I get this data properly 'split up'? (I do want
the text within quotes to stay together, with the comma.)

Tx.

Ed (in Virginia)

 

[macropod]

Hi Ed,

The issue isn't one of whether vba allows you to efficiently parse csv files: it does. Your problem is in expecting the split
function to do it for you without taking account of the csv conventions for handling strings containing commas. One approach would
be to use Word to initiate an Excel session, use Excel to parse the file, then return whatever data you want. You could even modify
the data and have Excel update the file for you.

If all you need is a way to read the file, check out:
http://www.xbeat.net/vbspeed/c_ParseCSV.php
and especially Mike Peterson's post.

--
Cheers
macropod
[Microsoft MVP - Word]

Well, I frequently work with other peoples data sent in csv files. It is a real pain to have to convert them, massage them and
then convert them back to what the original author sent.

Besides, what on earth as Microsoft thinking in allowing us to create .csv files and then not allowing us to efficiently use them
in VBA?

The mixing of COMMA and QUOTE COMMA QUOTE is a real problem. If I were to adopt your solution, but wanted to keep csv, is there a
way to tell Excel that if one field is QUOTE COMMA QUOTE separated, they all should be? Then at least I can 'split' on a QUOTE
COMMA QUOTE.

Tx.

Ed (in Virginia)

Hi Ed,

Have you tried opening the file in Excel? From there, you could save the file as a tab-delimited text file.

--
Cheers
macropod
[Microsoft MVP - Word]

My csv database contains lines of data that look like this
"Anderson, David",23987,ANDERSON,7,Individual,1/2/2003,"Anderson, Mary",23347
"Jones, Robert",23988,JONES,13,Joint,4/23/2006,"Jones, Angela",23988
etc.

I need to break each line up into separate pieces, and would ordinarily read each line into a variable (let's say called
"client") and use the vba command ''split" to create an array called nextclient, thusly:
nextclient=split(client, ",")

However, as you can see, the some of the elements are QUOTE COMMA separated, and that messes everything up when the split on
just on a comma.

Is there a way that can I get this data properly 'split up'? (I do want the text within quotes to stay together, with the
comma.)

Tx.

Ed (in Virginia)

 

[Ed]

Hmmm. That sounds like a good plan. I'll give that a shot. Thanks.

Ed

Hi Ed,

The issue isn't one of whether vba allows you to efficiently parse csv
files: it does. Your problem is in expecting the split function to do it
for you without taking account of the csv conventions for handling strings
containing commas. One approach would be to use Word to initiate an Excel
session, use Excel to parse the file, then return whatever data you want.
You could even modify the data and have Excel update the file for you.

If all you need is a way to read the file, check out:
http://www.xbeat.net/vbspeed/c_ParseCSV.php
and especially Mike Peterson's post.

--
Cheers
macropod
[Microsoft MVP - Word]

Well, I frequently work with other peoples data sent in csv files. It is
a real pain to have to convert them, massage them and then convert them
back to what the original author sent.

Besides, what on earth as Microsoft thinking in allowing us to create
.csv files and then not allowing us to efficiently use them in VBA?

The mixing of COMMA and QUOTE COMMA QUOTE is a real problem. If I were to
adopt your solution, but wanted to keep csv, is there a way to tell Excel
that if one field is QUOTE COMMA QUOTE separated, they all should be?
Then at least I can 'split' on a QUOTE COMMA QUOTE.

Tx.

Ed (in Virginia)

Hi Ed,

Have you tried opening the file in Excel? From there, you could save the
file as a tab-delimited text file.

--
Cheers
macropod
[Microsoft MVP - Word]


My csv database contains lines of data that look like this
"Anderson, David",23987,ANDERSON,7,Individual,1/2/2003,"Anderson,
Mary",23347
"Jones, Robert",23988,JONES,13,Joint,4/23/2006,"Jones, Angela",23988
etc.

I need to break each line up into separate pieces, and would ordinarily
read each line into a variable (let's say called "client") and use the
vba command ''split" to create an array called nextclient, thusly:
nextclient=split(client, ",")

However, as you can see, the some of the elements are QUOTE COMMA
separated, and that messes everything up when the split on just on a
comma.

Is there a way that can I get this data properly 'split up'? (I do want
the text within quotes to stay together, with the comma.)

Tx.

Ed (in Virginia)

 

[Ed]

For anyone following this thread, here is the solution. Works nicely, and
very fast.
http://www.devx.com/vb2themax/Tip/19083

Thanks to the programmer of this: Francesco Balena

-Ed (in Virginia)