VB 6.0: Export Recordset to a Text File

[Shuja Ali]

A recordset object in ADO library has Save method for saving the data directly into a file (XML/BInary) however if one needs to export all the data into a delimeted file then GetString method of the recordset will be very useful.

Here is a function that can be used to export an existing recordset to a delimeted file

'Function: WriteRecordsetToFile
'Input :
'   rsExport -- The recordset that needs to be exported
'   fileName -- Name of the file to which the recordset data will be saved
'   cDelimeter -- The delimeter that will be used to separate columns
'Output
'   Returns True if the process was successfull
'   Returns False if the process failed
Public Function writeRecordsetToFile(ByVal rsExport As ADODB.Recordset, _
        fileName As String, cDelimeter As String) As Boolean
    'create filesystem and Text stream objects
    Dim currentFileSystem As FileSystemObject
    Dim exportStream As TextStream
    Dim tx As New ADODB.Command
    
    On Error GoTo exportError
    
    Set currentFileSystem = New FileSystemObject
    'create the file
    exportStream = currentFileSystem.CreateTextFile(fileName, True)
    'get all the data from the recordset and save it in the file
    exportStream.Write (rsExport.GetString(adClipString, , cDelimeter, vbCrLf))
    'close the stream
    exportStream.Close
    
    'release memory
    Set exportStream = Nothing
    Set currentFileSystem = Nothing
    writeRecordsetToFile = True
    Exit Function
    
    'error handler
exportError:
    '//TODO: write extra error handling code here
    'there was an error while exporting
    'release the memory before returning to calling function
    Set exportStream = Nothing
    Set currentFileSystem = Nothing
    writeRecordsetToFile = False
End Function

This function will return true if the export operation was successfull or else it will return false. We use FileSystemObject to create a TextStream and then save all the data to that stream. Here is how we can call this function

If writeRecordsetToFile(myRecordset, "C:\myExportedFile.csv", ",") Then
        MsgBox "Export was successfull"
    Else
        MsgBox "Export Failed"
    End If

To execute this function you would need to have a Reference added to Microsoft Scripting Runtime.