[tpm] Exporting Access files to .csv

[Chris Jones]

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 <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 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/
>>My Aphorisms - 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 .
>
> >>
>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
>
>_______________________________________________
>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