Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / June 2007

Tip: Looking for answers? Try searching our database.

Output to CSV w/ OutputTo

Thread view: 
Enable EMail Alerts  Start New Thread
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.