Excel to CSV Converter

[Selva Moorthy]

Hi All,

Recently I have an requirement to handle large excel in java where as got memory exception using Apache POI api.

Then I decided to covert excel to csv and then start form that, which failed since memory exception occurs while reading the input excel file.

So I looked for Perl to do the conversion since most of suggested. 

I am new to this perl and start digging into it and found various Perl module to do that.

In that I found Spreadsheet::ParseExcel and created blow pl to convert excel to csv. This once took 1-2 min to covert 60K record excel.

#!/usr/bin/perl

use strict;

use warnings;

use Spreadsheet::ParseExcel::Stream;

my $xls_file = $ARGV[0];

my $inp_path = ‘<input file directory>’;

my $out_path = ‘<output file directory>’;

my $xls = Spreadsheet::ParseExcel::Stream->new($inp_path.'/'.$xls_file);

my $cnt = 0;

my $cnt_file = 1;

my $aggregated_x = ();

my @data;

my $file_name;

my $cellVal;

my $sheet_name;

my $file_extension=".csv";

my $return_values="";

my @now = localtime(time);

while ( my $sheet = $xls->sheet() ) {

                $sheet_name = $sheet->name;

                $sheet_name =~ s/ /_/g;  

                $cnt_file = 1;

                while(my $row = $sheet->row){                    

                                @data = @$row;

                                foreach $cellVal (@data) {

                                                $aggregated_x .= "\"$cellVal\",";

                                }

                                chop($aggregated_x);

                                $aggregated_x .= "\n";

                                $cnt++;

                                if ($cnt==1){ write_header_file(); }

                                if($cnt==10000){ write_data_file(); }

                }

                if($cnt>0){ write_data_file(); }

}

print $return_values;

sub getFileName {

                my @values = split('\.', $xls_file);

                my $fileName = $_[1].'_'.$values[0].'_'.$_[0].'_';

                $fileName .= sprintf("%04d_%02d_%02d_%02d_%02d_%02d", $now[5]+1900,$now[4]+1,$now[3],$now[2],$now[1],$now[0]);

                return $fileName.$file_extension;

}

sub write_data_file{

                opendir (DIR, $out_path) or mkdir( $out_path);

                $file_name = getFileName($sheet_name,$cnt_file);

                chomp($aggregated_x);

                open (FILE, ">$out_path/$file_name");

                print FILE $aggregated_x;

                $return_values .= $file_name.',';

                $aggregated_x = ();

                $cnt_file++;

                $cnt=0;

}

sub write_header_file{

                opendir (DIR, $out_path) or mkdir( $out_path);

                $file_name = getFileName($sheet_name,'Header');

                chomp($aggregated_x);

                open (FILE, ">$out_path/$file_name");

                print FILE $aggregated_x;

                $return_values .= $file_name.',';

                $aggregated_x = ();             

}

[nv]

Hi,
I'm using the above code but i get the following errors:
Can't locate Coro.pm in @INC (@INC contains: D:/tools/lib/perl/510/ C:/Dwimperl/perl/site/lib C:/Dwimperl
/perl/vendor/lib C:/Dwimperl/perl/lib .) at C:/Dwimperl/perl/site/lib/Spreadsheet/ParseExcel/Stream/XLS.p
m line 8.
BEGIN failed--compilation aborted at C:/Dwimperl/perl/site/lib/Spreadsheet/ParseExcel/Stream/XLS.pm line
8.
Compilation failed in require at C:/Dwimperl/perl/site/lib/Spreadsheet/ParseExcel/Stream.pm line 31.


Kindly suggest ...

[Douglas Wilson]

On Wednesday, July 23, 2014 at 4:01:42 AM UTC-7, nv wrote:

Hi,
I'm using the above code but i get the following errors:
Can't locate Coro.pm in @INC

You did not properly install Spreadsheet::ParseExcel::Stream. You can not install the library by just copying the *.pm files. Google for how to install perl modules. I like to use cpanm.