Importing text file without end-of-line delimiter?

[ker_01]

In Excel, I recorded a macro while importing a text file (because that's the
easiest way I've found to capture the fixed-width values for my fields). It
imported just fine.

Now I'm using VBA to import that same text file a line at a time and parse
each line for data values. I was having trouble (after the first line, the
code would quit). I used a msgbox to display that first line prior to
parsing- and it showed me the whole file! (or as much as could fit in a
msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel knew
that there were multiple lines and put the data in multiple rows. However,
when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any suggestions?

Thank you,
Keith

 

[Rick Rothstein \(MVP - VB\)]

In the VB world, the end of line marker is a Carriage Return followed by a
Line Feed... if your lines are not delimited with this, Line Input does not
see your individual lines of text as individual lines. I'm guessing your
lines are delimited by simple Line Feeds. If this is the case, and if this
code will only be used for files generated in the same way as the one you
are currently working with, you could do away with the Do-Loop, read the
entire file into a variable and then Split the text into individual lines
using the Split function with vbLf, or Chr(10) if you prefer, as the
delimiter... this will produce a zero-based array that you can loop through
element by element (which will then be line-by-line) and do whatever you
need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if your
code could have to handle both Line Feed delimited text and text delimited
with Windows' normal line delimiter (a Carriage Return followed by a Line
Feed), then write back and I'll generalize the code for you.

Rick

 

[ker_01]

Rick-

Thank you for your response, explanation, and sample code. For this project
I will import several different files, but their sources and formats are so
different I'm making a separate sub for each. I'll adapt the snippet you've
provided, and post back if I have any problems or questions.

Thank you!!
Keith

 

[Dave Peterson]

Is it possible to open the file (using the equivalent of File|Open), then parse
the data from the worksheet?

If not, maybe you can open the file, convert it, save it as a new name, then
have your code open the converted file:

Option Explicit
Sub UpDateTxtFile()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

myInFileName = "C:\my documents\excel\test.txt"
myOutFileName = Environ("temp") & "\testout.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = vbLf
myContents = .Replace(myContents, vbCrLf)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

Then point at the temp folder's testout.txt.

You'll want to be nice, so clean up that file in the %temp% folder when you're
done with it.

 

[Rick Rothstein \(MVP - VB\)]

In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed as
from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage Feed
followed by a Line Feed which is an normal Windows type file). You pass it
the full path and filename for your file and it returns an array containing
each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick

 

[ker_01]

Rick-
This looks fabulous- Just to help me learn, I understand everything except
the use of the 'space' keyword:

TotalFile = Space(LOF(FileNum))

It appears that this is getting the total file length (length of string to
load in the next line), but I'm not clear what the 'space' keyword is doing
for the statement. Can you educate me with a brief explanation?

Thank you!
Keith

 

[Rick Rothstein \(MVP - VB\)]

Whenever you have a question about a keyword in VB, the first thing you
should do is click anywhere on the word and press F1 to get VB's help file
for that keyword. The Space function returns the number of spaces (character
with the ASCII value of 32) called for by the value you pass into it (in
this case, you are right, the length of the file).

Rick

 

[ker_01]

Rick-

I had looked up the word, I guess I didn't fully understand it's use in this
context; I would have thought that
LOF(FileNum)

would provide a numeric (Long) length of file, which I would have assumed
was sufficient to use in
Get #FileNum, , TotalFile

e.g., get from selected file (#FileNum) a total of n (TotalFile) characters

Space, from help:
"Returns a Variant (String) consisting of the specified number of spaces"

So I guess in my mind, my confusion stems from the fact that I'm translating
TotalFile = Space(LOF(FileNum))

as setting TotalFile equal to a very long empty string, rather than a
numeric value to use in
Get #FileNum, , TotalFile

Your version works just fine in my code (properly attributed with comments,
of course), but at the moment it's still code magic, since my brain is still
working on the logic above


Thanks,
Keith

 

[ker_01]

Ugh, please ignore my last post. I'm not sure why my brain was thinking that
the third GET parameter was length, when it is actually the destination
string.

Now my understanding of (this small bit of) technology has advanced enough
that it is no longer magic...


Thanks again,
Keith