Solved: Trying to use Line Input, but only vbLF

Printable View

11-02-2010, 06:06 AM
Paul_Hossler

Solved: Trying to use Line Input, but only vbLF

I need to read a very large tab delimited text file (mainframe generated), but each line is terminated with a LF, not a CR-LF pair, or a CR, which Line Input needs.

Because of the file size and because a lot of data will not be stored because of the data in a line, I didn't want to just brute force load it and do text to columns.

Right now, I'm thinking about

1. reading and writing byte-wise a copy of the file, replacing LF with CR's
2. using Line Input to pull in a single record
3. Split() to seperate at the tabs
4. Then storing the record if it meets certain critera

Seems that that will take even more time

Suggestions anyone?

Paul
11-02-2010, 06:35 AM
Paul_Hossler

Meant to include the current incarnation of the "proof of concept" macro I'm using

[VBA]
Sub drv2()
Dim iFile1 As Long, iFile2 As Long
Dim sBuffer As String

Dim i As Long

iFile1 = FreeFile
Open "File.txt" For Binary As iFile1

iFile2 = FreeFile
Open "File.out" For Output As iFile2
i = 1
While Not EOF(iFile1)
sBuffer = Input(65536, #iFile1)
sBuffer = Replace(sBuffer, vbLf, vbCr & vbLf)
Print #iFile2, sBuffer
i = i + 1
Application.StatusBar = i
Wend

Close #iFile2
Close #iFile1
End Sub
[/VBA]

It works, but somehow seems unnecessary to have to do it this way.

For example, if the file records are already terminated with CR-LK, it'll fail

Paul
11-02-2010, 06:49 AM
Kenneth Hobs

Seems like you might have a vbCR twice sometimes after the first Replace. One could then replace the two codes for one.

Seems like I dealt with an issue like yours once.

Does vbCR & vbLF differ from vbCRLF?

Here is an interesting macro for importing a big csv. http://www.mrexcel.com/forum/showthread.php?t=505997
11-02-2010, 09:25 AM
Kenneth Hobs

Seems like I did something like what they did in this thread to handle the vbCRLF. http://www.mrexcel.com/forum/showthread.php?t=505975
11-02-2010, 10:33 AM
Paul_Hossler

Ken -

thanks for the links

Quote:

a. Seems like you might have a vbCR twice sometimes after the first Replace.
b. Does vbCR & vbLF differ from vbCRLF?

a. I'll trace through the output to see
b. No, I just getting old and forgetful

aul
11-03-2010, 09:55 AM
shrivallabha

Quote:

Originally Posted by Paul_Hossler

Ken -

thanks for the links

a. I'll trace through the output to see
b. No, I just getting old and forgetful

aul

Yes, you forgot "P" in your name!
11-03-2010, 01:16 PM
Paul_Hossler

See -- that proves it!!

Can I blame my keyboard?

Paul