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:
Problem: Converting XLSX file to CSV

 



vsmeruga
Novice

Apr 29, 2014, 8:11 AM

Post #1 of 13 (9647 views)
Problem: Converting XLSX file to CSV Can't Post

<p>I am 3 days old to perl programming and I am trying hard to read the posts through google to my problems and finding solutions.</p>

<p>During that process I found a script for the solution and Implemented it with my source file.</p>

</p>I am seeing an issue with it and not able to understand how to resolve it.</p>

<p> Issue is: Source file has 45 fields and 21st field data is Blanks. </p>

<p>In the csv file output: Next fields(22nd fields) data is placed in 21st field and remaining follows.. Due to that output is wrong.</p>

<p> Please help me with this issue as I am new to Perl scripting </p>


Code
 
#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::XLSX;

open STDOUT, ">", "output.csv" or die "$0: open: $!";
open STDERR, ">&STDOUT" or die "$0: dup: $!";

my $excel = Spreadsheet::XLSX -> new ('/home/source/srcfile.xlsx');


my $line;
foreach my $sheet (@{$excel -> {Worksheet}}) {
#printf("Sheet: %s\n", $sheet->{Name});
$sheet -> {MaxRow} ||= $sheet -> {MinRow};
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
$sheet -> {MaxCol} ||= $sheet -> {MinCol};
foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) {
my $cell = $sheet -> {Cells} [$row] [$col];
if ($cell) {
$line .= "\"".$cell -> {Val}."\",";
}
}
chomp($line);
print "$line\n";
$line = '';
}
}



(This post was edited by FishMonger on Apr 30, 2014, 6:33 AM)


vsmeruga
Novice

Apr 30, 2014, 2:00 AM

Post #2 of 13 (9388 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

"Hi Gurus

Did I post this question in wrong place?. I haven't seen reply to it.



Thanks

VJ"


FishMonger
Veteran / Moderator

Apr 30, 2014, 6:50 AM

Post #3 of 13 (9275 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

If the goal is to convert the .xlsx file to a .csv file, why make it more complicated than it needs to be. Why not simply open the file in Excel and do a File -> Save As and save it in csv format?

I use OpenOffice and don't have Excel or an .xlsx file to be able to test your script but if you post a sample file as an attachment, I'll run a test to see if I can duplicate the issue and find a fix.


vsmeruga
Novice

Apr 30, 2014, 7:28 AM

Post #4 of 13 (9247 views)
Re: [FishMonger] Problem: Converting XLSX file to CSV [In reply to] Can't Post

I have to automate this. I can do file/save as manually every day.

Attached requested source file. "Rpt" and "Ref" fields has blank values.
Attachments: srcfile.xlsx (9.40 KB)


vsmeruga
Novice

Apr 30, 2014, 7:53 AM

Post #5 of 13 (9233 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

Sorry correction. I can't do File/Save As every day manually.


FishMonger
Veteran / Moderator

Apr 30, 2014, 8:35 AM

Post #6 of 13 (9230 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

The problem is with this section:

Code
            if ($cell) {  
$line .= "\"".$cell -> {Val}."\",";
}

If the cell is empty, then that conditional will be false and the empty field is not added to the $line string.

To fix that problem, you could add an else clause that adds an empty string and comma.

Here's an adjusted version which fixes the issue in the manner that I'd use and uses the Text::CSV_XS module to handle the csv formatting.


Code
#!/usr/bin/perl 

use strict;
use warnings;
use Text::CSV_XS;
use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX->new('srcfile.xlsx');
my $csv = Text::CSV_XS->new(
{
binary => 1,
eol => "\n",
always_quote => 1
}
);

my $csv_file = 'output.csv';
open(my $csv_fh, '>', $csv_file) or die "failed to open '$csv_file' <$!>";

foreach my $sheet (@{$excel->{Worksheet}}) {
$sheet->{MaxRow} ||= $sheet->{MinRow};

foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {

$sheet->{MaxCol} ||= $sheet->{MinCol};
my @row;
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
my $cell = $sheet->{Cells}[$row][$col];
my $value = defined $cell->{Val} ? $cell->{Val} : '';
push @row, $value;
}
$csv->print($csv_fh, \@row);
}
}
close $csv_fh;


Here's the output it generates.

Quote
"PId","Sta","Nar","FoUp","Rpt","Date","Order Time","Ref","IType","Input Time","Is SIX Reportable"
"123456","def","0","0","","41718","41718.338509953704","","abc","41718.33851400463","0"



FishMonger
Veteran / Moderator

Apr 30, 2014, 8:53 AM

Post #7 of 13 (9225 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

There is a side issue that I'd like to point out.

Redirecting STDERR to your csv file is not a good choice in error handling.

Since this will be an automated task, you should write status messages to a logfile. There are a number of good logger modules on cpan. IMO, the best choice is Log::Log4perl.
http://search.cpan.org/~mschilli/Log-Log4perl-1.43/lib/Log/Log4perl.pm

Using one of the logger modules simplifies the logging process and will give you consistent format.

I have some of my scripts running as cron jobs and they can also be run interactively. I have the logging configured to output normal info messages to the screen and logfile and if a fatal error occurs it also sends out an email.


(This post was edited by FishMonger on Apr 30, 2014, 8:57 AM)


vsmeruga
Novice

Apr 30, 2014, 9:22 AM

Post #8 of 13 (9219 views)
Re: [FishMonger] Problem: Converting XLSX file to CSV [In reply to] Can't Post

Thanks for the reply. I found the issue. my amount is displayed as "thousand separator" because of it single field is separated as 2 fields. I changed the code to pipe separated instead of comma and it worked. thanks!!


FishMonger
Veteran / Moderator

Apr 30, 2014, 9:34 AM

Post #9 of 13 (9215 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

That's one of the reasons why it's always better to use one of the CSV modules on cpan. The Text::CSV_XS module would have handled the "thousand separator" without having to change the delimiter.

You now need to be careful of fields that contain the | pipe char.


vsmeruga
Novice

May 1, 2014, 2:03 AM

Post #10 of 13 (9138 views)
Re: [FishMonger] Problem: Converting XLSX file to CSV [In reply to] Can't Post

Many Thanks. As I am very new to perl. I will try to understand and see what I can do with it.


vsmeruga
Novice

May 1, 2014, 2:09 AM

Post #11 of 13 (9136 views)
Re: [FishMonger] Problem: Converting XLSX file to CSV [In reply to] Can't Post

Hi Fish Monger

Thanks. I tried the script as it is you provided and I see the below error.

"Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/local/lib/perl5/site_perl/5.14.2/x86_64-linux /usr/local/lib/perl5/site_perl/5.14.2 /usr/local/lib/perl5/5.14.2/x86_64-linux /usr/local/lib/perl5/5.14.2 .) at ./script.pl line 4.
BEGIN failed--compilation aborted at ./script.pl line 4."

Can you help me on this error.



Many thanks


FishMonger
Veteran / Moderator

May 1, 2014, 6:03 AM

Post #12 of 13 (9050 views)
Re: [vsmeruga] Problem: Converting XLSX file to CSV [In reply to] Can't Post

That error means that you need to install the Text::CSV_XS module.


vsmeruga
Novice

May 2, 2014, 1:44 AM

Post #13 of 13 (8776 views)
Re: [FishMonger] Problem: Converting XLSX file to CSV [In reply to] Can't Post

Thanks. I will install it.

 
 


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

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