Array slicing optimisation

[Kyle123]

Hi All,

I am optimising some code and need to be able to slice large arrays and am hoping that there is a more efficient way.

The arrays are 40k lines plus.

I currently loop through three workbooks, each with 40k lines and, I put each range directly into a variant array, then into an array.

I then loop through the array to access each sub array and convert each sub array to 1d so I can use the filter function. I have tried using INDEX and tried transposing the data from each workbook, both error, which I'm assuming is due to the size of the data.

Currently I'm using the below (which is an excerpt):

Sub getData()Const fPath     As String = "D:\My documents\xyz\"
Dim myfile      As String
Dim tempArr()   As String
Dim x           As Integer
Dim i           As Long
Dim rs          As Variant
Dim rs(1 To 3)  As Variant


myfile = Dir(fPath & "*.csv")
x = 1
Do Until myfile = ""
        Set wb = Workbooks.Open(fPath & myfile, False, True)
        rs = wb.Sheets(1).Columns(1).SpecialCells(2).Value
        rscoll(x) = rs
        x = x + 1
        wb.Close False
myfile = Dir()
Loop


For x = 1 To 3
    ReDim tempArr(1 To UBound(rscoll(x)))
    
    For i = 1 To UBound(rscoll(x))
         tempArr(i) = UCase(rscoll(x)(i, 1))
    Next i
    
    ' Find data in array using Filter()
    ' Do some other stuff
    
Next x
End Sub

To give a bit more background, the files are TabDelimited and I need to find given words (60+) that may exist on any row, and in any of a number of fields on each row.

I've also tried Querying the workbooks with ADO and filtering the recordsets, but this was unbelievably slow.

Any ideas as to whether this can be sped up at all? / Better way of attacking the problem?

Cheers

Kyle

[OnErrorGoto0]

Did you try ADO (either UNION query or three separate queries), then GetRows to put the data into an array? You could also restrict the data using a WHERE clause with Like (though that might not be very fast).

[Kyle123]

Yes, I tried that, but GetRows() still returns a 2d array which needs to be sliced. Also I can restrict the data, but I'd need to run more queries :S

Looks like looping through might be the only option then

Thanks for looking

[snb]

Much simpler ( and faster !):

sub snb()
  c00="D:\My documents\xyz\"
  c01=dir(c00 & "*.csv")

  do until c01=""
    open c00 & c01 for input as #1
      sn=filter(split(Input(LOF(1),1),vbcrlf),"filtercriterion")
    close
    c01=Dir
  loop
End Sub

sn is a 0-dimensional array containing all 'rows' in the csv-file that contain the searched for textstring.

You can even add the content of all csv files into 1 string before splitting and filtering.

[nilem]

as an option

Sub ert()
Dim txt$, tempArr$(), fold As Object, fl As Object
Const fPath As String = "D:\xyz\"

With CreateObject("Scripting.FileSystemObject")
    Set fold = .GetFolder(fPath)
    For Each fl In fold.Files
        With .OpenTextFile(fl, 1, True)
            txt = txt & "," & .ReadAll
            .Close
        End With
    Next fl
End With
Set fold = Nothing: Set fl = Nothing

tempArr = Split(Replace(txt, Chr(10), ","), ",")
MsgBox "LBound(tempArr) = " & LBound(tempArr) & " ;UBound(tempArr) = " & UBound(tempArr)
End Sub

*it works with Russian local settings

[Kyle123]

Great suggestions, Thanks