Extracting data with delimiters

  • Thread starter Thread starter Tippy
  • Start date Start date
T

Tippy

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 i
into a 3 element array. Although inefficient, I created three separat
loops for each column for clarity and since the last column may contai
colons. This is what I have so far, and it seems to fail in the firs
loop and I don't know why. Any help would be appreciated. Attached i
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) O
(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=46446
 
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,

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
 
If you're running xl2k or higher, you could use the Split command to separate
your string into an array.

Option Explicit

Sub grabSTA()
' Gets filename from cell E1 as uses it as input #1
Dim strFILENAME As String
Dim textColonArray As Variant
Dim WholeLine As String
Dim iCtr As Long

strFILENAME = Sheet1.Range("E1").Value
Open strFILENAME For Input As #1

While Not EOF(1)
Line Input #1, WholeLine
textColonArray = Split(WholeLine, ":")
'''you'll still need to trim the values if you want
For iCtr = LBound(textColonArray) To UBound(textColonArray)
textColonArray(iCtr) = Trim(textColonArray)
Next iCtr
'do what you want...

Wend
Close #1

End Sub
 
Dave,

Your code is overlooking the fact that not all ":" in the text file are
delimiters, some are part of the actual field text (time separators).

Nikos
 
Oops. Missed that.

If it's only the last "column" that could have colons, you could use split and
then recombine that last one:

Option Explicit

Sub grabSTA()
' Gets filename from cell E1 as uses it as input #1
Dim strFILENAME As String
Dim textColonArray As Variant
Dim WholeLine As String
Dim iCtr As Long

strFILENAME = Sheet1.Range("E1").Value
Open strFILENAME For Input As #1

While Not EOF(1)
Line Input #1, WholeLine
textColonArray = Split(WholeLine, ":")
'''you'll still need to trim the values if you want

If UBound(textColonArray) > 2 Then '3 or more elements
For iCtr = 3 To UBound(textColonArray)
textColonArray(2) = textColonArray(2) & ":" & textColonArray(iCtr)
Next iCtr
ReDim Preserve textColonArray(0 To 2)
End If

For iCtr = LBound(textColonArray) To UBound(textColonArray)
textColonArray(iCtr) = Trim(textColonArray(iCtr))
Next iCtr
'do what you want...

Wend
Close #1

End Sub

(I also missed an (ictr) in that last trim() statement in the other post.)



Nikos said:
Dave,

Your code is overlooking the fact that not all ":" in the text file are
delimiters, some are part of the actual field text (time separators).

Nikos
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to write to 'Categories' field using VBA 2
Loop Repeats - PLS HELP 2
Code copies between key word is in reverse order 5
Extract Access records to new workbook using VBA in Excel 6
vba code help 2
Passing references 1
Read the next line in a text file 12
Save As and new workbook new name used in existing code 8

Back
Top