We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Reading Delimited Text Files

hozempa asked
Medium Priority
Last Modified: 2010-04-30
Possible a difficult question so depending on depth of explanation, more point will be awarded.  Now the question.  I have some text delimited(comma's and double quotes) files so how do I read them without using the Input# function or how does Input# work?  Essentially, I have a situation where I cannot use Input# but because of differing record/line lengths but the variables are indentical up to the point where the shorter record ends.  So as I may have to use Line Input#, I was wonder how to parse a line that is comma delimited when a text variable/field may contain a comma.  If any of this explanation of the problem is unclear, let me know.

Thanks in advance, hozempa
Watch Question

One easy way to do, is reading the file in a string file, ad after process this string as you want.

I will add some sample code tomorrow
First, define the varabile "aa$" as a global array of any dimension :

Then, use the following function. Pass it your source string (in your case read via the Line Input# statement), and a string containing your delimiter (in your case ",".

The function will fill dynamically redimension aa$ as it finds more elements in your source string, and will return the number of array elements it placed in aa$.


Public Function ParseString(strSource As String, strDelim As String)
Dim intElementCnt As Integer
Dim intCurPos As Integer
Dim intStrLen As Integer

intElementCnt = 1
intCurPos = 1
intStrLen = Len(strSource)
    ReDim Preserve aa$(1 To intElementCnt)
    intStrLen = (InStr(intCurPos, strSource, strDelim) - intCurPos)
    If intStrLen < 0 Then
        aa$(intElementCnt) = Right$(strSource, (Len(strSource) - (intCurPos - 1)))
        aa$(intElementCnt) = Mid$(strSource, intCurPos, intStrLen)
        intCurPos = intCurPos + (Len(aa$(intElementCnt)) + Len(strDelim))
        intElementCnt = intElementCnt + 1
    End If
Loop Until intStrLen < 0
ParseString = UBound(aa$)

End Function


Looking at the code, it appears that it doesn't handles a text variable/field that contains a comma.  If you can provide a sample that does account for this senario then I will accept this answer, otherwise I will have to reject and open it to others.

Regards, hozempa

You want to parse the line or simply read it in as is?
I use this for parsing comma delimited strings ....

It handles ANY character that you specify as a delimeter, ditzo. If you can't learn to code, and you can't even use or READ code that is put in your lap, then I think you need another line of business!! ROFL


You're correct about the code vettrange. However, I accidentally stated comma in my comment when I meant double quotation marks.  And obviously, your code falls short of this doesn't it.  Considering that the original question stated the problem of text strings containing commas, I believe that you are the one who has the problem with writing code.  Your simple little comma parsing routine is inadequate for the task at hand so, in the future, please do not post an answer if you do not understand the question.  By the way, your code has errors also.



Acutally, there was nothing wrong with my rejection comment.  If you R-E-A-D it carefully, you may notice that it states your code does not handle a situation where a text field within the comma delimited string contains comma's.  Since this text field should necessary remain entact your code with separate it into more that one variable because it doesn't respect ,"a text field that should be counted as one variable, despite the number of commas it may contain".  By the way, you code is still buggy.
Unlock this solution and get a sample of our free trial.
(No credit card required)

Have a look at www.mygale.org/~jumbo/Exchange/ParseList.Txt

Sorry, I forgot StrCountStr which simply is:

Function StrCountStr(s As String, searchStr As String) As Long

Dim i As Long
Dim pos As Long

StrCountStr = 0
If searchStr = vbNullString Then
   Exit Function
End If

pos = InStr(s, searchStr)
Do While pos > 0
   StrCountStr = StrCountStr + 1
   pos = InStr(pos + Len(searchStr), s, searchStr)
End Function


Although I have already solved the problem and requested that this question be deleted, I will award the points to you even though I haven't had the chance to look it over.  Thanks for responding to the question.

regards, hozempa
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.