CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Failing to Pass formatted data

 



vsmeruga
Novice

Apr 28, 2014, 7:08 AM

Post #1 of 6 (9072 views)
Failing to Pass formatted data Can't Post

Hi Gurus


I am an architect and I am almost new to perl scripting
I am seeing a problem while running the script. I am trying to get the date in "yymmdd" format and pass it to the filename variable.



I would like to capture date and replace it with $filedt in the source file name. Please help me on this. tried many ways but not succeded



But this works when I try to capture just the date as a separate script. But it is not working in this main script.





Code
 
#!/usr/bin/perl -w
# For each tab (worksheet) in a file (workbook),
# spit out columns separated by ",",
# and rows separated by c/r.

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw(ExcelFmt);
use Time::Local;
use POSIX qw/strftime/;

use strict;
use warnings;

my $file='nposx.csv';
my $datefmt = 'yyyy-mm-dd'; # ISO 8601
my $filedt = strftime '%y%m%d', localtime;

open STDOUT, ">", $file or die "$0: open: $!";
open STDERR, ">&STDOUT" or die "$0: dup: $!";

my $filename = shift || "/home/etc/srcfile-$filedt-001.xls";


my $excel = Spreadsheet::ParseExcel->new();
my $eBook = $excel->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
$eSheet = $eBook->{Worksheet}[$sheet];
$sheetName = $eSheet->{Name};
#print "#Worksheet $sheet: $sheetName\n";
next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCol})));
foreach my $row ($eSheet->{MinRow}+6 .. $eSheet->{MaxRow}) {
foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
my $cell = $eSheet->{Cells}[$row][$column];
if (defined $cell){
if (defined $cell->{Type} && $cell->{Type} eq 'Date'){
print ExcelFmt($datefmt, $cell->unformatted() ) . ",";
} else {
print $cell->value() . ",";
}
} else {
print ",";
}
}
print "\n";
}
}



(This post was edited by FishMonger on Apr 28, 2014, 7:53 AM)


Laurent_R
Veteran / Moderator

Apr 28, 2014, 8:54 AM

Post #2 of 6 (9063 views)
Re: [vsmeruga] Failing to Pass formatted data [In reply to] Can't Post

Hi,

I think that possibly your program does not see the name of the variable ends when you contruct the file name.


instead of the following string:

Code
"/home/etc/srcfile-$filedt-001.xls"

try to use this:

Code
"/home/etc/srcfile-${filedt}-001.xls"


But it would be easier for us to help you if you explained what does not work, which error message you get, etc.


(This post was edited by Laurent_R on Apr 28, 2014, 8:58 AM)


vsmeruga
Novice

Apr 28, 2014, 8:58 AM

Post #3 of 6 (9059 views)
Re: [Laurent_R] Failing to Pass formatted data [In reply to] Can't Post

<p>

Thanks for the reply.

I changed it as below: no luck. got this message in output file:nposx.csv("Use of uninitialized value $sheets in subtraction (-) at ./perl_excel_xls_to_csv_nposx.pl line 30.")

</p>

<code>

my $filename = shift || "/home/vmeruga_alt/NPOSX____-ERM__-NC-BAC__-${filedt}-001.xls";

</code>


Laurent_R
Veteran / Moderator

Apr 28, 2014, 10:05 AM

Post #4 of 6 (9026 views)
Re: [vsmeruga] Failing to Pass formatted data [In reply to] Can't Post

It appears that your problem may have nothing to do with the date problem you were mentionning. (Although it may...)

The error seems to happen here:

Code
foreach my $sheet (0 .. $sheets - 1) {

where the variable $sheets is undefined, meaning that this other line:

Code
my $sheets = $eBook->{SheetCount};

is not succeeding.

This is turns probably means that you don't succeed to open the speadsheet file. I have not used speadsheet modules for many years, I can't help you much more on this part, but there must be in the module something on how you can check if your file has been successfully open. Make that check.

Also try to print out your important variables (file name, etc.) to try to see what gets wrongly populated.


vsmeruga
Novice

Apr 29, 2014, 3:54 AM

Post #5 of 6 (8692 views)
Re: [Laurent_R] Failing to Pass formatted data [In reply to] Can't Post

<p>

Hi I am seeing bigger problem now.

My source file is having values to amount field as "-130,730 ". Seems like they use 1000 separator in the cell format. And -130 and 730 are stored as separate field values. Can you suggest some solution to get rid of the problem.

Thanks

</p>


vsmeruga
Novice

Apr 29, 2014, 7:58 AM

Post #6 of 6 (8598 views)
Re: [vsmeruga] Failing to Pass formatted data [In reply to] Can't Post

<p>

Hi Gurus

I tackled this using different way. Instead of making comma separated, I did as pipe separated and got rid of the issue. Now it creates required number of columns.

Thanks for your suggestions.

</p>

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives