I have a requirement to collate over 6000 csv files into a single csv document. The current VBA process is: 1. Open individual CSV data file 2. Load contents of file to array based on number of rows 3. Close individual CSV file 4. Process array
In order to improve efficiency of the code and processing, I was hoping there may be a method to load the data from the individual CSV files into an array without opening and closing every single file.
I am using Excel 2011 for Mac.
Ok I am assuming that All 6000 files have the same format.
My Test Conditions
I ran the below code and the code took just 4 seconds.
Option Explicit
Sub Sample()
Dim strFolder As String, strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim StartTime As String, endTime As String
Dim n As Long, j As Long, i As Long
strFolder = "C:\Temp\"
strFile = Dir(strFolder & "*.csv")
n = 0
StartTime = Now
Do While strFile <> ""
Open strFolder & strFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
ReDim Preserve FinalArray(j + UBound(strData) + 1)
j = UBound(FinalArray)
For i = LBound(strData) To UBound(strData)
FinalArray(n) = strData(i)
n = n + 1
Next i
strFile = Dir
Loop
endTime = Now
Debug.Print "Process started at : " & StartTime
Debug.Print "Process ended at : " & endTime
Debug.Print UBound(FinalArray)
End Sub
Screenshot of the folder
Screenshot of the Code Output
UPDATE
Ok I tested it in MAC
My Test Conditions
I ran the below code and the code took LESS THAN 1 second (since there were only 1024 files). So I am expecting it to again run for 4 secs in case there were 6k files
Sub Sample()
Dim strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim StartTime As String, endTime As String
Dim n As Long, j As Long, i As Long
StartTime = Now
MyDir = ActiveWorkbook.Path
strPath = MyDir & ":"
strFile = Dir(strPath, MacID("TEXT"))
'Loop through each file in the folder
Do While Len(strFile) > 0
If Right(strFile, 3) = "csv" Then
Open strFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
ReDim Preserve FinalArray(j + UBound(strData) + 1)
j = UBound(FinalArray)
For i = LBound(strData) To UBound(strData)
FinalArray(n) = strData(i)
n = n + 1
Next i
strFile = Dir
End If
strFile = Dir
Loop
endTime = Now
Debug.Print "Process started at : " & StartTime
Debug.Print "Process ended at : " & endTime
Debug.Print UBound(FinalArray)
End Sub
Screenshot of the folder
Screenshot of the Code Output