Importing Large Text Files into MS Excel

[vassabi]

Any suggestions on how I can code the importing of a large text file into MS Excel, that exceeds the maximum number of lines available on a MS Excel worksheet (i.e. 65,536)?

Currently I am using the following code to import the file:

Workbooks.OpenText Filename:=SelectFile1, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _
1), Array(10, 1), Array(13, 1), Array(46, 1), Array(60, 1), Array(65, 1), Array(76, 1),Array(77, 1), Array(91, 1), Array(92, 1), Array(107, 1), Array(108, 1), Array(121, 1),Array(132, 1), Array(133, 1))

Any help would be appreciated!!

Cheers.

 

[nath]

Hi Dan73,

YOu will need to reorganise your text file somehow. Either you read the information and write them on one spreadsheet, changing sheet when the limit has been reached, either you start by splitting your text file into as many file as needed, then open then in turn with the OpenText method (as in your code) onto different spreadsheets.

You will have to search information about reading text files. It will be something like:

[\code]
Sub ModifyTxtFile()
Dim iFile As Integer, jFile As Integer
Dim str As String
Dim numOutputFiles as integer
Dim myNumLines as long

numOutputFiles = 1
myNumLines = 0

iFile = FreeFile()
Open "C:\MySourceTextFile.txt" For Input As #iFile

jFile = FreeFile()
Open "C:\MyOUtputFile" & numOutputFiles & ".txt" For Output As #jFile

Do While Not EOF(iFile)
Line Input #iFile, str
if numFiles < 65000 then
Print #jFile, str
myNumLines = myNumLines + 1
else
Close #jFile
numOutputFiles = numOutputFiles + 1
jFile = FreeFile()
Open &quot;C:\MyOUtputFile&quot; & numOutputFiles & &quot;.txt&quot; For Output As #jFile
Print #jFile, str
myNumLines = 1
end if
Loop

Close #jFile
Close #iFile
End Sub

That should put you on the right track hopefully.

Nath

 

[danharman]

Thanks Nath, much appreciated.
Dan73