UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ExportToCSV    

Synopsis

The following code will Export data to a CSV file. You can pass a Table name Query name, or an SQL statment to it. If you pass an SQL statement, be sure to wrap it with parenthesis. The procedure is declared as a Function for conveinience and will always return a 0. The conveinience being referenced, is that of adding the function call directly to that OnClick procedure of a Button control with something like this in the On Click property:

=ExportToCSV("someTable", "C:\Temp\someTable.csv", ";", True)

Of course you can still call the code in VBA with standard calling conventions.

CODE

' ExportToCSV
' http://www.utteraccess.com/wiki/index.php/ExportToCSV
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2011-04-11                    Initial Release
' 2.0  2012-06-21                    Added strCustomHeader & strStringValueDelimiter
Public Function ExportToCSV(strSource As String, _
                          strFilename As String, _
                          Optional strColumnDelimiter As String = ",", _
                          Optional blColumnHeaders As Boolean, _
                          Optional strCustomHeader As String, _
                          Optional strStringValueDelimiter As String) As Byte
'Exports a table or query or SQL statement to a text file.  If a SQL is passed
'as the source, enclose it in Parenthesis.
 
   Dim intChannel As Integer
   Dim strSQL As String
   Dim strCSV As String
   Dim x As Integer
   
   'Close any open files
   For intChannel = 1 To 511
       Close #intChannel
   Next intChannel
   
   'Open a channel to communicate with your file
   intChannel = FreeFile
   Open strFilename For Output Access Write As #intChannel
   
   'Write the contents of the table to the file
   'Open the source
   strSQL = "SELECT * FROM " & strSource & " As vTbl"
   
   With CurrentDb.OpenRecordset(strSQL, 4) 'dbOpenSnapshot = 4
       
       'Write custom header
       If Len(strCustomHeader) > 0 Then _
           Print #intChannel, strCustomHeader
       
       'Write the headers if appropriate
       If blColumnHeaders = True Then
           For x = 0 To .Fields.Count - 1
               strCSV = strCSV & strColumnDelimiter & .Fields(x).Name
           Next x
           Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1)
       End If
               
       'Write the CSV
       Dim strValue As String
       Do Until .EOF
           
           strCSV = ""
           
           For x = 0 To .Fields.Count - 1
               
               strValue = Nz(.Fields(x), "<NULL>")
               
               Select Case .Fields(x).Type
                   Case dbText, dbMemo
                       strValue = strStringValueDelimiter & strValue & strStringValueDelimiter
               End Select
               
               strCSV = strCSV & strColumnDelimiter & strValue
           
           Next x
           
           Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1)
           
           .MoveNext
           
       Loop
       
   End With
   
   'Close all the files
   Close #intChannel
 
End Function

Creative Commons License
ExportToCSV by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,494 times.  This page was last modified 02:37, 22 June 2012 by Brent Spaulding.   Disclaimers