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
– Tim Williams, Commented Jul 18, 2017 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
– pgSystemTester, Commented Jul 19, 2017 at 2:20
check here for an an idea about importing text (csv) file .... stackoverflow.com/questions/10434335/… — jsotola
– jsotola, Commented Jul 19, 2017 at 2:51

paul bica · Accepted Answer · 2017-07-19 03:06:04Z

1

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

edited Jul 19, 2017 at 3:06

answered Jul 19, 2017 at 2:52

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mike01010 Over a year ago

is this still the best way today? is there not a function that an more efficiently/quickly insert a csv formatted string with line returns into a range?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related