[tpm] Exporting Access files to .csv

[Stuart Watt]

I'm surprised if there's an issue with the underlying data. I use Text::CSV without problems. And I use it a lot. But Text::CSV does default a lot of odd settings. 

Two thoughts: (1) the types method apparently does nothing for encoding data, only for decoding, and (2) quotes are only added when 'necessary', which will be (by default) strings with commas or newlines in them (or spaces, even though they aren't actually necessary in all cases). 

I don't have Access (only Excel) so I can't be sure what it's quoting rules are. However, Access looks like it is more zealous and quotes virtually everything. 

So (a) the data will probably be fine for import to, e.g., Excel, or (b) Text::CSV settings like always_quote may be more like the Access output. 

--S


On 2012-10-15, at 4:49 PM, 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