Shell Programming and Scripting

Hi Guys,

I have used Perl scripting to convert XLSX file to TXT file using Perl module Spreadsheet::XLSX.

I processed one XLSX file having one column and 65k rows of data .

Strangely ,It is merging data for every 2047 row and I could see data in TXT file as Ex: Suppose in XLSX file ,if 2047 row data is A and 2048 row data is B .
In respective converted TXT file ,for 2047 row data is AB (merging 2047 +2048 data of XLSX ) which it should not print like this .it should print 2047 row as A and 2048 row as B in converted TXT file .

This is the same scenario for every 2047 records repetively in the same converted TXT file
At 4094 row in TXT - merging 4094 + 4095 data of XLSX
At 6141 row in TXT - merging 6141 + 6142 data of XLSX

So finally,comparing with XLSX file records and converted TXT file records of the above file(65K rows), Iam missing around 40 to 60 records/rows in TXT file.

I tried using changing the TXT file to UTF-8 format ,but no luck.

Could anyone please let me know solution to overcome this .
For refernece below is the Perl script used for conversion ,

Code:

#!/usr/bin/perl -w
#main.pl

use POSIX;

@files =<home/*.XLSX>;

foreach $file (@files)

 {
      print $file . "\n";

      use Spreadsheet::XLSX;
      my $excel   = Spreadsheet::XLSX -> new ($file);
      my $filename= $file.".".TXT;       
      print "filename is $filename\n";



        foreach my $sheet (@{$excel -> {Worksheet}})
       {

        $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)
               {        
                 open(FILE, ">>$filename") || die("Couldn't open file");
                 printf(FILE "%s\r",$cell -> {Val});
                 close(FILE);  
               }

             }
           }
         }
  }

Thanks,
Rajk

I believe that \r should be an \n. Depending on how you view the file this may cause various problems.

Hi ,
Thanks for the reply..
Tried that ,\n is giving the new line character for every record and avoding 2047 line issue. And also it is randomly giving blank records .More importantly, I should read this converted TXT file form other application .And that application doesn't entertains any new characters expect the records (data) parsed .

Thanks.Raj

---------- Post updated at 03:10 AM ---------- Previous update was at 01:30 AM ----------

Hi Every one ,

Looking for suggestions or any quick responses on this ..If any one have the idea upon the issue .
Or ANyone who has encountered and debugged this issue ..please respond..

Thanks,
Raj