|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200606/1
|
|
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" <u21899[ at ]uwe> wrote in message
news:61804bd10b6c9[ at ]uwe...
[Quoted Text] > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200606/1
|
|
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
John Nurick wrote:
[Quoted Text] >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
--
Message posted via http://www.accessmonster.com
|
|
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.
On Fri, 09 Jun 2006 16:57:38 GMT, "thrashin via AccessMonster.com"
<u21899[ at ]uwe> wrote:
[Quoted Text] >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
>
>John Nurick wrote:
>>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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
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.
John Nurick wrote:
[Quoted Text] >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
>
>--
>John Nurick [Microsoft Access MVP]
>
>Please respond in the newgroup and not by email.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200606/1
|
|
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")
On Fri, 09 Jun 2006 22:30:34 GMT, "thrashin via AccessMonster.com"
<u21899[ at ]uwe> wrote:
[Quoted Text] >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.
>
>John Nurick wrote:
>>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
>>
>>--
>>John Nurick [Microsoft Access MVP]
>>
>>Please respond in the newgroup and not by email.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
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.
John Nurick wrote:
[Quoted Text] >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.
>
>--
>John Nurick [Microsoft Access MVP]
>
>Please respond in the newgroup and not by email.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200606/1
|
|
Yes, TransferText exports queries as well as tables. Just give it the
name of the query for the TableName argument.
On Mon, 12 Jun 2006 22:11:05 GMT, "thrashin via AccessMonster.com"
<u21899[ at ]uwe> wrote:
[Quoted Text] >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.
>
>
>John Nurick wrote:
>>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.
>>
>>--
>>John Nurick [Microsoft Access MVP]
>>
>>Please respond in the newgroup and not by email.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
|