Info

Sub ImportFile()

Dim WBO As Workbook Dim WBC As Workbook

Set WBO = ActiveWorkbook ThisFile = "C:\inventory.csv" Workbooks.OpenText Filename:=ThisFile Cells.Copy WBO.Worksheets("Data").Range("A1")

If Cells(65536, 1).Value > 1111 Then

' The file was too large. Import again, starting at row 32767 Workbooks.OpenText Filename:=ThisFile, StartRow:=32767 1 There are some rows that have already been copied ' Everything from 32767 to 65,536 is already on the data sheet. 1 Delete the first 32,770 rows from this second pass of the file RowsToSkip = Application.Rows + 1 - 32767 Cells(1, 1).Resize(RowsToSkip, 1).EntireRow.Delete Cells.Copy WBO.Worksheets("Data").Range("AA1") End If End Sub

Reading Text Files One Row at a Time

Continuing the example, as the retail chain keeps opening more stores, they will eventually get to store 50. Fifty stores times 2,000 SKUs means a file with 100,000 rows. After this happens, the alternative is to read the text file one row at a time. The code for doing this is the same code you might remember in your first high school BASIC class.

You need to open the file for INPUT as #1. You can then use the Line Input #1 statement to read a line of the file into a variable. The following code opens sales.txt, reads 10 lines of the file into the first 10 cells of the worksheet, and closes the file.

Sub Import10()

ThisFile = "C\sales.txt" Open ThisFile For Input As #1 For i = 1 To 10

Line Input #1, Data Cells(i, 1).Value = Data Next i Close #1 End Sub

Rather than read only 10 records, you will want to read until you get to the end of the file. A variable called EOF is automatically updated by Excel. If you open a file for input as #1, then checking EOF(1) will tell you whether you've read the last record or not.

Use a Do...While Loop to keep reading records until you've reached the end of the file.

Sub ImportAll()

ThisFile = "C:\sales.txt" Open ThisFile For Input As #1 Ctr = 0 Do

Cells(Ctr, 1).Value = Data Loop While EOF(1) = False Close #1 End Sub

After reading records with code such as this, you will note in Figure 18.9 that the data is not parsed into columns. All the fields are in Column A of the file.

Figure 18.9

When reading a text file one row at a time, all the data fields end up in one long entry in Column A.

Figure 18.9

When reading a text file one row at a time, all the data fields end up in one long entry in Column A.

Use the TextToColumns method to parse the records into columns. The parameters for TextToColumns are nearly identical to the OpenText method.

Cells(1, 1).Resize(Ctr, 1).TextToColumns Destination:=Range("A1"), _ 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))

Rather than hard-code that you are using the #1 designator to open the text file, it is safer to use the FreeFile function. This returns an integer representing the next file number available for use by the Open statement. The complete code to read a text file smaller than 65,536 rows would be

Sub ImportAll()

ThisFile = "C:\sales.txt"

FileNumber = FreeFile

Open ThisFile For Input As #FileNumber

Line Input #FileNumber, Data Ctr = Ctr + 1

Cells(Ctr, 1).Value = Data Loop While EOF(FileNumber) = False Close #FileNumer

Cells(1, 1).Resize(Ctr, 1).TextToColumns Destination:=Range("A1"), _ DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, xlGeneralFormat), _ Array(2, xlMDYFormat), Array(3, xlGeneralFormat), _ Array(4, xlGeneralFormat), Array(5, xlGeneralFormat), _ Array(5, xlGeneralFormat), Array(6, xlGeneralFormat), _ Array(7, xlGeneralFormat), Array(8, xlGeneralFormat), _

0 0

Post a comment