import part of a text file

[L Mehl]

Another application creates text files which I import into a worksheet for
further processing.

The text file looks like:
ARRAY 1 3023 <--3023 indicates how many X-values are in the file
#X-values, ...some more misc text ...
0.378228
0.737527
1.113739
1.488899
1.866231
2.257432
.... more X-values
<--one space in this line
#Y-values
0.195559
0.152420
0.126161
0.199365
0.173638

.... more Y-values

I need only the first 2 rows plus all the X-values (up to but not including
the line containing one space.

There could be 65,500+ X-values, and line-by-line processing is slow with
many values to import.

Is there a way in VBA to import a specified number of lines, without using a
line-by-line method?

expression.OpenText(...) has a StartRow parameter, but no "EndRow" as far as
I can tell.

Does anyone know of a text-file parsing program or a method which could be
used to write a program which could be called by VBA, to create a new file
after stripping the rows below and including the line containing one space?

Thanks for any help.

Larry Mehl

 

[Bernie Deitrick]

Larry,

In a nutshell: you are limited to line by line, but it shouldn't be _that_
slow.

If you have control over the output of the other application, have it write
a tab-delimited text file of no more than 65, 536 lines in the format

XVal1 tab YVal1 CRLF
XVal2 tab YVal2 CRLF
XVal3 tab YVal3 CRLF
......

and Excel will be able to simply open the file in one shot.

HTH,
Bernie
MS Excel MVP

 

[L Mehl]

Bernie --

Thanks for the reply. Programmer reluctant to change the other main app
because writing separate files for X and Y, giving the X to me and
concatenating the X and Y for the main app would slow it down.

I will test the method of importing the whole file into a temp worksheet and
stripping the Y values. That method limits me to 65,536/2 however.

Larry

 

[shockley]

Larry,

Here's a way to do it using FileSystemObject. I am calling the source file
"TestSource.txt" and I have 4 extra text files in the same folder called
"test01.txt", "test02.txt", "test03.txt", and "test04.txt". Make as many of
these as you need to split the original source file into separate files of
max 65536 lines. There may be some minor bugs and some special cases where
it doesn't work exactly right, but the general idea is there and you should
be able to modify it to your needs. You may have to add a reference for
Microsoft Scripting Runtime. You can get help on the FileSystemObject and
related properties and methods here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/ht
ml/vaobjfilesystemobject.asp

HTH,
Shockley


Public Const SaveDir As String = "C:\Documents and
Settings\shockley\Desktop\"
Sub Macro1()
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSourceFile = fso.GetFile(SaveDir & "testSource.txt")
Set tsRead = oSourceFile.OpenAsTextStream(ForReading,
TristateUseDefault)

Do
x = x + 1
Set oWriteFile = fso.GetFile(SaveDir & "test0" & x & ".txt")
Set tsWrite = oWriteFile.OpenAsTextStream(ForWriting,
TristateUseDefault)
LineCount = 0

Do
sTest = tsRead.ReadLine
tsWrite.WriteLine sTest
LineCount = LineCount + 1
If tsRead.AtEndOfStream Then Exit Do
Loop Until LineCount = 65536

Workbooks.OpenText _
FileName:=SaveDir & "test0" & x & ".txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

Set wbk = ActiveWorkbook
With wbk.Sheets(1)
Set rngEnd = .Cells(1, 1).End(xlDown)
If Not rngEnd Is Nothing Then
iRow = rngEnd.Row - 1
Set rngSource = Range(.Cells(1, 1), .Cells(iRow, 1))
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, x), .Cells(iRow, x))
End With
rngDest.Value = rngSource.Value
End If
End With
wbk.Close SaveChanges:=False
Loop Until LineCount < 65536
rngDest.Cells(iRow + 1) = " "
End Sub

 

[shockley]

PS,

Change the line

iRow = rngEnd.Row - 1

to

iRow = rngEnd.Row

I forgot to add that the macro handles files with more than 65536 lines by
listing the extra data in as many adjacent columns as are needed.

Shockley

 

[shockley]

I've revised the macro slightly so you only need one temporary text file
instead of the series of files. I call it "testWrite.txt". Create this as a
blank text file before you run the macro the first time.


Public Const SaveDir As String = "C:\Documents and
Settings\shockley\Desktop\"
Sub Macro1()
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSourceFile = fso.GetFile(SaveDir & "testSource.txt")
Set tsRead = oSourceFile.OpenAsTextStream(ForReading,
TristateUseDefault)

Do
x = x + 1
Set oWriteFile = fso.GetFile(SaveDir & "testWrite.txt")
Set tsWrite = oWriteFile.OpenAsTextStream(ForWriting,
TristateUseDefault)
LineCount = 0

Do
sTest = tsRead.ReadLine
tsWrite.WriteLine sTest
LineCount = LineCount + 1
If tsRead.AtEndOfStream Then Exit Do
Loop Until LineCount = 65536

Workbooks.OpenText _
FileName:=SaveDir & "testWrite.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

Set wbk = ActiveWorkbook
With wbk.Sheets(1)
Set rngEnd = .Cells(1, 1).End(xlDown)
If Not rngEnd Is Nothing Then
iRow = rngEnd.Row
Set rngSource = Range(.Cells(1, 1), .Cells(iRow, 1))
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, x), .Cells(iRow, x))
End With
rngDest.Value = rngSource.Value
End If
End With
wbk.Close SaveChanges:=False
tsWrite.Close
Set tsWrite = Nothing
Loop Until LineCount < 65536
rngDest.Cells(iRow + 1) = " "
End Sub

 

[L Mehl]

Shockley --

Thank you for the code. I appreciate all that effort.

Larry

 

[shockley]

Larry,

You're quite welcome. I'm naturally a bit curious if it helps you at all--I
wasn't sure if it would run fast enough.

For a source file with 65534 x-values, my macro, on my machine runs 7
seconds.

To simply open the file with Excel and copy into another workbook, as with
this code:

Sub Macro2()
BeginTime = Now
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSourceFile = fso.GetFile(SaveDir & "testSource.txt")
Set tsRead = oSourceFile.OpenAsTextStream(ForReading,
TristateUseDefault)

Do
sTest = tsRead.ReadLine
LineCount = LineCount + 1
Cells(LineCount, 1) = sTest
If tsRead.AtEndOfStream Then Exit Do
Loop Until LineCount = 65536

Runtime = (Now - BeginTime) * 86400
ThisWorkbook.Sheets(1).Cells(1, 5) = Runtime
End Sub

it takes 3 seconds. But this code has the line count limitation.

To read the text file line by line and place each x-value in Excel, as with
this code:

Sub Tester()
BeginTime = Now
Workbooks.OpenText _
FileName:=SaveDir & "testWrite.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

Set wbk = ActiveWorkbook
With wbk.Sheets(1)
Set rngEnd = .Cells(1, 1).End(xlDown)
If Not rngEnd Is Nothing Then
iRow = rngEnd.Row
Set rngSource = Range(.Cells(1, 1), .Cells(iRow, 1))
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, 1), .Cells(iRow, 1))
End With
rngDest.Value = rngSource.Value
End If
End With
wbk.Close SaveChanges:=False
Runtime = (Now - BeginTime) * 86400
Cells(1, 5) = Runtime
End Sub

takes 23 seconds. So, my code is half as fast as the simplest,
line-count-limited method, but about 3 times as fast as the method you may
have tried.

Regards,
Shockley

 

[shockley]

Oops! In the preceeding message, "Macro2" is the one that copies values
line by line and runs in 23 seconds. "Tester" opens the text file with Excel
and copies into another workbook and runs in 3 seconds.

Shockley

 

[L Mehl]

Shockley --

I will probably use it after I write some code to strip the Y-values before
the import, to save perhaps more time.

Larry

 

[shockley]

Larry,

I will probably use it after I write some code to strip the Y-values

before the import<

Replace the line

If tsRead.AtEndOfStream Then Exit Do

with this line

If sTest = " " Then Exit Do

This will stop the parsing at the line with the single space--no need to
strip the y-values. I was testing the macro on a souce file with no y-values
and then forgot to revise it back to work with the real-life format of the
file.

I'll be glad to help with any more bugs.

Regards,
Shockley

 

[L Mehl]

Shockley --

Thank you. You are very generous to help me so completely.

Larry

Larry,

before the import<

Replace the line

If tsRead.AtEndOfStream Then Exit Do

with this line

If sTest = " " Then Exit Do

This will stop the parsing at the line with the single space--no need to
strip the y-values. I was testing the macro on a souce file with no y-values
and then forgot to revise it back to work with the real-life format of the
file.

I'll be glad to help with any more bugs.

Regards,
Shockley

....

 

[shockley]

Larry,

I have a weakness for text file manipulation--it's been my pleasure!

Regards,
Shockley