PDA

View Full Version : Solved: Trying to use Line Input, but only vbLF



Paul_Hossler
11-02-2010, 06:06 AM
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

Paul_Hossler
11-02-2010, 06:35 AM
Meant to include the current incarnation of the "proof of concept" macro I'm using


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


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

Kenneth Hobs
11-02-2010, 06:49 AM
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

Kenneth Hobs
11-02-2010, 09:25 AM
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

Paul_Hossler
11-02-2010, 10:33 AM
Ken -

thanks for the links



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

shrivallabha
11-03-2010, 09:55 AM
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!

Paul_Hossler
11-03-2010, 01:16 PM
See -- that proves it!!

Can I blame my keyboard?

Paul