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

Hey guys ive been working on a code to convert CSV to XLS how ever i cant figure out how to just output 10 rows of data per sheet. my objective-create a program that will convert a csv to xls and if the number of rows exceed lets say 10 then the 11th row will be the first in the next(newly created worksheet). please don't give me references to pre made programs... i want to learn :) please fix this code and help me out..thanks in advance 
  #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;
    use Data::Dumper;

#count the number of records
use strict;
open(IN, "t.txt");
my @str = <IN>;
close(IN);
print scalar(@str);
#end counting the no.of records

open (TABFILE, 't.txt') or die "t.txt: $!";

my $worksheetno=(int(@str/10))+1;
print "\n$worksheetno\n";

    my $workbook  = Spreadsheet::WriteExcel->new('tab.xls');


    my $row = 0;
    my $rowcount=0;


     while($worksheetno>0)
      {
      my $worksheet = $workbook->add_worksheet();
       while (<TABFILE>) {


       chomp;
        my @Fld = split(',');
        print "@Fld\n";

        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
            $col++;
            
        }
        $row++; }

        open (TABFILE, 't.txt') or die "t.txt: $!";
        $row=0;

      $worksheetno--;
    }

[download]

Replies are listed 'Best First'.
Re: CONVERTING CSV TO XLS
by Tux (Canon) on May 26, 2014 at 06:25 UTC

    Fragile code. Your CSV parsing is using split which is bound to break.

    What you want to do is already done in csv2xls which comes with Text::CSV_XS. You can learn by reading the code. It is Open Source.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: CONVERTING CSV TO XLS
by Anonymous Monk on May 26, 2014 at 06:26 UTC
[reply]
Re: CONVERTING CSV TO XLS
by mayanks94 (Initiate) on May 26, 2014 at 10:59 UTC
    Okay i re did the code can any monk please help me in extracting the array contents in such a way that each sheet will have data of the text files in rows like 0-10(1 worksheet),11-20(2nd worksheet)..... currently my code is giving an output of 0-10 rows of the text file in all the worksheets :S because of an incorrect method of data extraction from the array 
     
#!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;


#count the number of records
use strict;
open(IN, "t.txt");
my @str = <IN>;
close(IN);
print scalar(@str);
#end counting the no.of records

open (TABFILE, 't.txt') or die "t.txt: $!";

my $worksheetno=(int(@str/10))+1;
print "\n$worksheetno\n";

    my $workbook  = Spreadsheet::WriteExcel->new('tabnew.xls');


    my $row = 0;
    my $rowcount=0;


     while($worksheetno>0)
      {
      my $worksheet = $workbook->add_worksheet();
       while (<TABFILE>) {

      if ($row>9)
      {last;}


       chomp;
        my @Fld = split(',');
        print "@Fld\n";

        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
            $col++;

        }
        $row++; }

        open (TABFILE, 't.txt') or die "t.txt: $!";
        $row=0;

      $worksheetno--;
    }

    [download]
[reply]
[d/l]

      The code isn't working because you re-open the TABFILE at every new sheet. Another problem is that exiting the while loop with last discards a line of data just read in from TABFILE.

      Try this;

      #!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $MAX_ROW = 9;

my $workbook  = Spreadsheet::WriteExcel->new('tabnew.xls');
my $worksheet = $workbook->add_worksheet();
my $row = 0;
my $sheetno = 1;
print "Sheet $sheetno\n";

open (TABFILE, 't.txt') or die "t.txt: $!";
while (<TABFILE>) {
  if ($row > $MAX_ROW){
    $worksheet = $workbook->add_worksheet();
    ++$sheetno;
    $row = 0;
    print "Sheet $sheetno\n";
  }

  chomp;
  my @Fld = split(',');
  print "Row $row = @Fld\n";
  $worksheet->write_row($row, 0, \@Fld);
  $row++; 
}

      [download]
      poj
[reply]
[d/l]
[select]
Re: CONVERTING CSV TO XLS
by mayanks94 (Initiate) on May 26, 2014 at 07:02 UTC
    I went through this code https://metacpan.org/source/HMBRAND/Text-CSV_XS-1.08/examples/csv2xls/csv2xls I am really new to perl and i need to make this program ASAP so can you please tell me in the above code how i can convert my t.txt to tab.xls(i mean where should i enter the filenames in the code above hwat changes i have to make please help im too noob :) )
[reply]

      Install Text::CSV_XS, from a command prompt:

      cpan Text::CSV_XS

      [download]

      Install other modules required by the csv2xls tool:

      cpan Date::Calc
cpan Spreadsheet::WriteExcel

      [download]

      When you run csv2xls without any arguments it'll display the usage information.

[reply]
[d/l]
[select]
        Thanks! Is there any way i could.. instead of going till the end of the tabfile (while (<TABFILE>)) go till a variable which has initial value of lets say 10 and the in the next iteration(i increment that variable by 10) so my file now is read from 10 to 20... then 20-30 and so on and so forth ? if such a method is possible i believe my code would work ! the question is.. is it possible ?
[reply]
Re: CONVERTING CSV TO XLS
by mayanks94 (Initiate) on May 27, 2014 at 06:12 UTC
    Thanks guys ! POJ i figured out the problem yesterday itself :) Unfortunately members like Corion forced me to shut the web page immediately otherwise i would have posted the new code. Except him everyone was very kind ! I am starting to love Perl and will soon start contributing on this website too :) Thanks Again :)
[reply]

Back to Seekers of Perl Wisdom