VBA: how to load a full CSV file into an array

[actjfc]

I need Excel help. I need to keep using the following code to load a CSV into an Array, Can somebody complete the code to make it work for me? THANKS!

Sub Import_CSV()

    Dim CSV_File As String
    Dim iFileNum As Integer
    Dim CSV_Row_Line As String
    
    CSV_File = "C:\Sample.csv"

    iFileNum = FreeFile()
    Open CSV_File For Input As iFileNum

    Do While Not EOF(iFileNum)
        
        Line Input # iFileNum, CSV_Row_Line
                
        'How to asign the full number of rows and columns in the cvs file to an array! 
        ' Data(i,j) = CSV_Row_Line split by "," ?????!
        'If there is a way to asign it all at once it may work, but I am also interested in reading the CSV file them row by row.
                
        End If
    
    Loop

    Close iFileNum
    
End Sub

Thanks again!

 

[Norie]

You could always try using ADO to read the CSV into a file using the GetRows method.

 

[actjfc]

Thanks Norie, I got this procedure from the decision maker, and I need to stick to it now. Can you point out a website or link? where I can learn how to do this by using this kind of code iFileNum = FreeFile() and Open CSV_File For Input As iFileNum. Thanks!

 

[StephenCrump]

Wouldn't something like this suffice?

Dim wbCSV As Workbook
Dim Data As Variant

Set wbCSV = Workbooks.Open(Filename:="C:\sample.csv")

With wbCSV
    Data = .Sheets(1).UsedRange.Value
    .Close
End With

'Just to demonstrate: dump Data to Sheet1!A1 (say)
Worksheets("Sheet1").Range("A1").Resize(UBound(Data), UBound(Data, 2)).Value = Data

 

[actjfc]

Thanks, this is a very neat efficient way to get the whole data in one worksheet at once. Is it something like it to dump the data at once in a 2-dimensional array in the PC memory? Thanks again!

 

[Norie]

The code Stephen posted dumps all the data into a 2-dimensional array, Data, here.

With wbCSV
    Data = .Sheets(1).UsedRange.Value
    .Close
End With

That array is stored in memory and can be used once the CSV has closed to, for example, put the data in another sheet.

Worksheets("Sheet1").Range("A1").Resize(UBound(Data), UBound(Data, 2)).Value = Data

 

[actjfc]

Thanks!

It looks like is still "dumping" the data in the Sheet1, or using it just for a fraction of a second! It does not overwrite but I can see a flick of the CSV data in the screen and disappear. What would happen in the CSV file has more than a million rows? After, I load the data I want to locate the rows that comply with a condition. I got a Subcript out of range error with this code. Take a look, please.

Sub MrExcel()

Dim wbCSV As Workbook
Dim Data As Variant

Set wbCSV = Workbooks.Open(fileName:="C\sample.csv")

With wbCSV
    Data = .Sheets(1).UsedRange.Value
    .Close
End With

For i = 0 To UBound(Data, 1)

    If Left(Data(i, 1), 4) = "TEST" Then
    
            For j = 0 To UBound(Data, 2)
            
                Sheet1.Cells(i, j) = Data(i, j)
            
            Next j
    
    End If

Next i

End Sub

 

[Norie]

The data isn't being dumped in Sheet1.

What's happening is the CSV file is being opened in Excel, then the array Data is populated with the data from the file, and the file is closed leaving you with the data from the file in memory in the array Data.

If there are more then 1,048,576 rows in the CSV then you won't be able to open it in Excel so this method isn't going work.

 

[actjfc]

Thanks! Application.ScreenUpdating = False will take care of the flicker. Now, I need to ge a rid of the Subscript Out of Range error. Thanks for the explanation.