J
Janelle
The Split function is very nice, but it does have limited
functionality. The strings I'm trying to split often have
text qualifiers in them. I've tried searching previous
posts in both Excel and Access and have found several
alternatives, but they all break if the qualifier is also
part of the text. Let me give an example.
I create a .csv file in notepad that contains the
following:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8, 9,
and 10 inches",11 inches
If I then open that csv file in Excel, everything is very
nicely split up into cells A1 through G1:
1 inch
2"
3 inches
4, 5, and 6"
7 inches
8, 9, and 10 inches
11 inches
Now let's say I write a code to programmatically read the
csv file into vba using:
Open strInfFile For Input Access Read As intFileNum
Line Input #intFileNum, strInputString
debug.print strInputString
gives the following result:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8, 9,
and 10 inches",11 inches
What I'd like to do is take that string and break it up
into a one-dimensional array with the same contents as the
cells in Excel.
But I keep getting things like:
1 inch
"2"""
"3 inches"
"4
5
and 6"""
7 inches
"8
9
and 10 inches"
11 inches
or
1 inch
"2"""
"3 inches"
"4, 5, and 6"""
7 inches
"8, 9, and 10 inches"
11 inches
or
1 inch
3 inches
7 inches
8, 9, and 10 inches
11 inches
Help!!! Isn't there a decent csv string parser anywhere?
I'm using Excel 2000.
I've thought about writing something myself, but I'm
afraid I can't figure out how to handle the text
qualifiers either.
functionality. The strings I'm trying to split often have
text qualifiers in them. I've tried searching previous
posts in both Excel and Access and have found several
alternatives, but they all break if the qualifier is also
part of the text. Let me give an example.
I create a .csv file in notepad that contains the
following:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8, 9,
and 10 inches",11 inches
If I then open that csv file in Excel, everything is very
nicely split up into cells A1 through G1:
1 inch
2"
3 inches
4, 5, and 6"
7 inches
8, 9, and 10 inches
11 inches
Now let's say I write a code to programmatically read the
csv file into vba using:
Open strInfFile For Input Access Read As intFileNum
Line Input #intFileNum, strInputString
debug.print strInputString
gives the following result:
1 inch,"2""","3 inches","4, 5, and 6""",7 inches,"8, 9,
and 10 inches",11 inches
What I'd like to do is take that string and break it up
into a one-dimensional array with the same contents as the
cells in Excel.
But I keep getting things like:
1 inch
"2"""
"3 inches"
"4
5
and 6"""
7 inches
"8
9
and 10 inches"
11 inches
or
1 inch
"2"""
"3 inches"
"4, 5, and 6"""
7 inches
"8, 9, and 10 inches"
11 inches
or
1 inch
3 inches
7 inches
8, 9, and 10 inches
11 inches
Help!!! Isn't there a decent csv string parser anywhere?
I'm using Excel 2000.
I've thought about writing something myself, but I'm
afraid I can't figure out how to handle the text
qualifiers either.