Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 15 votes, 5.00 average. Display Modes
Old 02-16-2009, 06:38 PM   #16
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Export a query output as csv format

(btw, i've made the export specification ok using the Eval that works but returns no dates if the date combobox has nothing selected, but now i need to figure out how to make an Eval that returns all dates when nothing is selected in the dates combo...)

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 02-16-2009, 08:28 PM   #17
danmac
Registered User
 
Join Date: Feb 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
danmac is on a distinguished road
Re: Export a query output as csv format

Quote:
Originally Posted by datAdrenaline View Post
I export to CSV with my own procedure ..

Code:
Public Function ExportToCSV_D(strSource As String, _
                            strFileName As String, _
                            Optional strColumnDelimiter As String = ",", _
                            Optional blHeaders As Boolean) As Byte
'Exports a table or query or SQL statemtent 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 TEMP file and
    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 the headers if appropriate
        If blHeaders = 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
        Do Until .EOF
            strCSV = ""
            For x = 0 To .Fields.Count - 1
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>")
            Next x
            Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1)
            .MoveNext
        Loop
        
    End With
           
    'Close all the files
    Close #intChannel
    
End Function
I have an equivalent one for use with ADO as well ... if interested, I'd be glad to post it too ...
Hi datAdrenaline I am currently having some troubles implementing this in an ADP project. Could this possibly be an indicator I need to use your ADO version?
I have a DB Application created in Access and interfacing with a SQL 2000 instance. Unfortunatly because this is an ADP I cannot use the TransferText method because I cannot save an Export Specification (I'm trying to achieve a comma or tab delimited file with no quotes/Text Delimeter).

Any help anyone can offer would be much appreciated.
danmac is offline   Reply With Quote
Old 03-02-2009, 05:28 PM   #18
datAdrenaline
AWF VIP
 
Join Date: Jun 2008
Location: Northern Virginia
Posts: 697
Thanks: 0
Thanked 27 Times in 12 Posts
datAdrenaline is a jewel in the rough datAdrenaline is a jewel in the rough datAdrenaline is a jewel in the rough
Re: Export a query output as csv format

Hello Danmac ...

Sorry to be so long on a reply! ... I have been busy of late, and I am just getting around to returning to AWF ... so ... in response to ...

>> Could this possibly be an indicator I need to use your ADO version? <<

Yep ... you definately need the ADO version with an ADP ... so ... give this a shot:


Code:
Public Function ExportToCSV_A(strSource As String, _
                            strFileName As String, _
                            Optional strColumnDelimiter As String = ",", _
                            Optional blHeaders As Boolean = False) 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 strHeaders 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 TEMP file and
    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 CurrentProject.Connection.Execute(strSQL, , 1) 'adCmdText = 1
                
        'Build Headers if appropriate
        If blHeaders = True Then
            For x = 0 To .Fields.Count - 1
                strHeaders = strHeaders & strColumnDelimiter & .Fields(x).Name
            Next
            strHeaders = Mid(strHeaders, Len(strColumnDelimiter) + 1) & vbCrLf
        End If
    
        'Write to the CSV file
        Print #intChannel, strHeaders & .GetString(2, , strColumnDelimiter, vbCrLf, "<NULL>") 'adClipString = 2
        
    End With
           
    'Close all the files
    Close #intChannel
    
End Function
Hope it helps you out!

__________________
Brent Spaulding | datAdrenaline |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
datAdrenaline is offline   Reply With Quote
The Following User Says Thank You to datAdrenaline For This Useful Post:
tucker61 (09-12-2019)
Old 03-03-2017, 08:18 AM   #19
MrLaMatta
Newly Registered User
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
MrLaMatta is on a distinguished road
Re: Export a query output as csv format

I know this is an old thread, but I just wanted to thank you for the script; with just a few changes I am generating text files that i can use to transmit payment orders to my bank.
Very grateful of your insight!
MrLaMatta is offline   Reply With Quote
Old 09-12-2019, 11:34 AM   #20
tucker61
Newly Registered User
 
Join Date: Jan 2008
Posts: 199
Thanks: 27
Thanked 2 Times in 2 Posts
tucker61 is on a distinguished road
Re: Export a query output as csv format

Again, appreciate this is a old thread, but the code with a bit of tweaking does exactly what i needed. Thanks

tucker61 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Access Query to Excel ShanVel Queries 7 11-18-2009 09:42 AM
Export query with currency removed into comma delimited file Poppy Queries 0 04-18-2006 01:12 AM
unusual export to excel PhilipEwen Modules & VBA 3 11-07-2003 09:37 AM
Can't change format of field output from query Nicola Queries 3 08-14-2001 09:24 AM
[SOLVED] Automating saving query output as a csv file Caroline Modules & VBA 1 04-11-2000 06:41 AM




All times are GMT -8. The time now is 10:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World