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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.