Export a query output as csv format (1 Viewer)

atol · Jul 12, 2008

Hi there,
I have a quiery that runs behind a command button and exports data to Excel; however i want the format to be csv. Is it doable? Any ideas or code suggestions are appreciated.

Best,
Atol

atol · Jul 13, 2008

Thanks Bob. Much appreciated!

wiklendt · Nov 17, 2008

hey there bob.

i am having issues with an export. basically, everything works fine BUT the output txt file is being generated with comma delimitation and quotation marks around all the fields individually. i want tab delimited with no quotation marks.

i am using A2007, and it does not seem to have the same export to text options as previous versions have - it has some other weird way of doing it which i can't get it to export in any way but one, let alone tab delimited!

i am also doing this via VBA On Click event from a form button:

Code:

Private Sub cmdExportStudyData_Click()
On Error GoTo Err_cmdExportStudyData_Click

    Dim strStudy, strDate, strFileName, strExportFile, strDoc As String
    
    strDoc = "qryExportStudyData"
    strStudy = cmbStudies
    strDate = Format(Date, "yyyymmmdd")
    strFileName = strStudy & " sero (" & strDate & ").txt"
    
    strExportFile = CurrentProject.Path & "\ExportResults\" & strFileName

    If IsNull(cmbStudies) Then
        MsgBox "Please select a study first"
    Else

    DoCmd.TransferText acExportDelim[COLOR=Red], ,[/COLOR] strDoc, strExportFile, False

    MsgBox ("Your data file is saved as: " & Chr(13) & strFileName & Chr(13) & Chr(13) & "Here: " & Chr(13) & strExportFile)

    End If

Exit_cmdExportStudyData_Click:
    Exit Sub

Err_cmdExportStudyData_Click:
    Msg = "Error # " & str(Err.Number) & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Resume Exit_cmdExportStudyData_Click

End Sub

i understand the export specifications are to go between the two commas highlighted in red in my code above, but i'm buggered if i can find help in access, and your tutorial doesn't seem to follow in A2007 (LOL, and your tutorial is the only reference on how to do this in these forums here that i can see

)

i'm happy to do this programmatically in VBA rather than build a query to use, but i'm baffled at what the parameters are!

anyone know how to do this via VBA? or in A2007? (but i NEED it to work on A2000-2003)

wiklendt · Nov 17, 2008

ok, i got a bit further with this helpful post http://www.access-programmers.co.uk/forums/showthread.php?t=120454&highlight=export+specification and exporting manually seems to work fine BUT i can't seem to now refer to this export specification neither via my VBA, nor when i manually export (doesn't ask for a specification to use)...

that is, when i now try to reference my saved specification (i called it eqryExportStudyData) in VBA, shown below, it made no difference to the output file but also caused no errors. this is testing in A2007 only (but note that this will ultimately be used on an A2K system)

Code:

...
DoCmd.TransferText acExportDelim, [COLOR=Red]eqryExportStudyData[/COLOR], strDoc, strExportFile, False
...

any ideas?

edit:
for those wanting to know, i managed to make the spec file in A2007 by right-clicking on my query (which is strDoc in the above code, but the query name is "qryExportStudyData") and exporting, along with the Eval("") method in the referenced post in the in-lying query) and following prompts simliar to those detailed by bob for 2000 versions.

wiklendt · Nov 17, 2008

and when i just tried to declare the spec file, thinking maybe that's the issue, thusly:

Code:

    strSpec = "eqryExportStudyData"
...
    DoCmd.TransferText acExportDelim, strSpec, strDoc, strExportFile, False

i get an error 3625 (please see attached image)

edit:
of course, when i go to "external data" in the ribbon, then click on "saved exports", "equryExportStudyData" is there!

wiklendt · Nov 17, 2008

somewhat related, but unrelated: once access creates the txt file, can i get it to open it in notepad automatically? i.e., user presses button "export study data", access exports to txt (already working, though with wrong delimitation) and then opens it in notepad fof the user to see?

wiklendt · Nov 17, 2008

ok, finally success!! what i had to do was right-click on my query, export | text file, then click OK at the wizardy looking window, then when it comes up with the first spec options, click on "Advanced" (bottom left of the dialog window) , set my specs, then save using the "save as..." button.

THEN i could use it in my VBA... phew! what a way around

edit:
(this does NOT save it into the "saved exports" area of 2007, also, the DB that i inherited had already some saved exports in there via the way described above in this post, which did not show in the "saved exports" - just in case this helps anyone else with A2007

wiklendt · Nov 17, 2008

ound my own solution re opening the file in notepad. i simply added this line after my message box

Code:

    Shell "notepad.exe " & strExportFile, vbNormalFocus

datAdrenaline · Nov 17, 2008

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 ...

wiklendt · Nov 17, 2008

Hey thanks datAdrenaline! looks simpler than i expected

i'll file it for future use. i'm not yet experienced at all with ADO sets, but i'm sure lots of other people on the forums might appreciate this alternative (possibly even me in the future!)

but i did have one more question for everyone else. i have been exporting my data as described in this thread (using an export spec file). everything has been exporting fine now EXCEPT one thing i can't figure out: i have in my output query set all the dates to a specific formatting: uppercase for month names. the export file 'prints' all the names in Sentence case.

i've tried forcing upper case in the tables themselves, though it seems i can only apply one type of formatting per field (in this case, the date is formatted as ddmmmyyyy, rather than the default dd/mm/yyyy) even so, the uppercase formatting doesn't seem to follow through.

any ideas?

gemma-the-husky · Nov 17, 2008

wikelndt

without trying it myself in A2007, the procedure you describe to manage import/export specs (ie - using the advanced option) seems like its the same mechanism as in other access versions. Are you seeing some differences?

datAdrenaline · Nov 17, 2008

In the query you export, instead of specifying a format property on the date column. Create an expression for the date column that looks something like this:

UCase(Format([YourDateField],"ddmmmyyyy"))

wiklendt · Nov 17, 2008

gemma-the-husky said:
wikelndt

without trying it myself in A2007, the procedure you describe to manage import/export specs (ie - using the advanced option) seems like its the same mechanism as in other access versions. Are you seeing some differences?

sorry gemma-the-husky, i did end up getting this (creating an export spec file) to work (check my flurry of post posting LOL, bit messy).

There is a difference, though mostly seems to be where the button to press is. in previous versions, you select File|Export... in 2007, you select External Data|Text File... (in the 'ribbon') then follow the prompts. in 2007, i believe the setup is more confusing thanks to the first dialog box, but if you just click OK on that one (without checking any boxes) you get the same export options as previous versions.

i also found that if you set up an export spec in 2007, you HAVE to do it via the "advanced" button when setting up export, and NOT "save export steps" that 2007 offers if you don't go via "advanced". The conflict for me here is that these "steps" 2007 calls 'export specification', but does not acutally allow you to use it. if you want to use an export spec, you have to go via Advanced button when exporting and save the spec there.

wiklendt · Nov 17, 2008

datAdrenaline said:
In the query you export, instead of specifying a format property on the date column. Create an expression for the date column that looks something like this:

UCase(Format([YourDateField],"ddmmmyyyy"))

that fixed it, thanks

i knew it had to be something simple!! LOL

wiklendt · Feb 17, 2009

Hi, just using this export feature in another database and i'm trying to set up an export specification.

The data i'm exporting is determined by a query. This query has a criteria based on a combo box on my form.

Now, normally this query and parameter work like this:

Code:

Like "*" & [forms]![frmSpecimens].[cmbDateExport] & "*"

But for exporting, there is an error to the effect of:
"Expected Parameter 1" or something.

This is fixed by using the Eval function, HOWEVER, this stuffs up my parameter somewhat. it changes the criteria to:

Code:

Eval("[forms]![frmSpecimens].[cmbDateExport]")

which would normally be fine, except that the "Like" version returned ALL dates if none was selected, this "Eval" version returns NONE if no date is selected. i've tried playing around but i mostly get an error stipulating and especially helpful dialog box with only the word "unkown" on it.

i've tried:

Code:

Eval("*" & "[forms]![frmSpecimens].[cmbDateExport]" & "*")

and

Eval(Like "*" & "[forms]![frmSpecimens].[cmbDateExport]" & "*")

both give the same "unknown" error when i try to view the query results...

any ideas?

wiklendt · Feb 17, 2009

(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...)

danmac · Feb 17, 2009

datAdrenaline said:

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.

datAdrenaline · Mar 3, 2009

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!

MrLaMatta · Mar 3, 2017

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!

tucker61 · Sep 12, 2019

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

Export a query output as csv format (1 Viewer)

Registered User.

Registered User.

i recommend chocolate

i recommend chocolate

i recommend chocolate

Attachments

i recommend chocolate

i recommend chocolate

i recommend chocolate

AWF VIP

i recommend chocolate

Super Moderator

AWF VIP

i recommend chocolate

i recommend chocolate

i recommend chocolate

i recommend chocolate

New member

AWF VIP

New member

Registered User.

Similar threads

Users who are viewing this thread