Thread: Excel 2003 - Importing text file > 65536 rows

Hello,
Recently one of our systems underwent a large upgrade and one of the files I receive everyday is about 10x bigger than it was. I had this automated process, where excel would open this file and go to the very bottom, then to the very right, and copy this value into a master sheet for me. This is simply the count of the # of records in the file.

The problem is now, this file is 200 000 rows, too big for our Excel 2003 to handle at work... so I looked around and found some code that can import this into multiple spreadsheets, but I am trying to avoid this as I have to do this with around 40 files, is there a way to just import the last row? or the last section?, -- as the only value in this entire file that I require is the the rightmost value in the very bottom row.

Thanks for any suggestions in advance,
Adam

A software upgrade may be the simplest answer, Excel 2007 can handle 1,048,576 rows x 16,384 columns.

Hello Adam,

What type of file are you referring to? Is it an Excel workbook, a CSV, a text file, or a database?

Hello AdamParker,

I haven't test this macro as I don't have a file large enough to import. The macro will prompt you for the text file to open and start importing the data at cell "A1" of the last worksheet in the workbook (usually Sheet3). Once the sheet is filled, a new sheet will be added to the workbook. This process will be repeated until the file is downloaded. The default delimiter is a comma. You change this and the starting worksheet in the code. They are marked in red. The file status is displayed in Excel's Status Bar .

Code:

'Written: October 21, 2009
'Author:  Leith Ross
'Summary: Imports a text file greater than 65535 lines into Excel.

Sub ImportTextFile()

  Dim Arr As Variant
  Dim Data As Variant
  Dim Delimiter As String
  Dim FileFilter As String
  Dim FileName As String
  Dim N As Integer
  Dim R As Long
  Dim Wks As Worksheet
  
    Delimiter = ","
    Set Wks = Worksheets("Sheet3")
    
    FileFilter = "Text Files (*.csv; *.txt), *.csv;*.txt, All Files (*.*), *.*"
    FileName = Application.GetOpenFilename(FileFilter, 0, "Open")
    If FileName = "False" Then Exit Sub
    
      N = FreeFile
      Application.DisplayStatusBar = True
      
      Open FileName For Input As #N
        Do While Seek(N) <= LOF(N)
          R = R + 1
          If R > Rows.Count Then
             R = 1
             Set Wks = Worksheets.Add(After:=Worksheets.Count)
          End If
        
          Line Input #N, Data
          Arr = Split(Data, Delimiter)
          Wks.Cells(R, "A").Resize(ColumnSize:=UBound(Arr) + 1).Value = Arr
          
          Application.StatusBar = "Writing Row " & R & " on " & Wks.Name
        Loop
      Close #N
      
      Application.StatusBar = "Download complete."
      
End Sub

If all you need is the last value, why copy the file to the worksheet at all?

Originally Posted by AP

... as the only value in this entire file that I require is the the rightmost value in the very bottom row.

Originally Posted by shg

If all you need is the last value, why copy the file to the worksheet at all?

Originally Posted by AP

Because we do this for a few hundred files, only about 40 of these files are now exceeding the excel row limit... could open them manually, but automating this would save time.

My question is, why read any file in at all, instead of just having code that reads to the bottom of the file and gets the one value you need?

So ... how about this:

Code:

Sub TheLastWord()
    Dim sFile       As String
    Dim iFF         As Integer
    Dim sLine       As String
    Dim asWd()      As String

    sFile = Application.GetOpenFilename(FileFilter:="Text Files, *.csv;*.txt", _
                                        Title:="Pick a file")
    If sFile = "False" Then Exit Sub

    iFF = FreeFile

    Open sFile For Input As #iFF
    Do Until EOF(iFF)
        Line Input #iFF, sLine
    Loop
    Close #iFF
    
    asWd = Split(sLine, ",")
    MsgBox Prompt:=asWd(UBound(asWd)), Title:="... and the last comma-delimited phrase is:"
End Sub

You're welcome. A little simplication, and coded as a function:

Code:

Function TheLastWord() As String
    ' Returns the last comma-delimited item from a text file
    ' VBA function only
    Dim sFile       As String
    Dim iFF         As Integer

    sFile = Application.GetOpenFilename(FileFilter:="Text Files, *.csv;*.txt", _
                                        Title:="Pick a file")
    If sFile = "False" Then Exit Function

    iFF = FreeFile

    Open sFile For Input As #iFF
    Do Until EOF(iFF)
        Line Input #iFF, TheLastWord
    Loop
    Close #iFF
    
    TheLastWord = Mid(TheLastWord, InStrRev(TheLastWord, ",") + 1)
End Function