Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File Import leaves a blank row after each line | Setting up and Configuration of Excel | |||
how can i convert text file to excel file ? | New Users to Excel | |||
Importing text file to excel | Excel Discussion (Misc queries) | |||
character restrictions when importing data from a text file | Excel Discussion (Misc queries) | |||
Exporting excel to text file | Excel Discussion (Misc queries) |