Create a csv file

[JIM]

I'm creating a CSV file with the followning:
DoCmd.TransferText acExportDelim, , "qryCSVSales", "C:\Sales.csv"
'transfers query records to an Peachtree sales import file

The problem is that all the alphanumeric fields have quotes around them. Is
the best procedure to output an Access file first then use an update query to
remove the quotes? I need the quotes around one field but the rest of the
quotes need to be removed.
TIA

 

[GBA]

just an experiment; try defining the output to sales.txt rather than .csv

 

[Marshall Barton]

I'm creating a CSV file with the followning:
DoCmd.TransferText acExportDelim, , "qryCSVSales", "C:\Sales.csv"
'transfers query records to an Peachtree sales import file

The problem is that all the alphanumeric fields have quotes around them. Is
the best procedure to output an Access file first then use an update query to
remove the quotes? I need the quotes around one field but the rest of the
quotes need to be removed.

Export the file maunually and use the Advanced button to
specify how each field should be treated. Then Save the
export specification so you can use it with TransferText.

 

[JIM]

Thanks for reply GBA but the file comes out exactly the same - with all the
alpha fields with double quotes around.

 

[JIM]

Hi Marshall, thanks for reply. I created a file from the query I am using
and exported the file and used the advanced button as you suggest. Problem
is I need one field to be surrounded with double quotes and the advanced
button doesn't allow to specify fields individually. If I specify "none"
then all fields come without quotes. The job ID field needs quotes around.
Is there a way to edit the export specs? Also I would like to export the
query directly instead of using a make table query and then exporting table.
Is that possible?
TIA

 

[Marshall Barton]

How about using the query to put the quotes around that one
field (something like """" & JobID & """"). Then you should
be able to use the "none" option without an intermediate
table.

 

[JIM]

OK. That worked great. In the query, using 3 double quotes around JobID and
using the wizard I chose "none" for text qualifier and VIOLA! all the quotes
were gone except the quotes around JobID. But I still can't export from a
query. I tried to export the query manually and get the message "Too few
parameters. Expected 1." What I would like to do is export directly from my
code. My code is:
DoCmd.TransferText acExportDelim, , "qryCSVSales", "C:\Sales.txt"
'transfers query records to an Peachtree sales import file

Is it possible?
TIA

How about using the query to put the quotes around that one
field (something like """" & JobID & """"). Then you should
be able to use the "none" option without an intermediate
table.
--
Marsh
MVP [MS Access]

Hi Marshall, thanks for reply. I created a file from the query I am using
and exported the file and used the advanced button as you suggest. Problem
is I need one field to be surrounded with double quotes and the advanced
button doesn't allow to specify fields individually. If I specify "none"
then all fields come without quotes. The job ID field needs quotes around.
Is there a way to edit the export specs? Also I would like to export the
query directly instead of using a make table query and then exporting table.

 

[JIM]

I think I'm almost there Marshall. Reading thru help, etc. I tried saving my
spec file in the export wizard. It's called CSVSalesSpecs. Where do I
insert it in my transfertext line? I've tried several ways but my program
won't compile.
TIA

How about using the query to put the quotes around that one
field (something like """" & JobID & """"). Then you should
be able to use the "none" option without an intermediate
table.
--
Marsh
MVP [MS Access]

Hi Marshall, thanks for reply. I created a file from the query I am using
and exported the file and used the advanced button as you suggest. Problem
is I need one field to be surrounded with double quotes and the advanced
button doesn't allow to specify fields individually. If I specify "none"
then all fields come without quotes. The job ID field needs quotes around.
Is there a way to edit the export specs? Also I would like to export the
query directly instead of using a make table query and then exporting table.

 

[Marshall Barton]

OK. That worked great. In the query, using 3 double quotes around JobID and
using the wizard I chose "none" for text qualifier and VIOLA! all the quotes
were gone except the quotes around JobID. But I still can't export from a
query. I tried to export the query manually and get the message "Too few
parameters. Expected 1." What I would like to do is export directly from my
code. My code is:
DoCmd.TransferText acExportDelim, , "qryCSVSales", "C:\Sales.txt"
'transfers query records to an Peachtree sales import file

If you only use 3 double quotes, you should end up exporting
the string "JobID" instead of "x234y" (i.e. the value of the
JobID field).

 

[Marshall Barton]

I think I'm almost there Marshall. Reading thru help, etc. I tried saving my
spec file in the export wizard. It's called CSVSalesSpecs. Where do I
insert it in my transfertext line?

The name of the export spec (in quotes) goes in
TransferText's second argument:

DoCmd.TransferText acExportDelim, "nameof spec",
"qryCSVSales", "C:\Sales.csv"

 

[JIM]

Thanks a lot. I didn't think of quotes around the specs. BTW the 3 double
quotes were around the value of the field JobID and it's working perfect.
Really grateful for your help.

 

[Marshall Barton]

Glad you got it to work, but I still don't understand what's
going on with the quotes. Would you please post the query's
SQL view so I can see what you have.