Parse A String Into Fields By Delimiter, Honoring Standard Quote Conventions

Sub ParseString(subInString As String, subDelimiter As String, subRetSegs() As
This subroutine will allow you to read a text file with LINEINPUT and parse out
the fields based on any delimiter character. The routine will place the fields
into a string array which must be defined in the calling routine. The routine
will honor standard quoting conventions including embedded quotes being
designated by a double-quote.

String, subNumOfSegs As Integer)

' This subroutine will take in a string (ususlly from a LINEINPUT) and
parse out the fields based upon an identified delimiter.
' the fields are returned in a String array.
' If a field is enclosed in quotes, then delimiters within the quotes are
treated as text NOT delimiters.
' If a field is enclosed in quotes and two or more contiguous quotes are
encountered then they are reduced to a single quote
' and included in the resultant field.
' If an "OpenQuote" condition exists and a single quote is found, it is
also assumed to be a delimiter.
'Inputs are: subInString > this is the string to be parsed.
' subDelimiter > this is a single character delimiter
'Outputs are: subRetSegs > This is a string array containing the
individual fields parsed out
' subNumOfSegs > This is an integer indicating the
number of fields which were parsed.
' = -100 Array overflow
' = -200 Delimiter not
single character
Dim CurrChar As String, PrevChar As String, NextChar As String, TempString
As String
Dim wlength As Integer, InQuote As Integer, CurrSub As Integer, CurrPos As
Dim StartPos As Integer, StringLength As Integer, MaxSub As Integer
' check length of delimiter
If (Len(subDelimiter) <> 1) Then
CurrSub = -200
Goto BlowThisParty
End If
subInString = subInString + subDelimiter + " " 'add a delimiter
to end of string so we don't need to worry about string

' remainder at end.
wlength = Len(subInString)
MaxSub = Ubound(subRetSegs) ' size of return array is defined in
calling routine
' Initialize
CurrSub = 1
StartPos = 1
StringLength = 0
CurrPos = 0
PrevChar = ""
CurrChar = ""
NextChar = Mid(subInString,1,1)

'Run thru the input string character by character
For CurrPos =1 To wlength
PrevChar = CurrChar
CurrChar = NextChar
NextChar = Mid(subInString,CurrPos+1,1)
If (CurrChar = subDelimiter) Then 'potential
new field
If (InQuote = 0) Then '
True means we are NOT currently inside of a quote
If StringLength = 0 Then ' If
StringLength had been previously set by "end-of-quote" condition, do not set
StringLength = CurrPos - StartPos
End If
If StringLength > 0 Then ' if
StringLength is > 0 then pull field otherwise set field to null
subRetSegs(CurrSub) =
subRetSegs(CurrSub) = ""
End If
'set up for next field
CurrSub = CurrSub + 1
StartPos = CurrPos + 1
StringLength = 0
If CurrSub > MaxSub Then 'Are we
at Array Overflow?
CurrSub = -100
Goto BlowThisParty
End If

End If
End If

'The "quote logic" is kinda confusing...
' If we get a quote and were not already with an InQuote condition, we set the
InQuote condition ON
' If we are already in an InQuote condition then...
' if the NEXT character is a Delimiter then we process an end-of-quote
' if the NEXT character is not a Delimiter and the PRIOR character was a quote, then
we delete the current
' character from the string and reprocess the current position which now has the next
character in it).
' If the PRIOR and NEXT characters are neither quote nor delimiter, treat quote as
endquote AND delimiter.
If (CurrChar=Chr(34))Then 'current character is a quote
If (InQuote=0) Then ' InQuote is FALSE
InQuote=1 'set quote flag on
StartPos = CurrPos + 1 'reset start to be inside of quotes
Else 'InQuote is TRUE
If (NextChar = subDelimiter) Then 'Next character is a delimiter (process end-of-
InQuote=0 'set quote flag off
StringLength = CurrPos - StartPos 'Set string length NOW inside of end-quote
Else 'Next character is NOT a delimiter
If (PrevChar = Chr(34)) Then ' If this is the second in a row, get rid of it. and re-
process position
TempString = Mid(subInstring,1,currpos-1) + Mid(subInstring,currpos+1,wlength)
subInString = TempString
wlength = Len(subInString)
CurrChar = PrevChar
If (NextChar <> Chr(34)) Then 'this is a single quote so MUST be the end of quote,
assume delimiter also
InQuote=0 'set quote flag off
CurrChar = subDelimiter
Goto ImpliedDelimiter
End If
End If
End If
End If
End If
If CurrSub > 0 Then ' Error contditions would have set CurrSub to negative
' Clean out rest of Array
subNumOfSegs = CurrSub - 1
For currpos = CurrSub To MaxSub
subRetSegs(CurrPos) = ""
subNumOfSegs = CurrSub
End If
End Sub

This was first published in November 2000

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.