[RESOLVED] How to optimize this problem

[doasidont]

I am importing hundreds of delimited text files into a workbook. Each text file can have anywhere from 1 to hundreds of records. So the rows in play would look something like this where 'n' could be any number (probably less than 1000):
Column A Column B
F1 R1
F1 R2
F1 R3
F1 ...
F1 Rn
F2 R1
... ...
Fn R1
Fn R2

Everything I've tried either causes Excel to throw up (out of resources) or takes forever. Does anyone have a good way of computing the number of records for each file as each file is imported, or after all of the files have been reported? Thanks.

[westconn1]

not knowing what you have tried so far..........

you could use various methods to get the last row (number of records) after each file is imported, deduct the previous, store value to an array (or whatever) for later processing, CountA of column A may be a good choice, redim array in blocks of 500, speed testing would demonstrate which method is quicker

[3com]

well, imagine plane text file comma delimited, such as...

F1R1,F2R1,F3R1,F4R1,F5R1,F6R1,F7R1,F8R1
F1R2,F2R2,F3R2,F4R2,F5R2,F6R2,F7R2,F8R2
F1R3,F2R3,F3R3,F4R3,F5R3,F6R3,F7R3,F8R3
F1R4,F2R4,F3R4,F4R4,F5R4,F6R4,F7R4,F8R4
F1R5,F2R5,F3R5,F4R5,F5R5,F6R5,F7R5,F8R5
F1R6,F2R6,F3R6,F4R6,F5R6,F6R6,F7R6,F8R6
F1R7,F2R7,F3R7,F4R7,F5R7,F6R7,F7R7,F8R7

... And you want count records, before import file...

Sub RecordsCounter()

Dim fPath As String
fPath = "C:\ImportFile.txt"  '  Change path file.
Dim arr(), arr0()
Dim nRecord As Long
nRecord = 0

Open fPath For Input As #1    ' Open file
Do While Not EOF(1)    ' Loop until end of file..
    ReDim Preserve arr0(nRecord) ' redim variable to store records
    Input #1, arr0(nRecord) ' Read records and store in on array
    Debug.Print arr0(nRecord) ' Print records 4 check it.
    nRecord = nRecord + 1
Loop
Close #1    ' Close file.

For Z = 0 To UBound(arr0)
    ReDim Preserve arr(Z)
    arr(Z) = Split(arr0(Z), ",")
Next Z

totalRecords = UBound(arr)
MsgBox " The num of records is " & totalRecords

End Sub

Perhaps this code can help as starting point.

[kaliman79912]

Any reason you would need to count before importing?

Anyway. I think if you have a delimited file, the best way to go for importing would be something like:

Workbooks.OpenText Filename:="C:\Temp\TextFile.csv", Origin:=65001, StartRow:=4, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 9), Array(4, 2), Array(5, 2), Array(6, 2))

This code will reaad your csv (commad separated values) file, starting at row 4 (that is because sometimes there are title rows you want to avoid), the FieldInfo is an array that determines the column number and the data type. i.e: 9= do not import, 2= General, etc.

[doasidont]

Thanks. Counting rows was the trick.