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.?

Importing a large text file
 

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.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=521665

Importing a large text file
 

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" wrote in message
...
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.?

Importing a large text file
 

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" wrote in message
...
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.?

Importing a large text file
 

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" wrote in message
...
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" wrote in message
...
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.?