Fastest way to aggregate data across 3 csv files + 1 user input table

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have 3 separate csv files plus 1 table for User input, to then aggregate results and output.

The largest csv file, CSV1 has 10,0001 rows and 6 columns
The two other csv files, CSV2 and CSV3 are both 9 rows by 4 columns

I need to map CSV1 against CSV2, CSV3 (i.e. VLOOKUP to append 2 additional columns to CSV1), aggregate results and then a final calculation column.

Currently, I'm using code based on thread [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 1[/URL] suggest by @Rick Rothstein as:
Rich (BB code):
Private Sub Get_Data(ByRef strFilePath As String, ByRef rng As Range)
    'Procedure to write data from selected data input file to range object of temporary sheet


    Dim lngFileNum      As Long
    Dim lngRow          As Long
    Dim lngLooper       As Long
    Dim varRec          As Variant
    Dim strTotalFile    As String
    Dim strRecords()    As String
    Dim strFields()     As String
    
    lngFileNum = FreeFile
    StatusBar_Notification "Importing data from file: " & strFilePath & " to sheet: " & rng.Parent.Name
    Settings False
    
    Open strFilePath For Binary As #lngFileNum 
        strTotalFile = Space(LOF(lngFileNum))
        Get #lngFileNum , , strTotalFile
    Close #lngFileNum 
    
    strRecords = Split(strTotalFile, vbNewLine)
    
    On Error Resume Next
    For lngLooper = LBound(strRecords) To UBound(strRecords)
        strFields = Split(strRecords(lngLooper), ",")
        rng.Offset(lngLooper, 0).Resize(1, UBound(strFields) + 1) = strFields
    Next lngLooper
    On Error GoTo 0
    
    Settings True
    
    Erase strRecords
    Erase strFields
    
End Sub
Above outputs a CSV file into a new sheet (the range argument is pre-defined for the new sheet), which I repeat 3 times for each CSV file.

For the line in blue, I found I couldn't output to an array and only directly to the sheet.

Ideally, I'd like to create an array of the CSV1 data and then append against CSV2 and CSV3 using dictionaries (in effect to aggregate against the Key ID value within CSV1) and then perform a few basic arithmetic calculations in code/memory and print out/hard code results.

Consideration #1
Read CSV2 and CSV3 in first (and create their own separate dictionaries) and then whilst looping over CSV1 to read in the data, apply the dictionary map for CSV2 and CSV3 respectively and create separate columns for the sheet for CSV1
With this, I should be able to perform all calculations and output into a dictionary for each loop, aggregating results into an output dictionary as I read them in.

Can anyone suggest methods or processes to make reading and then append and aggregate the data as fast as possible if consideration #1 can be improved? Please note, I'm looking for speed improvement suggestions, not specific coding directly for the spreadsheet.

TIA,
JackDanIce
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top