Append rows into different csv files based on column value

[number15]

Hi,
I'm looking for a solution for this case scenario.

I have an excel file with in a column some names and some csv files, named with same names.
I'm trying a way to extract the rows from the excel file and append them to the proper csv file.

For example, Excel file:

Name,Col2,Col3,Col4
Pippo,x,x,x
Pippo,y,y,y
Pippo,z,z,z
Pluto,a,f,b
Pluto,a,g,b

And following csv files:
Pippo.csv
Col2,Col3,Col4
a,b,f
x,b,v

Pluto.csv
Col2,Col3,Col4
y,s,b
y,s,g

Result should be
Pippo.csv
Col2,Col3,Col4
a,b,f
x,b,v
x,x,x
y,y,y
z,z,z

Pluto.csv
Col2,Col3,Col4
y,s,b
y,s,g
a,f,b
a,g,b

My knowledge of VBA is 0, but I can try to work on it if it's possible to solve this case.
I'm fine also to buy an add-in if exists.

Thanks

 

[DanteAmor]

Hi and welcome to MrExcel!

For example, Excel file:

If your data is as follows:

varios 11may2022.xlsm
	A	B	C	D
1	Name	Col2	Col3	Col4
2	Pippo	x	x	x
3	Pippo	y	y	y
4	Pippo	z	z	z
5	Pluto	a	f	b
6	Pluto	a	g	b
Sheet1

Put the macro in the excel file. Save the excel in the same folder where the csv files are.

Sub Append_rows_into_csv_files()
  Dim myPath As String, myFile As String, cad As String
  Dim i As Long, j As Long, k As Long, n As Long, m As Long
  Dim a As Variant, b As Variant, ky As Variant
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:D" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
  
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      n = n + 1
      dic(a(i, 1)) = n & "|" & 1
    End If
    n = Split(dic(a(i, 1)), "|")(0)
    m = Split(dic(a(i, 1)), "|")(1)
    b(n, m) = i
    m = m + 1
    dic(a(i, 1)) = n & "|" & m
  Next
  
  myPath = ThisWorkbook.Path & "\"
  For Each ky In dic.keys
    n = Split(dic(ky), "|")(0)
    myFile = myPath & ky & ".csv"
    If Dir(myFile) = "" Then
      Open myFile For Output As #1
    Else
      Open myFile For Append As #1
    End If
    For k = 1 To UBound(b, 2)
      If b(n, k) = "" Then Exit For
      i = b(n, k)         'get row
      cad = ""
      For j = 1 To UBound(a, 2)
        cad = cad & a(i, j) & ","
      Next
      If cad <> "" Then
        cad = Left(cad, Len(cad) - 1)
        Print #1, cad
      End If
    Next
    Close #1
  Next
End Sub

-----
If the csv file exists, then add the records at the end, if the csv file does not exist, then the macro creates a new csv file with the data from the columns.
-----

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Append_rows_into_csv_files) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[number15]

Hi Dante, thank you so much for the script.
I'm looking at it and try to understand what it does in every part.

How does it append the rows to the file? I mean, the columns needs to be same number and same order or it's based on the name of the columns or based on position?

Thanks

 

[DanteAmor]

How does it append the rows to the file?

With this instruction
Open myFile For Append As #1

I mean, the columns needs to be same number and same order or it's based on the name of the columns or based on position?

It always adds 4 columns and now that I see it, you need to add 3 columns (B, C and D).

Change this line:
For j = 1 To UBound(a, 2)

to this:
For j = 2 To UBound(a, 2)

 

[number15]

I can have edge cases where the number and the position of the columns in the starting file are different from the ones in the csv files.
So i was checking if I need to have all the same columns in all the files or it works in a different way (like maybe using column header).

It's just to understand: it's not a big deal to create a macro to add/sort the columns in the original xlsx file.

 

[DanteAmor]

I can have edge cases where the number and the position of the columns in the starting file are different from the ones in the csv files.
So i was checking if I need to have all the same columns in all the files or it works in a different way (like maybe using column header).

It's just to understand: it's not a big deal to create a macro to add/sort the columns in the original xlsx file.

In your initial examples there is none of that. The macro works to add 3 data to the end of the file.

 

[number15]

Ok perfect. I'll edit it to append all the columns.

Everything seems working properly, just a question: how can I change it to avoid to create the file if the csv is missing?

I've trying in this way

If Dir(myFile) <> "" Then
      Open myFile For Append As #1
    End If

but it gives me error here:

Print #1, cad

Any suggestion?
Thanks

 

[DanteAmor]

how can I change it to avoid to create the file if the csv is missing?

Try this:

Sub Append_rows_into_csv_files()
  Dim myPath As String, myFile As String, cad As String
  Dim i As Long, j As Long, k As Long, n As Long, m As Long
  Dim a As Variant, b As Variant, ky As Variant
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:D" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
  
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      n = n + 1
      dic(a(i, 1)) = n & "|" & 1
    End If
    n = Split(dic(a(i, 1)), "|")(0)
    m = Split(dic(a(i, 1)), "|")(1)
    b(n, m) = i
    m = m + 1
    dic(a(i, 1)) = n & "|" & m
  Next
  
  myPath = ThisWorkbook.Path & "\"
  For Each ky In dic.keys
    n = Split(dic(ky), "|")(0)
    myFile = myPath & ky & ".csv"
    If Dir(myFile) <> "" Then
      Open myFile For Append As #1
      For k = 1 To UBound(b, 2)
        If b(n, k) = "" Then Exit For
        i = b(n, k)         'get row
        cad = ""
        For j = 2 To UBound(a, 2)
          cad = cad & a(i, j) & ","
        Next
        If cad <> "" Then
          cad = Left(cad, Len(cad) - 1)
          Print #1, cad
        End If
      Next
      Close #1
    End If
  Next
End Sub

 

[number15]

Seems working properly.

Thank you very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.