VB Syntax, Parsing CSV strings like "Doe, John",33, "6'2"""

Dan
#1 / 3

Parsing CSV strings like "Doe, John",33, "6'2"""

This is a multi-part message in MIME format.

------=_NextPart_000_01BC9377.FD223500
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hello fellow VBers!

I needed to parse strings containing a comma delimited variables (quotes
around strings).

For example, to convert a string like:
"Doe, John",33, "6'2"""
into an array, such that:
Arr(0)= Doe, John
Arr(1)= 33
Arr(2)= 6'2"

Ok, so I wrote a procedure that does this, and it works. But, the
procedure is huge, slow and very ugly.

It's so ugly that I feel that I'm overlooking a simpler way to do this.

I am wondering if any other coders would care to suggest such a technique?

------=_NextPart_000_01BC9377.FD223500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFFF">Hello fellow VBers! I needed to =
parse strings containing a comma delimited variables (quotes around =
strings). For example, to convert a string =
like: 	"Doe, John",33, =
"6'2""" into an array, such =
that: 	Arr(0)=3D Doe, John 	Arr(1)=3D =
33 	Arr(2)=3D 6'2" 	 Ok, so I wrote a =
procedure that does this, and it works.  But, the procedure is =
huge, slow and very ugly. It's so ugly that I feel that I'm =
overlooking a simpler way to do this. I am wondering if any other =
coders would care to suggest such a =

</body></html>
------=_NextPart_000_01BC9377.FD223500--

Tue, 04 Jan 2000 03:00:00 GMT

Robert Whetze
#2 / 3

Parsing CSV strings like "Doe, John",33, "6'2"""

Hi Dan,

Here is some code that I use. It is easily adaptable. Not sure if it is
the most efficient way but it works great.

***Note - I already knew the number of fields in my line. You should use
ReDim if lines vary.

Option Explicit

Dim OrderField(39) 'Known number of fields in line
Dim FieldCounter As Integer 'Counter for the array
Dim CurPos As Long 'Cursor position
Dim CurChar As String 'Current character
Dim txtStr As String 'String data
Dim NextQuote As Integer 'Next quote to determine string fields

Const StrQualifier = """" 'String qualifier
Const Delimiter = "," 'Delimiter

Function ParseOrderString(ByVal SrcStr As String) As Integer

txtStr = "" ' Used to build field data
FieldCounter = 1 ' Started my array at 1 to make some
later code easier
CurPos = 1 ' Start at the begining of the line
CurChar = Mid(SrcStr, CurPos, 1) ' Start at the first character

'Loop until the end of the comma delimited line
Do Until CurChar = ""
CurChar = Mid(SrcStr, CurPos, 1)

'Check to see if the current character is a quote
If CurChar = StrQualifier Then

'If current character is a quote find the position of the next quote
NextQuote = InStr(CurPos + 1, SrcStr, StrQualifier, 1)

'If the next character after the quote is a comma we have found
'the end of the field. Get the entire field
If Mid(SrcStr, NextQuote + 1, 1) = Delimiter Then
txtStr = Mid(SrcStr, CurPos + 1, NextQuote - CurPos - 1)
CurPos = NextQuote + 1
OrderField(FieldCounter) = txtStr

'Increase the array counter
FieldCounter = FieldCounter + 1

'Set the txtStr to an empty string
txtStr = ""
Else
End If
Else

'We have hit the comma, store the data in the OrderField array
If CurChar = Delimiter Then

'Store the data in txtStr in the array
OrderField(FieldCounter) = txtStr

'Increase the array counter
FieldCounter = FieldCounter + 1

'Set the txtStr to an empty string
txtStr = ""
Else

'Build the txtStr string until we hit the next comma
txtStr = txtStr & CurChar
End If
End If

'Move the cursor to the next position
CurPos = CurPos + 1
Loop

End Function

Hope this helps!

Robert Whetzel

***************

Hello fellow VBers!

I needed to parse strings containing a comma delimited variables (quotes
around strings).

For example, to convert a string like:
"Doe, John",33, "6'2"""
into an array, such that:
Arr(0)= Doe, John
Arr(1)= 33
Arr(2)= 6'2"

Ok, so I wrote a procedure that does this, and it works. But, the
procedure is huge, slow and very ugly.

It's so ugly that I feel that I'm overlooking a simpler way to do this.

I am wondering if any other coders would care to suggest such a technique?

Tue, 04 Jan 2000 03:00:00 GMT

Joe LeVasseu
#3 / 3

Parsing CSV strings like "Doe, John",33, "6'2"""

Dan-
Here's a pretty simple way:

(1) Make a text file with the following contents
and save it as c:\array.txt
________snip__________________________

"Doe, John",33, "6'2"
"Doe, Jane",30, "5'2"
"Doe, Joe",13, "4'2"

_________snip___________________

(2) Put two Command buttons on a form and
insert this code:

________snip___________________________________
Option Explicit

Dim arr() As Variant ' That way you can pull in numbers or strings
Private Sub Command1_Click()
Dim i%, iFreeFile%
Const szFileName As String = "c:\array.txt"
ReDim arr(3)
On Error GoTo BadFile
iFreeFile = FreeFile
Open szFileName For Input As FreeFile
Do While Not EOF(1)
Input #iFreeFile, arr(i), arr(i + 1), arr(i + 2)
i = i + 3
ReDim Preserve arr(i + 3)
Loop
Close iFreeFile
Command2.Enabled = True
Exit Sub
BadFile:
MsgBox "Can't find c:\array.txt"
Close #iFreeFile
End Sub

Private Sub Command2_Click()
Dim i%
For i = 0 To (UBound(arr()))
Print arr(i)
Next
End Sub

Private Sub Form_Load()
Command1.Caption = "Load Array"
Command2.Caption = "Show array"
Command2.Enabled = False
End Sub
______snip_______________________

Hey, it works,
Joe
--
***********************************************************

http://www.tiac.net/users/lvasseur/index.html
Microsoft MVP- Visual Basic
"Recompense injury with justice, and recompense
kindness with kindness." Confucius
**********************************************************

Hello fellow VBers!

I needed to parse strings containing a comma delimited variables (quotes
around strings).

For example, to convert a string like:
"Doe, John",33, "6'2"""
into an array, such that:
Arr(0)= Doe, John
Arr(1)= 33
Arr(2)= 6'2"

Ok, so I wrote a procedure that does this, and it works. But, the
procedure is huge, slow and very ugly.

It's so ugly that I feel that I'm overlooking a simpler way to do this.

I am wondering if any other coders would care to suggest such a technique?

----------

Wed, 05 Jan 2000 03:00:00 GMT