Sunday, August 30, 2009

Export SQL data to CSV

Maybe you are confused to export SQL data to CSV file format. Here is a simple source code to export SQL data to CSV file format

First create a new module and paste the following code:

Public Function CSVExport(db As DAO.Database, sSQL As String, sDest As String) As Boolean

Dim record As Recordset
Dim nI As Long
Dim nJ As Long
Dim nFile As Integer
Dim sTmp As String

On Error GoTo Err_Handler

Set record = db.OpenRecordset(sSQL, DAO.dbOpenDynaset, DAO.dbReadOnly)

nFile = FreeFile

Open sDest For Output As #nFile

For nI = 0 To record.Fields.Count - 1
sTmp = "" & (record.Fields(nI).Name)
Write #nFile, sTmp;
Next
Write #nFile,

If record.RecordCount > 0 Then
record.MoveLast
record.MoveFirst

For nI = 1 To record.RecordCount
For nJ = 0 To record.Fields.Count - 1
sTmp = "" & (record.Fields(nJ))
Write #nFile, sTmp;
Next
Write #nFile,
record.MoveNext
Next
End If

Close #nFile
CSVExport = True

Exit Function

Err_Handler:
MsgBox ("Error: " & Err.Description)

CSVExport = False

End Function


1 comment:

  1. You can also use getrows statement of recordset and if you insist to loop use eof and bof

    ReplyDelete