[tpm] Exporting Access files to .csv

[Chris Jones]

Thanks Mark!
I tried both ways.
my $csv = Text::CSV->new ( { binary => 1, 
always_quote => 1, } )  # should set binary attribute.
                          or die "Cannot use CSV: ".Text::CSV->error_diag ();

And with out alway_quote.  Always quote put 
quotes around interger and number fields and 
Excel displayed them as strings, not numbers so turned always_quote off.

At 08:27 AM 16/10/2012, Mark Jubenville wrote:
>For future reference you can force Text::CSV to 
>always quote every field by setting 'always_quote' to true:
>
>my $csv = Text::CSV->new({ always_quote => 1});
>
>or for an already instantiated csv object:
>
>$csv->always_quote(1);
>
>Cheers,
>
>Mark
>
>On 10/15/2012 8:14 PM, Chris Jones wrote:
>>It is all working great.  Thanks for your help!
>>I am using DBI ADO and Text::CSV.
>>
>>Text::CSV adds quotes where needed.  I was 
>>trying to get the same output as ms access 
>>which quotes all text fields whether they 
>>contain white space or not.  For reading in to 
>>excel, the quotes on text fields are not 
>>necessary unless there is a white space, new line, etc. in the field.
>>
>>It works slick.  Much better than opening each 
>>file in Access and exporting to csv.
>>
>>
>>
>>At 04:49 PM 15/10/2012, Chris Jones wrote:
>>>Thank you!  It works well except I can't get quotes around Text fields.
>>>
>>>I am using:
>>>my $csv = Text::CSV->new ( { binary => 1 } 
>>>)  # should set binary attribute.
>>>                          or die "Cannot use 
>>> CSV: ".Text::CSV->error_diag ();
>>>
>>>$csv->types ([Text::CSV::IV (),
>>>               Text::CSV::IV (),
>>>               Text::CSV::PV (),
>>>               Text::CSV::PV (),
>>>               Text::CSV::PV (),
>>>               Text::CSV::PV (),
>>>               Text::CSV::IV (),
>>>               Text::CSV::IV ()]);
>>>
>>>#output database results
>>>  while (my @row=$sth->fetchrow_array)
>>>   {
>>>         #my $line = join( ",", @row );
>>>         print "@row\n";
>>>                 my $status = $csv->combine(@row);
>>>                 my $line   = $csv->string();
>>>                 print "$line\n"
>>>
>>>         }
>>>
>>>The output has quotes around the memo fields 
>>>but the text fields are not quoted?
>>>
>>>At 09:21 AM 15/10/2012, Shlomi Fish wrote:
>>>>Hi Chris,
>>>>
>>>>On Mon, 15 Oct 2012 09:07:39 -0400
>>>>Chris Jones <mailto:cj at enersave.ca><cj at enersave.ca> wrote:
>>>>
>>>> > Thank you.
>>>> >
>>>> > I have written a perl script using DBI ADO.  The
>>>> > select and print statements work.  I am using the
>>>> > join statement to separate the fields by ',' but
>>>> > I can't figure out a way to get the text fields
>>>> > quoted so the output resembles the CSV output by msaccess.
>>>> >
>>>> > Example output from access:
>>>> > 240,10,"SPACE:LGT-W/A[1]","if( Local( ZONE-TYPE )
>>>> > == 1 then 1 endif","Set lighting power","8.4.4.6","db 2012-04-19 -
>>>> > added",1,0
>>>> >
>>>> > The field types:
>>>> > number, number, text, memo, text, text, memo, integer, integer.
>>>> >
>>>> > The output from my script:
>>>> >
>>>> > 240,10,SPACE:LGT-W/A[1],if( Local( ZONE-TYPE ) ==
>>>> > 1 then 1 endif,Set lighting power,8.4.4.6,db 2012-04-19 - added,1,0
>>>> >
>>>> > Thanks for any insight!
>>>>
>>>>Please use 
>>>><https://metacpan.org/module/Text::CSV>https://metacpan.org/module/Text::CSV 
>>>>to output CSV (as well
>>>>as read it). Read its documentation for 
>>>>configurations options that control
>>>>the resultant output.
>>>>
>>>>Regards,
>>>>
>>>>         Shlomi Fish
>>>>
>>>>--
>>>>-----------------------------------------------------------------
>>>>Shlomi Fish       <http://www.shlomifish.org/>http://www.shlomifish.org/
>>>>My Aphorisms - 
>>>><http://www.shlomifish.org/humour.html>http://www.shlomifish.org/humour.html
>>>>
>>>>“My only boss is God. And Chuck Norris who is his boss.”
>>>>
>>>>Please reply to list if it's a mailing list 
>>>>post - <http://shlom.in/reply>http://shlom.in/reply .
>>>
>>> >>
>>>Christopher Jones, P.Eng.
>>>Suite 1801, 1 Yonge Street
>>>Toronto, ON M5E1W7
>>>Tel. 416-203-7465
>>>Fax. 416-946-1005
>>>email <mailto:cj at enersave.ca>cj at enersave.ca
>>>
>>>_______________________________________________
>>>toronto-pm mailing list
>>><mailto:toronto-pm at pm.org>toronto-pm at pm.org
>>>http://mail.pm.org/mailman/listinfo/toronto-pm
>>
>> >>
>>Christopher Jones, P.Eng.
>>Suite 1801, 1 Yonge Street
>>Toronto, ON M5E1W7
>>Tel. 416-203-7465
>>Fax. 416-946-1005
>>email <mailto:cj at enersave.ca>cj at enersave.ca
>>
>>_______________________________________________
>>toronto-pm mailing list
>><mailto:toronto-pm at pm.org>toronto-pm at pm.org
>>http://mail.pm.org/mailman/listinfo/toronto-pm
>
>--
>
>Mark Jubenville <mailto:ioncache at gmail.com>ioncache at gmail.com
>_______________________________________________
>toronto-pm mailing list
>toronto-pm at pm.org
>http://mail.pm.org/mailman/listinfo/toronto-pm

 >>
Christopher Jones, P.Eng.
Suite 1801, 1 Yonge Street
Toronto, ON M5E1W7
Tel. 416-203-7465
Fax. 416-946-1005
email cj at enersave.ca
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/toronto-pm/attachments/20121019/737b1ca8/attachment.html>