extraer TSV de todas las hojas de un libro excel

Hola,
cuando intercambiamos datos en al laboratorio a menudo usamos libros Excel, en formato XLSX, con varias hojas. Ahora bien, si luego queremos convertirlos a ficheros con valores separados por tabuladores (TSV) o comas (CSV), MS Excel sólo te permite hacerlo de hoja en hoja, lo cual es un poco latoso para libros gordos. Aquí os pongo un script en Perl para hacer esta tarea desde el terminal, que requiere instalar el módulo Spreadsheet::ParseXLSX, por ejemplo con:

$ sudo cpan -i Spreadsheet::ParseXLSX

El código es:

#!/usr/bin/perl -w
# get separate tab-separated (TSV) files from sheets in Excel .xlsx file 
use strict;
use Spreadsheet::ParseXLSX;

die "# usage: $0 <infile.xlsx> \n" if(!$ARGV[0]);

my $parser = Spreadsheet::ParseXLSX->new();
my $book = $parser->parse($ARGV[0]);
foreach my $sheet (@{$book->{Worksheet}})
{
  open(TSV,'>',"$sheet->{'Name'}.tsv") || 
    die "# cannot create $sheet->{'Name'}.tsv: $!\n";

  foreach my $row ($sheet->{'MinRow'} .. $sheet->{'MaxRow'})
  {
    foreach my $col ($sheet->{'MinCol'} ..  $sheet->{'MaxCol'})
    {
      print TSV "$sheet->{'Cells'}->[$row]->[$col]->{'Val'}\t";
    } print TSV "\n";
  }

  close(TSV);
}

Si guarmos el código en un fichero de nombre xlsx2multitab.pl, podemos invocarlo de la siguiente manera:

$ perl xlsx2multitab.pl libro.xlsx

En el directorio actual se guardará un fichero .tsv por cada hoja del libro,
hasta luego,
Bruno

merge TSV files as Excel sheets (multitab2xls.pl)

Buenas,
hoy me he vuelto a encontrar con una tarea reincidente, la de juntar varios ficheros con datos separados por tabuladores (formato TSV) en un sólo fichero Excel para compartirlo más fácilmente con otros colegas.  Como decía esto me pasa con cierta frecuencia, y por eso hoy dediqué 5 minutos a escribir unas líneas de Perl que permiten automatizar esta tarea, previa instalación del módulo Spreadsheet::WriteExcel .

Para instalar dicho módulo es suficiente con:

$ sudo cpan -i  Spreadsheet::WriteExcel

El código del script multitab2xls.pl es el siguiente:

 #!/usr/bin/perl -w  
 use strict;  
 use Spreadsheet::WriteExcel;  
 use File::Basename;  
 die "# usage: $0 <target.xls> <*.tab> <*.tsv>\n" if(!$ARGV[1]);  
 my ($xlsname,$files) = (shift(@ARGV),0);  
 my $xlsbook = Spreadsheet::WriteExcel->new($xlsname);  
 my $title_sheet = $xlsbook->add_worksheet('original files');  
 foreach my $tabfile (@ARGV) {  
     $title_sheet->write($files++,0,$tabfile);  
   my $sheet = $xlsbook->add_worksheet(substr(basename($tabfile,qw(.tsv .tab)),-30));  
     open(TAB,$tabfile) || die "# cannot read $tabfile, exit\n";  
     while(<TAB>) {  
         $sheet->write_row($.-1, 0, [split(/\t/)]);  
     }  
     close(TAB);  
 }  

Y se ejecutaría de esta manera:

$ perl multitab2xls.pl outfile.xls folder/*.tab 

Un saludo,
Bruno

Mezclando datos en un solo archivo Excel

A menudo me ha tocado intercambiar datos con colegas en formato Excel y uno de los problemas que he padecido es el de tener que ir juntando a mano diferentes conjuntos de datos en diferentes hojas del mismo libro de cálculo. Una molestia añadida es la necesidad de importar archivos en formato texto, como columnas separadas por tabuladores, por ejemplo, en el mismo libro. Para automatizar este tipo de tareas y usar Excel/OpenOffice realmente sólo para el análisis de los datos, podemos echar mano por ejemplo del módulo Spreadsheet del CPAN. El mismo módulo puede servir además para leer y procesar datos directamente desde archivos Excel.

Volviendo al problema descrito arriba, supongamos por ejemplo que queremos unir en un sólo archivo excel tres archivos (datos1.xls, datos2.xls y datos3.tab) en uno solo que llamaremos todos_datos.xls . Pues bien, por medio del programa descrito debajo sería tan sencillo como ejecutar en el terminal:

 $ ./my_xls_merge.pl todos_datos.xls datos1.xls datos2.xls datos3.tab

Yo lo he probado en Linux y los archivos generados se leen perfectamente con el MS Excel, con la única pega de que se pierden las gráficas que hubiera ya en los archivos de entrada.

El código es el siguiente:

 #!/usr/bin/perl -w  
   
 # programa 9 my_xls_merge.pl   
 # programita para unir hojas de diferentes archivos .xls o .tab en un solo libro excel
 # OJO: conserva el valor de cada celda de una hoja de calculo, no las formulas,   
 # y se pierden los diagramas por ejemplo  
 # inspirado por http://www.perlmonks.org/?node_id=743574  
   
 use strict;  
 use Spreadsheet::ParseExcel;  
 use Spreadsheet::WriteExcel;   
 use File::Basename;  
   
 my $MAXLENGTHSHEETNAME = 31;  
   
 die "# usage: $0 <outfile.xls> <file1.xls> ... <fileN.xls>\n" if(scalar(@ARGV) < 2);  
   
 my @infiles = @ARGV;  
 my $outfile = shift(@infiles);  
   
 if(-e $outfile)  
 {  
    print "# a file named '$outfile' already exists, overwrite? (Y/N)\n";  
    my $overwrite = <STDIN>;  
    if($overwrite ne "Y\n"){ exit }  
    else{ unlink($outfile) }  
 }  
   
 # instantiate resulting excel object  
 my $outbook = Spreadsheet::WriteExcel->new($outfile) ||   
    die "# $0: cannot create '$outfile': $!";  
   
 for my $file (@infiles)   
 {  
    if(!-e $file)  
    {  
       print "# skipping non-existing file '$file'\n";  
       next;  
    }  
   
    # remove path   
    my ($filename,$copysheet_name) = (fileparse($file));   
      
    my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file);  
    if(defined($excel->{'File'})) # excel format infiles  
    {  
       foreach my $sheet (@{$excel->{Worksheet}})   
       {   
          $copysheet_name = $sheet->{'Name'} . '-' . $filename;  
          if(length($copysheet_name) > $MAXLENGTHSHEETNAME)  
          {  
             print "# abbreviating $copysheet_name to ";  
             $copysheet_name = substr($copysheet_name,0,$MAXLENGTHSHEETNAME-3).'...';  
             print "$copysheet_name\n";  
          }  
         print "# adding '$file' sheet '$sheet->{'Name'}' ($copysheet_name)\n";  
   
        my $copysheet = $outbook->add_worksheet($copysheet_name);  
            
          $sheet->{'MaxRow'} ||= $sheet->{'MinRow'};  
          foreach my $row ($sheet->{'MinRow'} .. $sheet->{'MaxRow'})   
          {  
               my @rowdata = map { $sheet->{'Cells'}->[$row]->[$_]->{'Val'} }   
                      $sheet->{'MinCol'} .. $sheet->{'MaxCol'};  
          $copysheet->write($row,0,\@rowdata);   
          }  
       }  
    }  
    elsif($file =~ /\.tab/)# .tab infiles, seria trivial manejar .csv por ejemplo  
    {  
       $copysheet_name = $filename;  
       print "# adding '$file' no_sheet ($copysheet_name)\n";  
         
       my $copysheet = $outbook->add_worksheet($copysheet_name);  
         
       open(INTAB,$file) || die "# $0 : cannot read '$file': $!";  
       while(<INTAB>)  
       {  
          chomp($_);  
          my @tabdata = split(/\t/,$_);  
          $copysheet->write($.-1,0,\@tabdata);  
       }  
       close(INTAB);  
    }  
 }  
   
 $outbook->close();  
 print "# outfile: $outfile\n";   
   

Un saludo