VB 6.0: Export Recordset to a Text File
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
VB Code:
'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
VB Code:
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.