Info

Array(9, xlGeneralFormat), Array(10, xlGeneralFormat), _ Array(10, xlGeneralFormat), Array(11, xlGeneralFormat))

End Sub

Reading Text Files with More Than 98,304 Rows

You can use the Line Input method for reading a large text file. My strategy is to read rows into cells A1:A65534, then begin reading additional rows into cell AA2. I start in Row 2 on the second set so that the headings can be copied from Row 1 of the first dataset. If the file is large enough that it fills up Column AA, then move to BA2, CA2, and so on. Also, I stop writing columns when I get to Row 65,534, leaving 2 blank rows at the bottom. This ensures that the code Range("A65536").End(xlup).Row finds the final row. The following code reads a large text file into several sets of columns:

Sub ReadLargeFile()

ThisFile = "C:\sales.txt"

FileNumber = FreeFile

Open ThisFile For Input As #FileNumber

Line Input #FileNumber, Data Cells(NextRow, NextCol).Value = Data NextRow = NextRow + 1 If NextRow = 65534 Then 1 Parse these records

Range(Cells(1, NextCol), Cells(65536, NextCol)).TextToColumns _ Destination:=Cells(1, NextCol), DataType:=xlDelimited, _ Comma:=True, FieldInfo:=Array(Array(1, xlGeneralFormat), _ Array(2, xlMDYFormat), Array(3, xlGeneralFormat), _ Array(4, xlGeneralFormat), Array(5, xlGeneralFormat), _ Array(6, xlGeneralFormat), Array(7, xlGeneralFormat), _ Array(8, xlGeneralFormat), Array(9, xlGeneralFormat), _ Array(10, xlGeneralFormat), Array(11, xlGeneralFormat)) ' Copy the headings from section 1 If NextCol > 1 Then

Range("A1:K1").Copy Destination:=Cells(1, NextCol) End If

' Set up the next section NextCol = NextCol + 26 NextRow = 2 End If

Loop

Close #FileNumber

' Parse the final Section of records FinalRow = NextRow - 1 If FinalRow = 1 Then

1 Handle if the file coincidentally had 65536 rows exactly NextCol = NextCol - 26

Else

Range(Cells(2, NextCol), Cells(FinalRow, NextCol)).TextToColumns _ Destination:=Cells(1, NextCol), DataType:=xlDelimited, _ Comma:=True, FieldInfo:=Array(Array(1, xlGeneralFormat), _ Array(2, xlMDYFormat), Array(3, xlGeneralFormat), _ Array(4, xlGeneralFormat), Array(5, xlGeneralFormat), _

Array(6, xlGeneralFormat), Array(7, xlGeneralFormat), _ Array(8, xlGeneralFormat), Array(9, xlGeneralFormat), _ Array(10, xlGeneralFormat), Array(11, xlGeneralFormat)) If NextCol > 1 Then

Range("A1:K1").Copy Destination:=Cells(1, NextCol) End If End If

I usually write the DataSets variable to a named cell somewhere in the workbook so I know how many datasets I have in the worksheet later.

As you can imagine, it is possible, using this method, to read 655,000 rows of data into a single worksheet. The code that you formerly used to filter and report the data now becomes more complex. You might find yourself creating pivot tables from each set of columns to create a dataset summary, and then finally summarizing all the summary tables with a final pivot table. At some point, you need to consider that the application really belongs in Access, or the data should be stored in Access with an Excel front end, as discussed in Chapter 19, "Using Access as a Back End to Enhance Multi-User Access to Data."

0 0

Post a comment