Formatting exported CSV file

[oatsybob]

Hello!

I'm working on a bit of code that queries a table for cell phone numbers and then saves the results of the query externally as a csv file to be accessed by a python script. I am able to export the data as a csv file, however when it saves the numbers are formatted like:

xxxxxxxxxx
xxxxxxxxxx
xxxxxxxxxx
xxxxxxxxxx

when I need them to be saved like:

xxxxxxxxxx,xxxxxxxx,xxxxxxxxx,xxxxxxxxx,xxxxxxx, etc.

Here's my code

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


Dim db: db = "C:\Users\Michael\Desktop\Kelly_be.mdb"
Dim exportDir: exportDir = "C:\Python27"
Dim exportFile: exportFile = "test3.csv"
Dim strPath As String


strPath = "C:\Python27\test3.csv"


If Dir(strPath) <> "" Then
    Kill (strPath)
End If


Dim cn: Set cn = CreateObject("ADODB.Connection")


cn.Open _
    "Provider = Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source =" & db


cn.Execute "SELECT Kids.[Phone_Secondary] INTO [text;HDR=YES;Database=" & exportDir & _
   ";CharacterSet=65001]." & exportFile & " FROM Kids WHERE Kids.[Phone_Secondary] <> False"




Exit_Command13_Click:
    Exit Sub


Err_Command13_Click:
    MsgBox Err.Description
    Resume Exit_Command13_Click
    
End Sub

Any thoughts/guidance would be greatly appreciated! I've only ever gotten great help from this site in the past.

[June7]

You need to concatenate data from multiple records into a single comma separated string?

Review: http://allenbrowne.com/func-concat.html

[oatsybob]

Almost, but not exactly. My problem is less complicated. My query outputs one column in a table which I want to concatenate into a single comma separated string.

[June7]

That is what Allen's code produces, a comma-delimited string of data from multiple records. This is the only way I know to accomplish. Adapt and refine the code to suit your situation. Gist of the procedure is to open a recordset and loop through the records building string.