Using VBA to read a .csv string into a multidimensional array

Question

I'm trying to automate the import of data into a tool I'm building in Excel. The idea is to read the data from a .csv file either directly into an array, or read the data as a string and then parse it using spaces " " and commas "," as delimiters, followed by an array. I've gotten this far:

Public Sub ImportData()
    Dim myData as String, strData() as String
    Dim thisFile as String

    thisFile = ActiveWorkbook.Path & "\" & "s.csv"
    Open thisFile For Binary As #1
    myData = Space$(LOF(1))
    Get #1, , myData
    Close #1
End Sub

This gets me to where "myData" is a now string of data separated by commas and spaces (commas delimiting for a new column, spaces delimiting for a new row).

How do I proceed to reconstruct this as a multidimensional (2D) array so that it can be printed onto the sheet I'm working on, or referenced straight from memory? Or is there an easier way?

Split() on spaces (are those really not newlines? - that would be an odd format for a csv file) to give you an array of "lines". Then split each line on comma (assuming you have no field values which might contain commas?) to get individual values in each line. — Tim Williams, Jul 18 '17 at 21:38
Are you certain you want a dynamic array? Stated differently, do you want more than two "columns" of data? That seems pretty unusual for CSV work, unless you're just building header rows and columns. Perhaps you want a 2d array with two columns of data, but a dynamic row? It would probably be easiest if you posted what string you have, and what you're expecting the array to contain (i.e. strdata(0,0)= xx strdata(0,1) = xxxxx — pgSystemTester, Jul 19 '17 at 02:20
check here for an an idea about importing text (csv) file .... https://stackoverflow.com/questions/10434335/text-file-in-vba-open-find-replace-saveas-close-file#10434907 — jsotola, Jul 19 '17 at 02:51

paul bica · Answer 1 · 2017-07-19T03:06:04.147

This is the implementation suggested by @Tim

Option Explicit

Public Sub OpenFile()
    Dim rawData As String, lineArr As Variant, cellArr As Variant
    Dim ubR As Long, ubC As Long, r As Long, c As Long

    Open ActiveWorkbook.Path & "\" & "s.csv" For Binary As #1
    rawData = Space$(LOF(1))
    Get #1, , rawData
    Close #1

    If Len(rawData) > 0 Then

        'If spaces are delimiters for lines change vbCrLf to " "
        lineArr = Split(Trim$(rawData), vbCrLf)

        ubR = UBound(lineArr) + 1
        ubC = UBound(Split(lineArr(0), ",")) + 1
        ReDim arr(1 To ubR, 1 To ubC)

        For r = 1 To ubR
            If Len(lineArr(r - 1)) > 0 Then
                cellArr = Split(lineArr(r - 1), ",")
                For c = 1 To ubC
                    arr(r, c) = cellArr(c - 1)
                Next
            End If
        Next
        ActiveSheet.Range(Cells(1), Cells(ubR, ubC)) = arr 'Place array on the sheet
    End If
End Sub

Using VBA to read a .csv string into a multidimensional array

1 Answers1