CODEWITHSUNDEEP
excelvbacsvexport-to-csv
Kasandra Murray
Kasandra Murray

Reputation: 65

How to export each individual excel row to its own individual csv file?

I am trying to build a variable data report in Adobe Illustrator. This report will have graphs and data for each individual listed in an excel/csv file. To be able to generate the graphs there needs to be a .csv file of the data only pertaining to the graph relevant to that specific individual.

So for example I have three individuals: John, Joe, and Ann.

Each individual will have a bar graph showing their growth in communication, and they will also have a graph showing their growth in confidence.

Each individual needs a csv for both graphs. Which means I will need a total of 6 csv files to start building the variable data report in excel.

Obviously all of the data will be together in one excel file that I will need to separate out and save as the individual csv files.

Is there a script I can use that will export each row (representative of the individual) as it's own separate csv file and name it based on the data in cell A2 and B1?

I have done some research and I have found some solutions that only reference the columns or how to export selected rows, but I will need to do this for 100+ individuals and these solutions will not work for the volume I need them to. I am somewhat familiar with VBA coding, but I am having a hard time finding anything that can help with this issue.

So if my cells look like this:

    A       B               C
1   Name    Communication   Communication End
2   John    20              25
3   Joe     16              18
4   Ann     23              27

My file names will look like this: John_Communication.csv, Joe_Communication.csv, Ann_Communication.csv

And will only export the data from each individual row as it's own csv file so that I can import the data into Adobe Illustrator to create the graphs I need for the individual's reports?

Upvotes: 1

Views: 1962

Answers (1)

Logan Reed
Logan Reed

Reputation: 902

One way to approach this is by writing out csv files from VBA macro:

Sub SaveRowsToSeparateCsv()

    Dim h As Range       ' header
    Dim d As Range       ' data
    Dim r As Range       ' row
    Dim c As Range       ' cell

    Dim i As Long

    Dim ha2d As Variant  ' header 2d array
    Dim ha1d As Variant  ' header 1d array
    Dim hcsv As String   ' header in csv format

    Dim da2d As Variant  ' data 2d array
    Dim da1d As Variant  ' data 1d array
    Dim dcsv As String   ' data in csv format

    ' init ranges with header and data
    With [a1].CurrentRegion
        Set h = .Rows(1)
        Set d = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
    End With

    ' we'll need header multiple times, so lets prepare it
    ha2d = h.Value
    ReDim ha1d(LBound(ha2d, 2) To UBound(ha2d, 2))
    For i = LBound(ha1d) To UBound(ha1d)
        ha1d(i) = ha2d(1, i)
    Next
    hcsv = Join(ha1d, ",") & vbNewLine

    ' now lets go through each row and save it seaprately as a csv
    For Each r In d.Rows
        da2d = r.Value
        ReDim da1d(LBound(da2d, 2) To UBound(da2d, 2))
        For i = LBound(da1d) To UBound(da1d)
            da1d(i) = da2d(1, i)
        Next
        dcsv = hcsv & Join(da1d, ",") & vbNewLine

        saveFile ThisWorkbook.Path & Application.PathSeparator _
                 & r.Cells(1) & "_Communication.csv", dcsv
    Next r

End Sub

Sub saveFile(pathname As String, sText As String)
    Dim fNum As Integer: fNum = FreeFile
    Open pathname For Output As fNum
    Print #fNum, sText;
    Close #fNum
End Sub

Upvotes: 2

Related Questions