[tpm] Exporting Access files to .csv

[Mark Jubenville]

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 <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
>>>
>>> âEURoeMy only boss is God. And Chuck Norris who is his boss.âEUR?
>>>
>>> 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
>
> _______________________________________________
> toronto-pm mailing list
> toronto-pm at pm.org
> http://mail.pm.org/mailman/listinfo/toronto-pm

-- 

Mark Jubenville ioncache at gmail.com <mailto:ioncache at gmail.com>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/toronto-pm/attachments/20121016/cbeedb13/attachment-0001.html>