Thursday, April 28, 2016

Large Text Files

Split between worksheets

While this problem is alleviated in Excel 2007+ with its 1,048,576 rows by 16,348 columns, The old XL versions are still here.

Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet.

Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets:

Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = InputBox("Please enter the Text File's name, e.g. test.txt") '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 Screen Updating Off Application.ScreenUpdating = False '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 'For Excel versions before Excel 97, change 65536 to 16384 If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
'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
'Close The Open Text File
'Remove Message From Status Bar
Application.StatusBar = False

End Sub

Importing Text Files Larger Than 16,384/65,536 Rows

Notice the code about 17 lines from the bottom of the macro.

'For xl97 and later change 16384 to 65536.

Also, after import, the data must be parsed. Use Data>Text to columns.

If you have not worked with macros before, Dave McRitchie has a tutorial:

Getting Started with Macros and User Defined Functions

See all Topics

No comments: