INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating a CSV file and removing the last comma from each row

Creating a CSV file and removing the last comma from each row

(OP)
Good Afternoon

The following code creates a .csv file.

The output looks something like this

Peter, Pan, 20, Male,
Mary, Pan, 20, Female,
John, Doe, 40, Male,

The problem is the last comma on each row. It should no be there. Or I need to delete it.
I can't figure out how to remove the last comma character from each row. Do you have any suggestions?

Function Process_CSV()

'Export csv file

Dim trz As Integer
Dim strCSV As String

For trz = 1 To 511
Close #trz
Next trz
trz = FreeFile

Set fso = CreateObject("Scripting.FileSystemObject")
folderFilePath = "C:\MyDocuments\"

Open "C:\MyDocuments\MyFile.csv" For Output Access Write As #trz

With CurrentDb.OpenRecordset("tbl_TEMP_ARCHIVE_ASSETS")
'Dim x As Integer
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & ", "
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)

Do Until .EOF
strCSV = ""
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "") & ", "
strCSV = StrConv(strCSV, vbUpperCase)
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
.MoveNext
Loop
End With
Close #trz

End Function

Thank You

RE: Creating a CSV file and removing the last comma from each row

Please use TGML to format your code to make it worth reading. What is strColumnDelimiter? I don't see where this is defined. Does your code compile?

CODE --> vba

Function Process_CSV()

    'Export csv file

    Dim trz As Integer
    Dim strCSV As String

    For trz = 1 To 511
        Close #trz
    Next trz
    trz = FreeFile

    Set fso = CreateObject("Scripting.FileSystemObject")
    folderFilePath = "C:\MyDocuments\"

    Open "C:\MyDocuments\MyFile.csv" For Output Access Write As #trz

    With CurrentDb.OpenRecordset("tbl_TEMP_ARCHIVE_ASSETS")
        'Dim x As Integer
        For x = 0 To .Fields.Count - 1
            strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & ", "
        Next x
        Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)

        Do Until .EOF
            strCSV = ""
            For x = 0 To .Fields.Count - 1
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "") & ", "
                strCSV = StrConv(strCSV, vbUpperCase)
            Next x

            Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
            .MoveNext
        Loop
    End With
    Close #trz
End Function 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Creating a CSV file and removing the last comma from each row

I usually do this:

CODE

With CurrentDb.OpenRecordset("tbl_TEMP_ARCHIVE_ASSETS")
    'Dim x As Integer
    For x = 0 To .Fields.Count - 1
        If x < .Fields.Count - 1 Then
            strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & ", "
        Else
            strCSV = strCSV & strColumnDelimiter & .Fields(x).Name
        End If
    Next x
    Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
    
    Do Until .EOF
        strCSV = ""
        For x = 0 To .Fields.Count - 1
            If x < .Fields.Count - 1 Then
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "") & ", "
            Else
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "")
            End If
            strCSV = StrConv(strCSV, vbUpperCase)
        Next x
        Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
        .MoveNext
    Loop
End With 

Looks to me you also had an extra comma at the end of your header row...
And I would guess you do not have Option Explicit at the top of your code sad

You may also simply do:

CODE

strCSV = Left(strCSV, Len(strCSV) - 1) 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Creating a CSV file and removing the last comma from each row

(OP)
Andrzejek, Thank You for your input

strCSV = Left(strCSV, Len(strCSV) - 1)
Removes all commas, defeating the purpose. I only need the last comma to be removed.

CODE --> vba

With CurrentDb.OpenRecordset("tbl_TEMP_ARCHIVE_ASSETS")
    'Dim x As Integer
    For x = 0 To .Fields.Count - 1
        If x < .Fields.Count - 1 Then
            strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & ", "
        Else
            strCSV = strCSV & strColumnDelimiter & .Fields(x).Name
        End If
    Next x
    Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
    
    Do Until .EOF
        strCSV = ""
        For x = 0 To .Fields.Count - 1
            If x < .Fields.Count - 1 Then
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "") & ", "
            Else
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "")
            End If
            strCSV = StrConv(strCSV, vbUpperCase)
        Next x
        Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
        .MoveNext
    Loop
End With 


The code works but the last row show information missing. For example

Peter, Pan, 20, Male
Mary, Pan, 20, Female
John, Doe,

Any input on this?

Thank You

RE: Creating a CSV file and removing the last comma from each row

"strCSV = Left(strCSV, Len(strCSV) - 1)
Removes all commas, defeating the purpose. I only need the last comma to be removed."
No, it does not.

CODE

Dim strCSV As String

strCSV = "Peter, Pan, 20, Male,"
strCSV = Left(strCSV, Len(strCSV) - 1)
Debug.Print strCSV 
You get:
Peter, Pan, 20, Male <- last comma eliminated, all other commas stay.

Besides, from your code you should NOT get this;
Peter, Pan, 20, Male

You should get this:
PETER, PAN, 20, MALE


"The code works but the last row show information missing"
Step thru the code and SEE what's going on. You should go thru the same lines of code for all records in your recordset, including the last record.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Creating a CSV file and removing the last comma from each row

(OP)
Andrzejek, Thank You for your help. You are right.
It works perfect!

RE: Creating a CSV file and removing the last comma from each row

airwolf09,

Consider marking the correct post from Andy as "Great post!" so others know your question has been answered and Andy gets a star.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Creating a CSV file and removing the last comma from each row

Or

CODE

    Dim Destination As String
    Dim SQLExport As String
    Destination = "C:\MyDocuments\"
    SQLExport = "Select * from tbl_TEMP_ARCHIVE_ASSETS"
    CurrentDb.CreateQueryDef "TempExport", SQLExport
    DoCmd.TransferText acExportDelim, , "TempExport", Destination & "myfile.csv", True
    DoCmd.DeleteObject acQuery, "TempExport" 

And if you don't like the defaults that Access uses for the text export, then simply create a short schema.ini in Destination with the lines shown below, which should give you the same export as all the code solutions shown above:

[myfile.csv]
TextDelimiter="none"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close