We help IT Professionals succeed at work.

VBA CSV Remove multiple commas

dnt2009
dnt2009 used Ask the Experts™
on
Hi,

I've written a macro that converts an excel worksheet into a csv file. The the Excel worksheet, the lines don't have the same number of columns. When the file is converted into csv, the system adds commas at the end of each line to make up of the empty column.
Please see bellow:
1 - The Excel data
2 - The code
3 - Resulting csv file


1 - The Excel data

H      CITCLULL      CSV535A      NEWM      12345      10212      20100705      20100705083748
D      12975812      0      4600000      1.0049                  
T      1      0.00                              


2 - The code

Sub CreateHoldingsCSVFile()
'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Holdings").Select
    Sheets("Holdings").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Sheets("MACRO").Select
    Range("A1").Select
   
End Sub

3 - Resulting csv file

H,CITCLULL,CSV535A,NEWM,12345,10212,20100705,20100705083431
D,12975812,0,4600000,1.0049,,,
T,1,0.00,,,,,

Thanks for your help.
D
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
CSV is a intermediate format used for transferring database info where each row normally has the same number of fields.  If you want to remove the extra commas, you will have to re-open the file as a text file and remove them.

Author

Commented:
Thanks for you comment Dave.
Would you have codes to do so?

Thanks,
D
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
No, I don't but if you 'Request Attention' and get the VB.NET or Visual Basic zones added to your question, I'm sure someone will.  It's probably a regular expression of some sort but I'm still struggling with those.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
dnt2009,

Where is the CSV intended to be used?
As far as I know, all systems take CSV should handle the same number of fields.
When you say, remove multiple commas, is that just at the end or ANY set of duplicate commas?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
As far as I can tell, if you open the resultant CSV in Excel, the extra commas do not get displayed and are handled correctly.  Any gaps in the data columns are correctly exported as nothing between two commas.

Author

Commented:
Thie CSV file is used to be uploaded on a thirdparty system. My csv file fails upload because of the commas at the end of the lines. I need to remove the extra commas at the end of each line not commas within the lines.

How do I add zones to my question?

Thanks,
D
Expert of the Quarter 2010
Expert of the Year 2010

Commented:

Sub CreateHoldingsCSVFile()
'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Holdings").Select
    Sheets("Holdings").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
'    Sheets("MACRO").Select
'    Range("A1").Select
   
End Sub

Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
    
    For r = 1 To Range("A" & Rows.Count).End(xlUp).Row
    s = ""
        For c = 1 To UsedRange.Columns.Count
            If Trim(Cells(r, c).Value) <> "" Then
            If InStr(Cells(r, c), ",") > 0 Then
                s = s & """" & Replace(Cells(r, c), """", """""") & ""","
            Else
                s = s & Cells(r, c) & ","
            End If
            End If
        Next c
    If s <> "" Then s = Left(s, Len(s) - 1)
    a.writeline s 'write line
Next r
   
End Sub

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Correction
Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
    
    For r = 1 To UsedRange.Rows.Count
        s = ""
        For c = 1 To UsedRange.Columns.Count
            If InStr(Cells(r, c), ",") > 0 Then
                s = s & """" & Replace(Cells(r, c), """", """""") & ""","
            Else
                s = s & Cells(r, c) & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub

Open in new window

Author

Commented:
thanks Cyberkiwi,
I tried your code. i run into the following issus: variables r anc have to be defined (Dim r,c). I defined UsedRange as range but the macro bugs on the line where UsedRange is used.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Macro version - references workbook, and Option Explicit safe.
Option Explicit
Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
    
    Dim r As Long
    Dim c As Long
    Dim ws As Worksheet
    Set ws = Sheets("HOLDINGS")
    For r = 1 To ws.UsedRange.Rows.Count
        s = ""
        For c = 1 To ws.UsedRange.Columns.Count
            If InStr(ws.Cells(r, c), ",") > 0 Then
                s = s & """" & Replace(ws.Cells(r, c), """", """""") & ""","
            Else
                s = s & ws.Cells(r, c) & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub

Open in new window

Commented:
After loading your file in Excel, TryFile --> SaveAS-->and then select CSV the file type. This should remove all commas at the end.

Author

Commented:
Hi Nepaluz,
That doesn't work. When opening the csv file in Notepad, I can still see the commas.

Author

Commented:
Hi cyberkiwi:,

FANTASTIC. IT WORKS!!!!
Except that it's changing my date format from YYYYMMDD to M/D/YYYY which caused the uplaod to fail!

Thanks a million.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Added check for dates, exported as YYYYMMDD

Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
   
    Dim r As Long
    Dim c As Long
    Dim ws As Worksheet
    Set ws = Sheets("HOLDINGS")
    For r = 1 To ws.UsedRange.Rows.Count
        s = ""
        For c = 1 To ws.UsedRange.Columns.Count
            If TypeName(ws.Cells(r, c).Value) = "Date" Then
                s = s & Year(ws.Cells(r, c)) & Right(100 + Month(ws.Cells(r, c)), 2) & Right(100 + Day(ws.Cells(r, c)), 2) & ","
            ElseIf InStr(ws.Cells(r, c), ",") > 0 Then
                s = s & """" & Replace(ws.Cells(r, c), """", """""") & ""","
            Else
                s = s & ws.Cells(r, c) & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub

Author

Commented:
thanks cyberkiwi,

I forgot to mention that last column in first row has the following date format
coulmn 7 has date format YYYYMMDD and column 8 YYYYMMDDHHMMSS. As shown below.

H      CITCLULL      CSV535A      NEWM      12345      10212      20100705      20100705083748
                     
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Are those columns 7 and 8 stored as NUMBERS, TEXT or DATE (with formatting)?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Try this
Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
    
    Dim r As Range
    Dim c As Range
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("HOLDINGS")
    For Each r In ws.UsedRange.Rows
        s = ""
        For Each c In r.Cells
            If c.Column = 7 Or c.Column = 8 Then
                s = s & c.Text & ","
            ElseIf InStr(c.Value, ",") > 0 Then
                s = s & """" & Replace(c.Value, """", """""") & ""","
            Else
                s = s & c.Value & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub

Open in new window

Author

Commented:
they are stored in date format using datevalue(text) formula.
the dates are ok now. However, last datat in line 2 has double quotes. Do you know why this happens?

H,CITCLULL,CSV535A,NEWM,12345,10212,20100705,20100705173337
D,12975812,0,4600000,"1,0049"
T,1,0
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
CSV format is broken up by commas.
If you have comma in your data ,then they have to be double-quoted to be recognized as a single item.
If you had a note that said "ok, tomorrow", and you don't quote it in a CSV file, you would end up with an extra field.

Author

Commented:
Hi Cyberkiwi,
Many thanks for your valuable help. You have saved me weeks of work.

Regards,
D
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
No problem.  Glad to have helped!

Author

Commented:
Hi again Cyberkiwi,

I have issues with date formatting when running the macro. Column 8 is not in the right format.
Column 7 date format should be YYYYMMDD using Today function
Column 8 date format should be YYYYMMDDHHMMSS using Now function
H      CITCLULL      CSV535A      NEWM      12345      10212      20100705      20100705083748

thanks for your help.
D
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Have you got some sample data rows showing values for columns 7 and 8?
Make up fake data for the other rows.
Are there date values like 2/5/2010 instead of already 20100502 ?

Author

Commented:
I need to create 2 different files with different specifications.
File1 needs dates in the Header: Column 7 and Column 8. I'm not retreiving data from any other source. I just use the formulas. Here are the codes for those columns.
'G-Statement Date
    Range("G1").Value = "=today()"
    Range("G1").Select
    Selection.NumberFormat = "YYYYMMDD"
   
    'H-Preparation Date and Time
    Range("H1").Value = "=now()"
    Range("H1").Select
    Selection.NumberFormat = "YYYYMMDDHHMMSS"

The result before CSV is fine
H      CITCLULL      CSV535A      NEWM      12345      10212      20100705      20100705083748

After CVS this is what I get. Column 7 is ok, but column 8 has been cut from YYYYDDHHMMSS to YYYYMMDD.

H,CITCLULL,CSV535A,NEWM,12345,10212,20100707,20100707

The second file is more complex I'll prepare a few sample to send to you.

Thanks,
D
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
I put these lines at the beginning of the sub to export to csv

    Range("G1").Value = "=today()"
    Range("G1").Select
    Selection.NumberFormat = "YYYYMMDD"
   
    'H-Preparation Date and Time
    Range("H1").Value = "=now()"
    Range("H1").Select
    Selection.NumberFormat = "YYYYMMDDHHMMSS"

And the resultant CSV had the right format for columns 7 and 8.  Just to be clear, this is what I tried.
Sub csvfile()

'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", True)
    
    Dim r As Range
    Dim c As Range
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("HOLDINGS")

''''''test
    ws.Range("G1").Value = "=today()"
    'Range("G1").Select
    'Selection.NumberFormat = "YYYYMMDD"
    ws.Range("G1").NumberFormat = "YYYYMMDD"
   
    'H-Preparation Date and Time
    ws.Range("H1").Value = "=now()"
    'Range("H1").Select
    'Selection.NumberFormat = "YYYYMMDDHHMMSS"
    ws.Range("H1").NumberFormat = "YYYYMMDDHHMMSS"
''''''test

    For Each r In ws.UsedRange.Rows
        s = ""
        For Each c In r.Cells
            If c.Column = 7 Or c.Column = 8 Then
                s = s & c.Text & ","
            ElseIf InStr(c.Value, ",") > 0 Then
                s = s & """" & Replace(c.Value, """", """""") & ""","
            Else
                s = s & c.Value & ","
            End If
        Next c
        If s <> "" Then
            While Right(s, 1) = ","
                s = Left(s, Len(s) - 1)
            Wend
            a.writeline s 'write line
        End If
    Next r
End Sub

Open in new window

Author

Commented:
Thanks cyberkiwi you are definitely wizard!
The macro works perfectly for my first file. However I can't update it for the second file.
The header dates are as following: user are prompted to input dates (Columns 7 and 8), Column 9's value is the NOW function.  
The following lines hold data retrieved from a system downloaded file: Columns 5 and 6. The original data is in text format so I've used the DATEVALUE function to convert them to date format.

This is how the file should look like:
H,CTU,CSV536A,NEWM,12345,10212,20100628,20100702,20100708083334,
D,0457025616,91.162,DELI,20100629,20100531,,13010312,USD,10994.28
D,0457025616,91.162,RECE,20100629,20100531,,13010412,USD,10994.28
D,0457025616,182.1903,RECE,20100525,20100601,,12976712,USD,21972.44
D,0457025616,378.8718,RECE,20100525,20100601,,12976812,USD,45692.55
T,4,743.3861

But this is what I get when running the csv macro:
H,CITCLULL,CSV536A,NEWM,12345,10212,20100708,20100708,7/8/2010 8:51:16 AM
D,LU0457025616,91.162,DELI,6/29/2010,5/31/2010,,13010312,USD,10994.28
D,LU0457025616,91.162,RECE,6/29/2010,5/31/2010,,13010412,USD,10994.28
D,LU0457025616,182.1903,RECE,5/25/2010,6/1/2010,,12976712,USD,21972.44
D,LU0457025616,378.8718,RECE,5/25/2010,6/1/2010,,12976812,USD,45692.55
T,4,743.3861

Here are the codes I'm using to create the file
   'G-From Date
    FromDate = InputBox("Please enter From Date. Date Format MM/DD/YYYY")
    Sheets("Transactions").Select
    Range("G1").Value = FromDate
    Range("G1").NumberFormat = "YYYYMMDD"
           
    'H-To Date
    ToDate = InputBox("Please enter To Date. Date Format MM/DD/YYYY")
    Sheets("Transactions").Select
    Range("H1").Value = ToDate
    Range("H1").NumberFormat = "YYYYMMDD"
           
    'I-Preparation Date and Time
    Range("I1").Value = "=now()"
    Range("I1").NumberFormat = "YYYYMMDDHHMMSS"

And I used your latest code the csv the file. I've updated the "test" with the appropriate columns but the result isn't as expected.

Thanks again for your help.
D
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Change around this part

    For Each r In ws.UsedRange.Rows
        s = ""
        For Each c In r.Cells
            If c.Column >= 7 Then
                s = s & c.Text & ","

Instead of

            If c.Column = 7 or c.Column = 8 Then

Author

Commented:
Hi Cyberkiwi.
The macro works beautifully, I changed a few things.
Thanks a million your help has been valuable.

D