Convert a 100,000 character linear text file

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

I have a 100,000+ character linear text file that I would like convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in advance
for any ideas.

Matthew Saxon
 
have you tried just opening the file in excel.

when you say linear, do you mean the it is 100,000+ lines long (single
character on each line) and you want to build lines of 178 characters from
that?

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) >= 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) > 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub
 
No, sorry. The file is one line with 100,000 plus characters. It is a
file from a bank database.
 
Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long, i as long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
for i = 1 to len(LineofText)
sStr = sStr & Mid(lineofText,i,1)
if len(sStr) >= 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Next
Loop
'Close the file
if len(sStr) > 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

If that doesn't work, we will need to read the line in in pieces - post back
if it doesn't work.
 
Back
Top