Using VBA to remove excess comma rows from a recently converted .csv file

Question

I currently extract a file from a source system and I get a .csv file. My process requires me to save this .csv as a .txt file so it can be uploaded to another system.

I have written VBA code which does this automatically for a list of .csv files.

However, the .txt files have lots of empty rows (denoted as commas) underneath the data. I have checked this and it is not real data, i.e., there are no spaces here. I am assuming it is a sort of metadata. But it is causing me problems as I need to remove all the bottom commas before it can be uploaded.

Here is an example of what my .txt file look like after being converted from .csv:

File name,dim1,blank,dim2
file 1,1,,apple
file 1,2,,orange
file 1,3,,banana
,,
,,
,,
,,

I need it to look like this:

File name,dim1,blank,dim2
file 1,1,,apple
file 1,2,,orange
file 1,3,,banana

I am aware of this answer here. But this doesn't solve the problem as I am left with no commas but empty space below the data so it can't be used as an upload.

    fn = Application.GetOpenFilename(FolderPath & FileName & ".txt")
    If fn = "" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True: .MultiLine = True
        .Pattern = ",+$"
        Open Replace(fn, ".txt", "_Clean.txt") For Output As #1
            Print #1, .Replace(txt, "")
        Close #1
    End With

The end result would then look like this which is not suitable:

File name,dim1,blank,dim2
file 1,1,,apple
file 1,2,,orange
file 1,3,,banana

Here is the code which creates the .txt files from the source .csv files. I am not tied to this approach so happy to change if it removes the commas

Sub CreateTextFiles()
Application.ScreenUpdating = False

Dim rng As Range
Set rng = Range("A1:A4")
Dim FileName As String
Dim FolderPath As String
FolderPath = Range("C1").Value

For Each cell In rng
    FileName = cell.Value
    'THIS WORKS BUT THE COMMAS ARE STILL PRESENT
    FileCopy FolderPath & FileName & ".csv", FolderPath & FileName & ".txt"
    
    'OPENS A NOTEPAD
    'Shell "notepad.exe " & FolderPath & FileName & ".csv", vbNormalFocus
Next cell

Application.ScreenUpdating = True
MsgBox "Text files have been created"
End Sub

Answer 1

EDIT: removed trailing newline from last output line.

Here's one way you could do it:

Sub Tester()

    ProcessCSV "C:\Temp\Test.csv"

End Sub

Sub ProcessCSV(f As String)
    Dim fso As Object, strmIn As Object, strmOut As Object, l As String
    Dim col As New Collection, i As Long
    Set fso = CreateObject("scripting.filesystemobject")
    
    'open input and output streams
    Set strmIn = fso.OpenTextFile(Filename:=f, IOMode:=1)
    Do While Not strmIn.AtEndOfStream 'loop through the input file lines
        l = strmIn.ReadLine
        If Len(Replace(l, ",", "")) > 0 Then col.Add l 'save line if not just commas
    Loop
    strmIn.Close   'close stream
    
    Set strmOut = fso.OpenTextFile(Filename:=Replace(f, ".csv", "_clean.txt", Compare:=vbTextCompare), _
                                   IOMode:=2, Create:=True)
    For i = 1 To col.Count - 1
        strmOut.WriteLine col(i)  'write all lines except last
    Next i
    strmOut.Write col(col.Count)  'last line with no newline
    strmOut.Close                 'close stream
End Sub