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

[airwolf09]

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

 

[dhookom]

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?

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

 

[Andrzejek]

I usually do this:

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 [tt]Option Explicit[/tt] at the top of your code :-(

You may also simply do:

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[airwolf09]

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.

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

 

[Andrzejek]

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.

Dim strCSV As String

strCSV = "Peter, Pan, 20, Male,"[blue]
strCSV = Left(strCSV, Len(strCSV) - 1)[/blue]
Debug.Print strCSV

You get:
[tt]Peter, Pan, 20, Male [/tt] <- last comma eliminated, all other commas stay.

Besides, from your code you should NOT get this;[tt]
Peter, Pan, 20, Male[/tt]
You should get this:[tt]
PETER, PAN, 20, MALE[/tt]

"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.

 

[airwolf09]

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

 

[dhookom]

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

 

[strongm]

Or

[blue]    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"[/blue]

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:
[tt]
[myfile.csv]
TextDelimiter="none"[/tt]