ExcelBanter - Extracting data with delimiters

Tippy,

The approach is indeed inefficient, so instead of bothering to find the
error, I took a different one from scratch. Basically what it does is it
reads a whole line and locates the separators, then breaks it up based on
the separator positions:

Sub grabSTA()
Dim strFILENAME As String
Dim textColonArray(2) As String

Sheet1.Range("E3").Select
strFILENAME = Sheet1.Range("E1").Value
Open strFILENAME For Input As #1
Line Input #1, lin
sep1 = InStr(1, lin, ":")
sep2 = InStr(sep1 + 1, lin, ":")
textColonArray(0) = Trim(Left(lin, sep1 - 1))
textColonArray(1) = Trim(Mid(lin, sep1 + 1, sep2 - sep1 - 1))
textColonArray(2) = Trim(Right(lin, Len(lin) - sep2))
For i = 0 To 2
ActiveCell.Offset(i, 0) = textColonArray(i)
Next
Close #1
End Sub

This approach can easily be modified to import all data lines (rejecting the
dashes), and put in three columns, much like the original file:

Sub garbSTA_All_Lines()
Dim strFILENAME As String
Dim textColonArray(2) As String

Sheet1.Range("E3").Select
strFILENAME = Sheet1.Range("E1").Value
Open strFILENAME For Input As #1
Sheet1.Range("E3").Select
linecount = 0
Do Until EOF(1)
Line Input #1, lin
If Left(lin, 3) < "---" Then
Debug.Print lin
sep1 = InStr(1, lin, ":")
sep2 = InStr(sep1 + 1, lin, ":")
textColonArray(0) = Trim(Left(lin, sep1 - 1))
textColonArray(1) = Trim(Mid(lin, sep1 + 1, sep2 - sep1 - 1))
textColonArray(2) = Trim(Right(lin, Len(lin) - sep2))
For i = 0 To 2
ActiveCell.Offset(linecount, i) = textColonArray(i)
Next
linecount = linecount + 1
End If
Loop
Close #1
End Sub

Note: with the separator locator inside the loop, it can cope with separator
positions varying from line to line. If you are certain they are constant,
this part can be pulled before the loop and thus executed only once,
marginally improving execution performance.

HTH,
Nikos

"Tippy " wrote in message
...
I would like to extract data from a text file delimited by semi colons.
The text file looks like:
XXX : XXX : XXX
XXX : XXX : XXX
XXX : XXX : XXX
XXX : XXX : XXX

So far I would just like to take the only the first line and put it
into a 3 element array. Although inefficient, I created three separate
loops for each column for clarity and since the last column may contain
colons. This is what I have so far, and it seems to fail in the first
loop and I don't know why. Any help would be appreciated. Attached is
the text file I am using.

Sub grabSTA()
' Gets filename from cell E1 as uses it as input #1
Dim strFILENAME As String
Dim textColonArray(3) As String
strFILENAME = Sheet1.Range("E1").Value
Open strFILENAME For Input As #1

Dim currChar, tmpTxt As String

currChar = Input(1, #1)

' First Block
tmpTxt = ""
Do
tmpTxt = tmpTxt & currChar

currChar = Input(1, #1)
Loop While (currChar < ":")
textColonArray(0) = tmpTxt
Sheet1.Range("E3").Value = Trim(textColonArray(0))

' Second Block
tmpTxt = ""
Do
tmpTxt = tmpTxt & currChar
currChar = Input(1, #1)
Loop While (currChar < ":")
textColonArray(1) = tmpTxt
Sheet1.Range("E4").Value = Trim(textColonArray(1))

' Third Block
tmpTxt = ""
Do
tmpTxt = tmpTxt & currChar
currChar = Input(1, #1)
Loop While ((Asc(currChar) < 10) Or (Asc(currChar) < 13) Or
(Not EOF(1)))
textColonArray(2) = tmpTxt
'Sheet1.Range("E5").Value = Trim(textColonArray(2))

Close #1

End Sub

:)

Attachment filename: short_sch_zny.txt
Download attachment:
http://www.excelforum.com/attachment.php?postid=464469
---
Message posted from http://www.ExcelForum.com/