jonix has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am tinkering with a script for writing data fetched from MSSQL to Excel directly.
It works great so far, but my integer return values are represented as decimal strings -
like 57.00 and a char denoted by a square that I cannot depict, instead of the expected 57.
Excel warns me that the number is stored as text.

Any hints how to pass those integers correctly are greatly appreciated - and please bash my code, as I am still a Perl n00b!

#!/usr/bin/perl
use strict;
use warnings;
use Win32::ODBC;
use Spreadsheet::WriteExcel::Big;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel::Big->new("mssql.xls");

# Add a worksheet
my $worksheet = $workbook->addworksheet();

#  Add and define a format
my $header = $workbook->addformat(); # Add a format
my $format = $workbook->addformat();
$header->set_bg_color('yellow');
$header->set_border();
$format->set_border();

# dsn needs to be configured - see Data Sources (ODBC) -> User dsn
my $dsn = "s-atlas";
my $db1;

if (!($db1 = new Win32::ODBC($dsn))){
    print "Error connecting to $dsn\nError: " . Win32::ODBC::Error() .
+ "\n";
    exit;
}

my $sql;
while (<>) {$sql .= $_}

if ($db1->Sql($sql)){
    print "db1 SQL failed.\nError: " . $db1->Error() . "\n";
    $db1->Close();
    exit;
}

my @data;
my @fields;
my $col = my $row = 0;

while ($db1->FetchRow()){
    $col = 0;
    @data = $db1->Data();
    if (!@fields){
        @fields = $db1->FieldNames();
        #print "$_ " for @fields, "\n";
        $worksheet->write($row, $col++, $_, $header) for @fields;
    }else{
        #print "$_ " for @data, "\n";
        $worksheet->write($row, $col++, $_, $format) for @data;
    }
    # catch >= 65535 rows in new worksheet
    if ($row == 65535){
        $worksheet = $workbook->addworksheet();
        $row = $col = 0;
        $worksheet->write($row, $col++, $_, $header) for @fields;
    }
    $row++;
}

[download]
Update: Solved - well kind of, a chop did the trick: 
for (@data) {
    chop;
    if ($_ =~ m/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/){
        print;
        $worksheet->write_number($row, $col++, $_, $format);
    }else{
        $worksheet->write($row, $col++, $_, $format)
    }
}

[download]

It prints now as expected, also in Excel.
How can I find out what the strange character was?
I only want to chop this one :)

Update 2: after thors hint about the strange character beeing a NUL value, I was able to get rid of it with the statement:

s/\0//g for @data;

[download]


Update 3 - this should be even better:

y/\0// for @data;

[download]

Update 4: This does not work as expected though, as no interpolation is done during character translation.
I will have to stick to the substitution as mentioned with update 2.

Last Update: I finaly found the real cause for this:
When setting up your User DSN in the ODBC Data Source Administrator,
make sure you uncheck "Use regional settings when outputting currency, numbers, dates and times"!

Cheers,
jonix

Replies are listed 'Best First'.
Re: Avoiding Strange Win32::ODBC Return Values for Integers
by thor (Priest) on Oct 28, 2005 at 13:57 UTC
    Hmm...that's weird. Let's take it back a step, though. What happens if you try to write to a "normal" format (i.e. a text file). Something like what you have commented out in your while loop. That might be illuminating as far as whether it's ODBC giving you bad values, or it's Spreadsheet::WriteExcel interpretting them in some odd-ball way.

    A third possibility is that you have to format those cells into which you're putting numeric data to actually be numbers. I seem to remember the S::WE module being fairly smart about that, though...

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

[reply]
      The output is the same if I just print it - so it is what I get from ODBC already.
      I just checked the SQL Server table properties again - I really do a sum() over a field defined as int.
[reply]
        So you're saying that you get a 57 with some strange character after it when you just print it as text?

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

[reply]
Re: Avoiding Strange Win32::ODBC Return Values for Integers
by LanceDeeply (Chaplain) on Oct 28, 2005 at 14:51 UTC
    your $format may not be set correctly. first try explicitly calling write_number() for that specific column. see the docs

    -hth
[reply]
[d/l]
[select]
      In the docs it tells me:

      "The "looks like" rule is defined by regular expressions:
      write_number() if $token is a number based on the following regex: $token =~ /^(+-?)(?=\d|\.\d)\d*(\.\d*)?(Ee(+-?\d+))?$/."

      So I changed my code from
      $worksheet->write($row, $col++, $_, $format) for @data;

      [download]
      to
      for (@data) {
    if ($_ =~ m/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/){
        print;
        $worksheet->write_number($row, $col++, $_, $format);
    }else{
        $worksheet->write($row, $col++, $_, $format)
    }
}

      [download]
      It never printed, so it might be a glitch in the "looks like" rules?

      Cheers,
      jonix
[reply]
[d/l]
[select]
        do you know which col# is supposed to be a number?
        i was thinking that you try something like this first:
        if ( $col == 42 )
{
    $worksheet->write_number($row, $col++, $_);
}
else
{
    $worksheet->write($row, $col++, $_, $format);
}

        [download]
        and once you're sure 57 is coming back from your query into the sheet, then you can start playing around with $format and your regex
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom