MS Access Forum / Forms Programming / June 2007
Output to CSV w/ OutputTo
|
|
Thread rating:  |
shmoussa - 18 Jun 2007 21:18 GMT I am trying to convert a query to a CSV file with my command button.
As of right now, when the command button is click, the open file dialog box opens and wait for a user to select a CSV file to import to a temp. table. Once the data is imported it automatically filters the table using a query. I want to take that query and save it as a CSV file, which the user will choose a filename and location for using the SAVE AS dialog box....
I am using:
DoCmd.OutputTo acQuery, "Query1", acFormatTXT
However, this returns a document with dashes all over it, used a dividers. Is there a better way to output a query to a CSV file?
Thanks.
Graham Mandeno - 18 Jun 2007 21:49 GMT OutputTo is for outputting *formatted* data. It might not be very *well* formatted, but that's the idea ;-)
Try using TransferText instead.
 Signature Good Luck :-)
Graham Mandeno [Access MVP] Auckland, New Zealand
>I am trying to convert a query to a CSV file with my command button. > [quoted text clipped - 13 lines] > > Thanks. shmoussa - 19 Jun 2007 20:43 GMT Thanks for your reply. I am trying to transfer the results of a QUERY to a CSV file. How do I do that with the TransferText? Isn't TransferText just for transferring tables? Is there a way to convert the results of the query to a Table, and THEN I can use the TransferText?
Thanks again for any help.
Graham Mandeno - 19 Jun 2007 22:39 GMT You can EXPORT a table or a query. (Obviously you cannot IMPORT to a query).
The following will export the result set of MyQuery to a file names test.csv in the same folder as your Access file, and with column headers in the first row:
DoCmd.TransferText acExportDelim,,"MyQuery",CurrentProject.Path & "\test.csv",True
 Signature Good Luck :-)
Graham Mandeno [Access MVP] Auckland, New Zealand
> Thanks for your reply. I am trying to transfer the results of a QUERY > to a CSV file. How do I do that with the TransferText? Isn't [quoted text clipped - 3 lines] > > Thanks again for any help. shmoussa - 20 Jun 2007 16:18 GMT Thanks for the reply again. It does do what I want now. However, I have all the code necassary to let the user choose a location and file name using the SAVE AS dialog box....is there a way to do that with this code? I don't want a predefined file name or location. Please let me know.
Douglas J. Steele - 20 Jun 2007 16:37 GMT You need to prompt the user for the file name to use, store their response in a variable (let's call it strFileName), then use
DoCmd.TransferText acExportDelim,,"MyQuery",strFileName,True
The best way to prompt them for the file name is to use the code in http://www.mvps.org/access/api/api0001.htm at "The Access Web"
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
> Thanks for the reply again. It does do what I want now. However, I > have all the code necassary to let the user choose a location and file > name using the SAVE AS dialog box....is there a way to do that with > this code? I don't want a predefined file name or location. Please let > me know. shmoussa - 20 Jun 2007 17:14 GMT I pasted the code into a module. Have this in my code...
DoCmd.TransferText acExportDelim,,"Query1",strFileName,True
It tells me that it requires a File Name arguement. I know something is missing. Can you possibly detail what I should be doing to make this right? Thanks.
Douglas J. Steele - 20 Jun 2007 18:59 GMT Did you read the rest of my suggestion?
Are you prompting the user for the file name to use and storing their response in strFileName, using the code in http://www.mvps.org/access/api/api0001.htm at "The Access Web"?
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
>I pasted the code into a module. Have this in my code... > [quoted text clipped - 3 lines] > is missing. Can you possibly detail what I should be doing to make > this right? Thanks. shmoussa - 20 Jun 2007 20:29 GMT I did my best to follow your advice. However, I've used that site before and got everything working. When I was using:
DoCmd.OutputTo acQuery, "Query1", acFormatTXT
to export the query to a document, the SAVE AS file dialog box came up and worked, no problem. So it has nothign to do with the code on the module. That was the only code I used to export. I did not even have to refer to the new module.
However, the above code does not give me the output I was hoping for. This code does:
DoCmd.TransferText acExportDelim,,"Query1",strFileName,True
However, it doesn't bring up the save as box. The OPEN dialog box comes up without a problem, as I use it in a previous step.
Here is all of my code....any ideas?
_________start________________ Private Sub Command0_Click() On Error GoTo err_Command0_Click
Dim strFile As String Dim strSQL As String Dim strSaveFileName As String
strFile = GetOpenFile_CLT("M:\LTSO\ADC Systems\SAB\Novell\Disk Space", "Select the .csv file that you want to filter")
If strFile = "" Then Exit Sub Else If MsgBox("Filter out the following file: " & strFile, vbYesNo, "Disk Space Report Filter") = vbNo Then Exit Sub Else End If End If
DoCmd.TransferText acImportDelim, "DISKS2", "Disk Utilization Information Entry", strFile, True
DoCmd.SetWarnings False
strSQL = "SELECT [Disk Information].[File Server Name (DN)], [Disk Information ].[Volume Name], [Disk Information ].[Volume Size (Mb)], [Disk Information ].[Space In Use (Mb)] " & _ "FROM [Disk Information ] " & _ "GROUP BY [Disk Information ].[File Server Name (DN)], [Disk Information ].[Volume Name], [Disk Information ].[Volume Size (Mb)], [Disk Information ].[Space In Use (Mb)] " & _ "HAVING [Disk Information ].[File Server Name (DN)] Not Like '*NCS*' " & _ "ORDER BY [Disk Information ].[Volume Name]"
CurrentDb.QueryDefs("Query1").SQL = strSQL
DoCmd.TransferText acExportDelim, , "Query1", strFileName, True
MsgBox "Filtering data has completed successfully", vbOKOnly, "Filter Data"
DoCmd.Close acForm, "Form1"
exit_Command0_Click: Exit Sub
err_Command0_Click: If Err.Number = 3107 Then MsgBox "You are not authorized to add data.", , "Unauthorized" Exit Sub Else MsgBox Err.Number & " " & Err.Description Resume exit_Command0_Click End If
End Sub
__________end__________________
Douglas J. Steele - 20 Jun 2007 22:10 GMT I don't see anywhere in your code where you're calling GetOpenFile to set a value for strFileName.
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)
>I did my best to follow your advice. However, I've used that site > before and got everything working. When I was using: [quoted text clipped - 77 lines] > > __________end__________________ shmoussa - 20 Jun 2007 22:34 GMT Thanks for your response. I tried using the code from the website you gave me. As I said, I've got the save dialog box to work with OutputTo but not with the TransferText. The open file dialog box works fine too. I'm not really sure how to do this. Any useful advice is appreciated. THANK YOU.
> I don't see anywhere in your code where you're calling GetOpenFile to set a > value for strFileName. [quoted text clipped - 86 lines] > > - Show quoted text - Douglas J. Steele - 21 Jun 2007 00:34 GMT Assuming you've copied the code from http://www.mvps.org/access/api/api0001.htm, then as it says at the top of the page, you need something like:
Dim strFilter As String Dim strSaveFileName As String
strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xls)", "*.xls") strSaveFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ Filter:=strFilter, _ Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acExportDelim, , "Query1", strSaveFileName, True
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)
> Thanks for your response. I tried using the code from the website you > gave me. As I said, I've got the save dialog box to work with OutputTo [quoted text clipped - 93 lines] >> >> - Show quoted text - Graham Mandeno - 21 Jun 2007 05:08 GMT Thanks for jumping in, Doug. My availability is a bit erratic at the moment, not to mention the ubiquitous problem of time zones.
Just a minor correction though - I suspect it should be:
strFilter = ahtAddFilterItem(strFilter, "Delimited text files (*.csv)", "*.csv")
Personally I never use OutputTo, but I must say it's a bit erratic to automatically provide a SaveAs dialog when a filename is omitted using one method (OutputTo) but not another (TransferXXX).
Cheers, Graham
> Assuming you've copied the code from > http://www.mvps.org/access/api/api0001.htm, then as it says at the top of [quoted text clipped - 108 lines] >>> >>> - Show quoted text - Douglas J. Steele - 21 Jun 2007 11:48 GMT Yeah, you're right Graham. I just blindly copied the code from the page, and forgot the intent was a CSV file.
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
> Thanks for jumping in, Doug. My availability is a bit erratic at the > moment, not to mention the ubiquitous problem of time zones. [quoted text clipped - 126 lines] >>>> >>>> - Show quoted text - shmoussa - 21 Jun 2007 15:25 GMT GREAT. Thank you very much for your help!
|
|
|