Removing quotes from a csv file after DoCmd.TransferText

[thrashin via AccessMonster.com]

Hello. I have seen this topic covered here but it was two years ago. I
built a database in Access 2003. When the user clicks a command button, the
click event procedure runs/exports a table data to a csv via the acdelim
method. DoCmd.TransferText, acDelim, etc etc

When the file comes out onto my hard drive, I open it with Notepad. I see
quotes surrounding fields that have text in them. I want to remove the
quotes entirely from the csv output file. I currently can do this with
making a report first of the table data that I want to see (no page header no
page footer) than

DoCmd.OutputTo, acReport, (I forgot what I put after this) but, there is a
lot of space around the entries so then, I thought I would play with the
field widths of the report and put , label controls in between the fields to
make the TXT file look like a csv. That seems like a lot of work.

Isn't there a more preferred method to remove the quotes from a csv file with
a global find all " and strip all ". Would a Trim or Left, Mid, Right do the
trick? I just am forgetting the syntax.

Anotherwords, I could run the export as is since it works although I get
quotes in the output. But, I could programmatically with code open the file
on my hard drive and remove the quotes?

Sorry for the long windedness but, I have been scratching my head for three
days on this one?

Thank you

 

[John Nurick]

If you want to create a CSV file without quotes around the field values, use
an export specification. Export the table once manually and tell the wizard
to use no "qualifier" (instead of the default quote marks). Then click the
Advanced button in the wizard and save the specification under a convenient
name.

Subsequently, pass the name of the specificatoin as an argument to
TransferText.

Remember that,if you don't use quote marks in the CSV file you must ensure
that there are no commas in the data.

To remove quote marks from an existing CSV file, use the Replace command of
Notepad or any other text editor. Or if Perl is installed on your machine,
you can use this command:

perl -i.bak -pe"s/\x22//g" "myfile.csv"

 

[thrashin via AccessMonster.com]

John,

Thank you for your response. When you say Export manually are you referring
to, right click table, choose export, when save in dialog box comes up what
do you do? I see what looks to be like it asking you to export it to a
database or excel or paradox etc. etc. I dont see an advanced button.

Thanks

If you want to create a CSV file without quotes around the field values, use
an export specification. Export the table once manually and tell the wizard
to use no "qualifier" (instead of the default quote marks). Then click the
Advanced button in the wizard and save the specification under a convenient
name.

Subsequently, pass the name of the specificatoin as an argument to
TransferText.

Remember that,if you don't use quote marks in the CSV file you must ensure
that there are no commas in the data.

To remove quote marks from an existing CSV file, use the Replace command of
Notepad or any other text editor. Or if Perl is installed on your machine,
you can use this command:

perl -i.bak -pe"s/\x22//g" "myfile.csv"

Hello. I have seen this topic covered here but it was two years ago. I
built a database in Access 2003. When the user clicks a command button,

[quoted text clipped - 31 lines]

Thank you

 

[John Nurick]

That's the place to start. You need to export to to a text file (CSV is
a flavour of text file): once that's started you should get the wizard
and its Advanced button.

John,

Thank you for your response. When you say Export manually are you referring
to, right click table, choose export, when save in dialog box comes up what
do you do? I see what looks to be like it asking you to export it to a
database or excel or paradox etc. etc. I dont see an advanced button.

Thanks

If you want to create a CSV file without quotes around the field values, use
an export specification. Export the table once manually and tell the wizard
to use no "qualifier" (instead of the default quote marks). Then click the
Advanced button in the wizard and save the specification under a convenient
name.

Subsequently, pass the name of the specificatoin as an argument to
TransferText.

Remember that,if you don't use quote marks in the CSV file you must ensure
that there are no commas in the data.

To remove quote marks from an existing CSV file, use the Replace command of
Notepad or any other text editor. Or if Perl is installed on your machine,
you can use this command:

perl -i.bak -pe"s/\x22//g" "myfile.csv"

Hello. I have seen this topic covered here but it was two years ago. I
built a database in Access 2003. When the user clicks a command button,

[quoted text clipped - 31 lines]

Thank you

 

[thrashin via AccessMonster.com]

John,

I am real close now. I understand how to export the table manually and
create the spec. What I do not understand is how to deal with date fields
upon delimited export. When the table exports I see something like
,200668 00:00:00

I want to get rid of the 00:00:00 and format the 200668 to 20060608. How can
I do this? Eventually this .csv file will be imported into a CRM system that
is finicky. All the fields have to be in a specific order separated by
commas and no space and formatted just right.

Thank you so much for all your help.

Thank You.

That's the place to start. You need to export to to a text file (CSV is
a flavour of text file): once that's started you should get the wizard
and its Advanced button.

John,

[quoted text clipped - 28 lines]

 

[John Nurick]

Create a query that uses calculated fields to format the values exactly
as required, then export the query instead of the table. For example,
this will format a date field the way you describe:

fDate: Format([TheDate], "yyyymmdd")

John,

I am real close now. I understand how to export the table manually and
create the spec. What I do not understand is how to deal with date fields
upon delimited export. When the table exports I see something like
,200668 00:00:00

I want to get rid of the 00:00:00 and format the 200668 to 20060608. How can
I do this? Eventually this .csv file will be imported into a CRM system that
is finicky. All the fields have to be in a specific order separated by
commas and no space and formatted just right.

Thank you so much for all your help.

Thank You.

That's the place to start. You need to export to to a text file (CSV is
a flavour of text file): once that's started you should get the wizard
and its Advanced button.

John,

[quoted text clipped - 28 lines]

Thank you

 

[thrashin via AccessMonster.com]

John,

Thank you very much for your insight. Can you DoCmd.TransferText a query?
It appears you can only do that with a table. So, I made a query that makes
a table from table data that I want to export into a delim file. In that
make table query I used that format calculated field on my short date to make
it yyyymmdd. Then I used DoCmd.TransferText, acExportDelim, [Specification
Name], [Name of Table in quotes], "Output file"
and it worked.

Now I have my fields formatted right for my finicky CRM computer.

Thanks very much again!

I wish there was a way that I could just have reached this conclusion with
one table, pulling what i need in the order I need it in and formatted the
way I wanted.

Create a query that uses calculated fields to format the values exactly
as required, then export the query instead of the table. For example,
this will format a date field the way you describe:

fDate: Format([TheDate], "yyyymmdd")

John,

[quoted text clipped - 26 lines]

 

[John Nurick]

Yes, TransferText exports queries as well as tables. Just give it the
name of the query for the TableName argument.

John,

Thank you very much for your insight. Can you DoCmd.TransferText a query?
It appears you can only do that with a table. So, I made a query that makes
a table from table data that I want to export into a delim file. In that
make table query I used that format calculated field on my short date to make
it yyyymmdd. Then I used DoCmd.TransferText, acExportDelim, [Specification
Name], [Name of Table in quotes], "Output file"
and it worked.

Now I have my fields formatted right for my finicky CRM computer.

Thanks very much again!

I wish there was a way that I could just have reached this conclusion with
one table, pulling what i need in the order I need it in and formatted the
way I wanted.

Create a query that uses calculated fields to format the values exactly
as required, then export the query instead of the table. For example,
this will format a date field the way you describe:

fDate: Format([TheDate], "yyyymmdd")

John,

[quoted text clipped - 26 lines]

Please respond in the newgroup and not by email.