Importing a large text file

I have a text file consisting of a very long list of numbers. I would like
to open this file in Excel, but Excel is limits imports of text files to
64,000 lines.

Is it possible to open such files, placing the first 64,000 lines in column
A, the next 64,000 in column B, etc.?

I believe you will have to break up the text file into pieces, export one at a time to excel. Then copy and paste to multiple columns in one sheet.

Try the macro below.

HTH,
Bernie
MS Excel MVP


Sub LargeFileImportColumnVersion()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim StoredCalcMode As Variant

'Ask User for File's Name
FileName = Application.GetOpenFilename

'Check for no entry
If FileName = "" Then End

'Get Next Available File Handle Number
FileNum = FreeFile()

'Open Text File For Input
Open FileName For Input As #FileNum

'Turn Stuff Off
With Application
.ScreenUpdating = False
StoredCalcMode = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet

'Set The Counter to 1
Counter = 1

'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65536 Then
'If On The Last Row Then Go To Top Of Next Column
Cells(1, ActiveCell.Column + 1).Select
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If

'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close

'Remove Message From Status Bar
Application.StatusBar = False

With Application
.ScreenUpdating = True
.Calculation = StoredCalcMode
.EnableEvents = True
End With

End Sub



"hmm" <[email protected]> wrote in message
news:[email protected]...
>I have a text file consisting of a very long list of numbers. I would like
> to open this file in Excel, but Excel is limits imports of text files to
> 64,000 lines.
>
> Is it possible to open such files, placing the first 64,000 lines in column
> A, the next 64,000 in column B, etc.?

Thanks for the macro.

I copied and pasted it into a new module under the PERSONAL.XLS workbook.
When I tried running it, after selecting the file in the OPEN window, it gave
the error "Run-time error '62: Input past end of file". When I opened the
debugger, the highlight cursor was at the line of code reading:

Line Input #FileNum, ResultStr

Any ideas how to fix it?


"Bernie Deitrick" wrote:

> Try the macro below.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Sub LargeFileImportColumnVersion()
> Dim ResultStr As String
> Dim FileName As String
> Dim FileNum As Integer
> Dim Counter As Double
> Dim StoredCalcMode As Variant
>
> 'Ask User for File's Name
> FileName = Application.GetOpenFilename
>
> 'Check for no entry
> If FileName = "" Then End
>
> 'Get Next Available File Handle Number
> FileNum = FreeFile()
>
> 'Open Text File For Input
> Open FileName For Input As #FileNum
>
> 'Turn Stuff Off
> With Application
> .ScreenUpdating = False
> StoredCalcMode = .Calculation
> .Calculation = xlCalculationManual
> .EnableEvents = False
> End With
>
> 'Create A New WorkBook With One Worksheet In It
> Workbooks.Add Template:=xlWorksheet
>
> 'Set The Counter to 1
> Counter = 1
>
> 'Loop Until the End Of File Is Reached
> Do While Seek(FileNum) <= LOF(FileNum)
> 'Display Importing Row Number On Status Bar
> Application.StatusBar = "Importing Row " & _
> Counter & " of text file " & FileName
> 'Store One Line Of Text From File To Variable
> Line Input #FileNum, ResultStr
> 'Store Variable Data Into Active Cell
> If Left(ResultStr, 1) = "=" Then
> ActiveCell.Value = "'" & ResultStr
> Else
> ActiveCell.Value = ResultStr
> End If
>
> If ActiveCell.Row = 65536 Then
> 'If On The Last Row Then Go To Top Of Next Column
> Cells(1, ActiveCell.Column + 1).Select
> Else
> 'If Not The Last Row Then Go One Cell Down
> ActiveCell.Offset(1, 0).Select
> End If
>
> 'Increment the Counter By 1
> Counter = Counter + 1
> 'Start Again At Top Of 'Do While' Statement
> Loop
> 'Close The Open Text File
> Close
>
> 'Remove Message From Status Bar
> Application.StatusBar = False
>
> With Application
> .ScreenUpdating = True
> .Calculation = StoredCalcMode
> .EnableEvents = True
> End With
>
> End Sub
>
>
>
> "hmm" <[email protected]> wrote in message
> news:[email protected]...
> >I have a text file consisting of a very long list of numbers. I would like
> > to open this file in Excel, but Excel is limits imports of text files to
> > 64,000 lines.
> >
> > Is it possible to open such files, placing the first 64,000 lines in column
> > A, the next 64,000 in column B, etc.?
>
>
>

My assumption, when you said list, was that the numbers were like this:

1
2
3
4
5
....

But if your file is like

1 2 3 4 5 .....

Then that may be a problem - though I can't get your specific error - I get an out of memory error.

How is your input file structured? And just how large is it?

HTH,
Bernie
MS Excel MVP


"hmm" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the macro.
>
> I copied and pasted it into a new module under the PERSONAL.XLS workbook.
> When I tried running it, after selecting the file in the OPEN window, it gave
> the error "Run-time error '62: Input past end of file". When I opened the
> debugger, the highlight cursor was at the line of code reading:
>
> Line Input #FileNum, ResultStr
>
> Any ideas how to fix it?
>
>
> "Bernie Deitrick" wrote:
>
>> Try the macro below.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> Sub LargeFileImportColumnVersion()
>> Dim ResultStr As String
>> Dim FileName As String
>> Dim FileNum As Integer
>> Dim Counter As Double
>> Dim StoredCalcMode As Variant
>>
>> 'Ask User for File's Name
>> FileName = Application.GetOpenFilename
>>
>> 'Check for no entry
>> If FileName = "" Then End
>>
>> 'Get Next Available File Handle Number
>> FileNum = FreeFile()
>>
>> 'Open Text File For Input
>> Open FileName For Input As #FileNum
>>
>> 'Turn Stuff Off
>> With Application
>> .ScreenUpdating = False
>> StoredCalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .EnableEvents = False
>> End With
>>
>> 'Create A New WorkBook With One Worksheet In It
>> Workbooks.Add Template:=xlWorksheet
>>
>> 'Set The Counter to 1
>> Counter = 1
>>
>> 'Loop Until the End Of File Is Reached
>> Do While Seek(FileNum) <= LOF(FileNum)
>> 'Display Importing Row Number On Status Bar
>> Application.StatusBar = "Importing Row " & _
>> Counter & " of text file " & FileName
>> 'Store One Line Of Text From File To Variable
>> Line Input #FileNum, ResultStr
>> 'Store Variable Data Into Active Cell
>> If Left(ResultStr, 1) = "=" Then
>> ActiveCell.Value = "'" & ResultStr
>> Else
>> ActiveCell.Value = ResultStr
>> End If
>>
>> If ActiveCell.Row = 65536 Then
>> 'If On The Last Row Then Go To Top Of Next Column
>> Cells(1, ActiveCell.Column + 1).Select
>> Else
>> 'If Not The Last Row Then Go One Cell Down
>> ActiveCell.Offset(1, 0).Select
>> End If
>>
>> 'Increment the Counter By 1
>> Counter = Counter + 1
>> 'Start Again At Top Of 'Do While' Statement
>> Loop
>> 'Close The Open Text File
>> Close
>>
>> 'Remove Message From Status Bar
>> Application.StatusBar = False
>>
>> With Application
>> .ScreenUpdating = True
>> .Calculation = StoredCalcMode
>> .EnableEvents = True
>> End With
>>
>> End Sub
>>
>>
>>
>> "hmm" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a text file consisting of a very long list of numbers. I would like
>> > to open this file in Excel, but Excel is limits imports of text files to
>> > 64,000 lines.
>> >
>> > Is it possible to open such files, placing the first 64,000 lines in column
>> > A, the next 64,000 in column B, etc.?
>>
>>
>>