Changing the structure of the files

Could you post an example CSV file for us to play with - perhaps with dummy data? What is the max number values for any given day? Could it be anything?

Originally Posted by Brendan_Floyde

Could you post an example CSV file for us to play with - perhaps with dummy data? What is the max number values for any given day? Could it be anything?

Sure, I'll update the main post by adding a sample file.
The max number of records in different files are not same and could be anything.
But, the structure of the input files is similar.
Thank you

As your data is nice and square adodb is way quicker than looping through each line. Requires a reference in the VBE go tools / references then tick microsoft active data objects 6.1 library (or the highest / nearest you have) - you only have to do this once.

this code assumes 1334108.csv is saved in the same directory as the file with the macro in.

This just transforms the one input csv file. You mention you have multiple in a folder. It's not clear how you want the data consolidated. Should the values be added together or new line for each input folder? Can you expand the output file example above to show what you want and also perhaps post a second input file?

Sub LoadCSVtoArray()

strPath = ThisWorkbook.Path & "\"

Set cn = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.Open strcon

strSQL = "transform Sum(Value) SELECT id, Year, Day FROM 1334108.csv GROUP BY id, Year, Day PIVOT Month;"


Dim rs As Recordset
Dim rsARR() As Variant
Dim fldCount As Integer
Dim iCol As Integer
Dim iRow As Integer


Set rs = cn.Execute(strSQL)
rsARR = rs.GetRows

fldCount = rs.Fields.Count

    For iCol = 1 To fldCount
        Range("a1").Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
    Next


rs.Close
Set cn = Nothing

Range("a2").Resize(UBound(rsARR, 2) + 1, UBound(rsARR, 1) + 1).Value = TransposeDim(rsARR)

End Sub
Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
    
    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant
    
    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)
    
    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X
    
    TransposeDim = tempArray

End Function

Originally Posted by Brendan_Floyde

This just transforms the one input csv file. You mention you have multiple in a folder. It's not clear how you want the data consolidated. Should the values be added together or new line for each input folder? Can you expand the output file example above to show what you want and also perhaps post a second input file?

2-15-2015 8-17-21 PM.png
is it clear? No header no blank row in between.
Just added another sample file to the original post.
Thank you.