Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   Export a query output as csv format (https://www.access-programmers.co.uk/forums/showthread.php?t=152949)

atol 07-11-2008 08:19 PM

Export a query output as csv format
 
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 07-12-2008 06:57 PM

Thanks Bob. Much appreciated!

wiklendt 11-16-2008 04:40 PM

Re: Export a query output as csv format
 
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, , 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 11-16-2008 05:33 PM

Re: Export a query output as csv format
 
ok, i got a bit further with this helpful post http://www.access-programmers.co.uk/...+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, eqryExportStudyData, 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 11-16-2008 05:42 PM

Re: Export a query output as csv format
 
1 Attachment(s)
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 11-16-2008 05:50 PM

Re: Export a query output as csv format
 
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 11-16-2008 06:47 PM

Re: Export a query output as csv format
 
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 11-16-2008 06:59 PM

Re: Export a query output as csv format
 
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 11-16-2008 09:44 PM

Re: Export a query output as csv format
 
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 11-17-2008 12:48 PM

Re: Export a query output as csv format
 
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 11-17-2008 01:09 PM

Re: Export a query output as csv format
 
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 11-17-2008 01:19 PM

Re: Export a query output as csv format
 
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 11-17-2008 01:37 PM

Re: Export a query output as csv format
 
Quote:

Originally Posted by gemma-the-husky (Post 776949)
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 11-17-2008 01:45 PM

Re: Export a query output as csv format
 
Quote:

Originally Posted by datAdrenaline (Post 776954)
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 :D i knew it had to be something simple!! LOL ;)

wiklendt 02-16-2009 06:12 PM

Re: Export a query output as csv format
 
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?


All times are GMT -8. The time now is 03:56 PM.

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