Libre Calc - Populate Cells from CSV string

Question

How do I populate multiple cells from a macro function in Libre Calc from a csv string?

Sample function

Function GetCsv

    Dim csvData As String
    csvData = "column1,column2,column3" & Chr(10) & "1,2,3" & Chr(10) & "4,5,6" & Chr(10) & "7,8,9"
    
    GetCsv = csvData

End Function

I'd like to call the function in cell A1 and in this example have A1:C4 populated with the data. What is the best way to achieve this?

Answer 1

Are you asking about splitting a text string into individual rectangular array elements? I would write it like this:

Function splitStr2Array(sSource As String, Optional sStrDelimiter As String, Optional sColDelimiter As String) As Variant 
Dim aTemp As Variant 
Dim aStr As Variant 
Dim Result As Variant 
Dim i As Long, j As Long, nStrLen As Long 
    If IsMissing(sStrDelimiter) Then sStrDelimiter = Chr(10)
    If IsMissing(sColDelimiter) Then sColDelimiter = ","
    On Error Resume Next 
    aTemp = Split(sSource, sStrDelimiter)
    aStr = Split(aTemp(0), sColDelimiter)
    nStrLen = UBound(aStr)
    ReDim Result(0 To UBound(aTemp), 0 To nStrLen)
    For i = 0 To UBound(aTemp)
        aStr = Split(aTemp(i), sColDelimiter)
        ReDim Preserve aStr(0 To nStrLen)
        For j = 0 To nStrLen
            Result(i, j) = aStr(j)
        Next j
    Next i
    splitStr2Array = Result
End Function

and call it from cell with formula like

=SPLITSTR2ARRAY("column1,column2,column3" & CHAR(10) & "1,2,3" & CHAR(10) & "4,5,6" & CHAR(10) & "7,8,9")}