ADO to CSV

[dr486]

Is it possible to dump an ADO recordset into a CSV file???

 

[dilettante]

Take a look at the Recordset's [tt]GetString()[/tt] method.

 

[dilettante]

There may also be other alternatives depending on the source of the original Recordset.

MDBtoCSV.wsf

<JOB>
  <OBJECT id = objCmd progID = "ADODB.Command"/>
  <REFERENCE object = "ADODB.Command"/>
  <RESOURCE id = resMDBConn>
    Provider=Microsoft.Jet.OLEDB.4.0;
      Data Source="$ScriptPath$\my.mdb"
  </RESOURCE>
  <RESOURCE id = "resSQL">
    SELECT * INTO [final.csv]
             IN "$ScriptPath$" "Text;HDR=YES;FMT=Delimited"
             FROM [mydata]
  </RESOURCE>
  <SCRIPT language = "VBScript">
    Option Explicit

    Function Resource(ByVal ResName)
      Dim strSFN

      strSFN = WScript.ScriptFullName
      Resource = _
        Replace(getResource(ResName), _
                "$ScriptPath$", _
                Left(strSFN, InStrRev(strSFN, "\") - 1))
    End Function

    Const cMyName = "Create CSV file from MDB table"
    Dim lngRecords

    objCmd.ActiveConnection = Resource("resMDBConn")
    objCmd.CommandText = Resource("resSQL")
    objCmd.Execute lngRecords, , adCmdText Or adExecuteNoRecords
    MsgBox "File contains " & _
      CStr(lngRecords) & _
      " records.", 0, cMyName
  </SCRIPT>
</JOB>

This VBScript converts a table "mydata" in "my.mdb" to a csv file "final.csv" using Jet.

 

[Andrzejek]

How about this:

Dim recOne As ADODB.Recordset

Private Sub Command1_Click()

strSQL = "SELECT * From MyTable "

Set recOne = New ADODB.Recordset
recOne.CursorType = adOpenForwardOnly
recOne.CursorLocation = adUseClient
recOne.LockType = adLockReadOnly
recOne.Open strSQL, Cn

recOne.Close

Call MakeSCVoutOfADORst(recOne)

End Sub

Private Sub MakeSCVoutOfADORst(rst As ADODB.Recordset)
Dim intF As Integer
Dim intR As Integer
Dim intFF As Integer
Dim str As String

intFF = FreeFile

rst.Open
Open App.Path & "\MyTextFile.csv" For Output As intFF

For intR = 1 To rst.RecordCount
    For intF = 0 To rst.Fields.Count - 1
        If Len(str) = 0 Then
            str = rst.Fields(intF).Value
        Else
            str = str & "," & rst.Fields(intF).Value
        End If
    Next intF
    
    Print #intFF, str
    str = ""
    rst.MoveNext
Next intR

Close intFF

rst.Close

End Sub

Have fun.

---- Andy

 

[JerryKlmns]

Another way (maybe faster)

Dim rst As ADODB.Recordset
Dim fso As Object 'Scripting.FileSystemObject
Dim ts As Object 'Scripting.TextStream

Set rst = New ADODB.Recordset
With rst
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = "SELECT * From MyTable"
    .Open
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(CurrentProject.Path & "\myCSVFile.txt", 2, True) 'ForWriting
    Do While Not .EOF
      ts.WriteLine .GetString(adClipString, , ",", vbCrLf)
    Loop
    .Close
End With
Set rst = Nothing
ts.Close
Set ts = Nothing
Set fso = Nothing