Parsing CSV strings like "Doe, John",33, "6'2""" 
Author Message
 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"><p><font size=3D2 =
color=3D"#000000" face=3D"Arial">Hello fellow VBers!<br><br>I needed to =
parse strings containing a comma delimited variables (quotes around =
strings).<br><br>For example, to convert a string =
like:<br>&#009;&quot;Doe, John&quot;,33, =
&quot;6'2&quot;&quot;&quot;<br>into an array, such =
that:<br>&#009;Arr(0)=3D Doe, John<br>&#009;Arr(1)=3D =
33<br>&#009;Arr(2)=3D 6'2&quot;<br>&#009;<br><br>Ok, so I wrote a =
procedure that does this, and it works. &nbsp;But, the procedure is =
huge, slow and very ugly.<br><br>It's so ugly that I feel that I'm =
overlooking a simpler way to do this.<br><br>I am wondering if any other =
coders would care to suggest such a =

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



Tue, 04 Jan 2000 03:00:00 GMT  
 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  
 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  
 
 [ 3 post ] 

 Relevant Pages 

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

2. Origin of "John Doe"

3. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

4. GetObject("","InternetExplorer.Application") fails in Excel VBA

5. SysCmd 603, "path","path"

6. Disabling "BACK"/"FORWARD" buttons

7. Loop print "VARIABLE", "VARIABLE"

8. DLL or something like "#"#ยค#"!"#

9. Disabling "BACK"/"FORWARD" buttons

10. CreateObject("Excel","//server"), MsgBox output

11. Problem With "window.showmodaldialog("")"

12. CreateObject("Excel","//server"), MsgBox output

 

 
Powered by phpBB® Forum Software