Read a csv file into an array using Excel VBA

Question

I am unable to figure out how to read in a simple csv file using Excel VBA.

If I want to open and read a csv file, this statement should suffice, as long as the filepath is a valid string?

 ' Open file and read contents 
    Open FilePath For Input As #1 
    FileContent = Input(LOF(1), 1) 
    Close #1

Then, I want to create a two dimensional array, with rows and columns. I would think this should do the job, but it does not.

' Split file content into rows 
RowsArray = Split(FileContent, vbCrLf) 
 
' Split rows into columns 
Dim i As Long 
For i = LBound(RowsArray) To UBound(RowsArray) 
    ColumnsArray = Split(RowsArray(i), ",") 
Next i 

It does not give an error, but the columns array is empty,

The whole function is here:

Public Function ReadCSVFileInToArray(FilePath) 
     
    ' Define variables 
    Dim FileContent As String 
    Dim RowsArray() As String 
   
    ' Open file and read contents 
    Open FilePath For Input As #1 
    FileContent = Input(LOF(1), 1) 
    Close #1 
     
    ' Split file content into rows 
    RowsArray = Split(FileContent, vbCrLf) 
     
    ' Split rows into columns 
    Dim i As Long 
    For i = LBound(RowsArray) To UBound(RowsArray) 
        ColumnsArray = Split(RowsArray(i), ",") 
    Next i 
    ReadCSVFileInToArray = ColumnsArray 
End Function 

I suspect both RowsArray and ColumnsArray need to be redimensioned, but how do I know the dimensions before splitting them?

It seems like this should be easy, so I'm clearly not understanding something. I can't even find an intelligible explanation on the web.

Answer 1

Return the Values of a CSV File in a 2D Array

An Example (Usage)

Sub Test()

    Const FILE_PATH As String = "C:\Test\Test.csv"
    Const ROW_DELIMITER As String = vbCrLf ' vbLf
    Const COL_DELIMITER As String = "," ' ";"
    
    Dim sArr: sArr = TextFileToArray(FILE_PATH, ROW_DELIMITER)
    If IsEmpty(sArr) Then Exit Sub
    
    Dim Data(): Data = GetSplitArray(sArr, COL_DELIMITER)
    
    ' Print to the Immediate window (Ctrl+G).
    PrintData Data
    
    ' Write to the worksheet.
    'With Sheet1.Range("A1")
    '    .Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    'End With
    
End Sub

• You can find the PrintData procedure here.

Rows to 1D Array

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns each line of a text file in an element
'               of a 1D zero-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function TextFileToArray( _
    ByVal FilePath As String, _
    Optional ByVal LineSeparator As String = vbLf) _
As Variant

    Dim TextFile As Long: TextFile = FreeFile
    
    Dim sArr() As String
    
    Open FilePath For Input Access Read As TextFile
        On Error Resume Next
            sArr = Split(Input(LOF(TextFile), TextFile), LineSeparator)
        On Error GoTo 0
    Close TextFile

    Dim n As Long
    
    For n = UBound(sArr) To LBound(sArr) Step -1
        If Len(sArr(n)) > 0 Then Exit For
    Next n
    
    If n < LBound(sArr) Then Exit Function
    If n < UBound(sArr) Then ReDim Preserve sArr(0 To n)
    
    TextFileToArray = sArr

End Function

Split 1D Array to 2D Array

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the split values of each element of a 1D array
'               in a row of a 2D one-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetSplitArray( _
    ByVal SourceArray As Variant, _
    Optional ByVal ColumnDelimiter As String = ",") _
As Variant

    Dim rDiff As Long: rDiff = 1 - LBound(SourceArray)

    Dim rCount As Long: rCount = UBound(SourceArray) + rDiff
    Dim cCount As Long: cCount = 1
    
    Dim Data(): ReDim Data(1 To rCount, 1 To cCount)
    
    Dim rArr() As String, r As Long, c As Long, cc As Long, rString As String
    
    For r = 1 To rCount
        rString = SourceArray(r - rDiff)
        If Len(rString) > 0 Then
            rArr = Split(rString, ColumnDelimiter)
            cc = UBound(rArr) + 1
            If cc > cCount Then
                cCount = cc
                ReDim Preserve Data(1 To rCount, 1 To cCount)
            End If
            For c = 1 To cc
                Data(r, c) = rArr(c - 1)
            Next c
        End If
    Next r

    GetSplitArray = Data

End Function

Answer 2

Let excel do the work

Public Function ReadCSVFileInToArray(FilePath)
     
    Dim wb As Workbook
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(FilePath, ReadOnly:=True)
    ReadCSVFileInToArray = wb.Sheets(1).UsedRange.Value
    wb.Close
    Application.ScreenUpdating = True

End Function

Split the first line to get the 2nd dimension.

Public Function ReadCSVFileInToArray(FilePath)
     
    ' Define variables
    Dim FileContent, RowsArray, ColumnsArray
    Dim ar()
   
    ' Open file and read contents
    Open FilePath For Input As #1
    FileContent = Input(LOF(1), 1)
    Close #1
     
    ' Split file content into rows
    RowsArray = Split(FileContent, vbCrLf)
    
    ' Split header row into columns
    ColumnsArray = Split(RowsArray(0), ",")
     
    ReDim ar(1 To UBound(RowsArray) + 1, 1 To UBound(ColumnsArray) + 1)
     
    ' Split rows into columns
    Dim i As Long, j As Long
    For i = LBound(RowsArray) To UBound(RowsArray)
        ColumnsArray = Split(RowsArray(i), ",")
        For j = 0 To UBound(ColumnsArray)
            ar(i + 1, j + 1) = ColumnsArray(j)
        Next
    Next i
    ReadCSVFileInToArray = ar
   
End Function

Answer 3

I adjusted your code and added explanations (see the comments) in the code as well

Option Explicit

Public Function ReadCSVFileInToArray(FilePath)
     
    ' Define variables
    Dim FileContent As String
    Dim RowsArray() As String
    Dim ColumnsArray() As String
    Dim vDat As Variant
   
    ' Open file and read contents
    Open FilePath For Input As #1
    FileContent = Input(LOF(1), 1)
    Close #1
     
    ' Split file content into rows
    RowsArray = Split(FileContent, vbCrLf)
    
    ' Redim the 1st dimension to have space for all rows
    Dim rowNo As Long
    rowNo = UBound(RowsArray)
    ReDim ColumnsArray(0 To rowNo, 0)
     
    ' Split rows into columns
    Dim i As Long, j As Long
    For i = LBound(RowsArray) To UBound(RowsArray)
        vDat = Split(RowsArray(i), ";")
        
        ' This will skip lines with no data especially last one if it only contains a CRLF
        If UBound(vDat) > 0 Then
            
            ' Redim the 2nd dimension to have space for all columns
            Dim colNo As Long
            colNo = UBound(vDat)
            ' Redim will preserve and fortunately we only have to change the last dimension
            ' If you use the Preserve keyword, you can resize only the last array dimension
            ' and you can't change the number of dimensions at all.
            ReDim Preserve ColumnsArray(rowNo, colNo)

            ' you have to copy element by element
            For j = 0 To colNo
                ColumnsArray(i, j) = vDat(j)
            Next j
            
        End If
    Next i
    
    ReadCSVFileInToArray = ColumnsArray
End Function

You can test it with

Sub testIt()
Dim vDat As Variant

    vDat = ReadCSVFileInToArray("filepath")
    Dim rg As Range
    Set rg = Range("A1")
    ' Resize the range to the size of the array
    Set rg = rg.Resize(UBound(vDat, 1), UBound(vDat, 2))
    rg = vDat
End Sub

The better way to import text files into Excel is Powerquery though because you have more control regarding data type etc.