Output to CSV w/ OutputTo

[shmoussa]

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]

OutputTo is for outputting *formatted* data. It might not be very *well*
formatted, but that's the idea ;-)

Try using TransferText instead.

 

[shmoussa]

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]

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

 

[shmoussa]

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]

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"

 

[shmoussa]

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]

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"?

 

[shmoussa]

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]

I don't see anywhere in your code where you're calling GetOpenFile to set a
value for strFileName.

 

[shmoussa]

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.

--
Doug Steele, Microsoft Access MVPhttp://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:

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__________________- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP

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

--
Doug Steele, Microsoft Access MVPhttp://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:

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__________________- Hide quoted text -

- Show quoted text -

 

[Graham Mandeno]

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

--
Doug Steele, Microsoft Access MVP

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

--
Doug Steele, Microsoft Access MVPhttp://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:

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__________________- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

Yeah, you're right Graham. I just blindly copied the code from the page, and
forgot the intent was a CSV file.

--
Doug Steele, Microsoft Access MVP

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

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

--
Doug Steele, Microsoft Access MVP

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

On Jun 20, 5:10 pm, "Douglas J. Steele"
I don't see anywhere in your code where you're calling GetOpenFile to
set a
value for strFileName.

--
Doug Steele, Microsoft Access MVPhttp://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:

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__________________- Hide quoted text -

- Show quoted text -

 

[shmoussa]

GREAT. Thank you very much for your help!