Subversion Repositories eFlore/Projets.eflore-projets

#!/usr/bin/perl

# $Date: 2005/10/19 10:14:00 $

# Excel to SQL statements translator.

# Needs the Spreadsheet::ParseExcel module from CPAN

#

# Example usage:

#

# xls2sql.pl help

# xls2sql.pl file=6960_TS_Bressanone_Brixen.xls \

#   coldefs="year INTEGER,MEAN1 DOUBLE PRECISION,MEAN2 DOUBLE PRECISION,MEAN3 DOUBLE PRECISION"

#

# Released under GPLv2.0

# by Daniel Calvelo Aros (dca@users.sf.net)

#

# few modifications by MN (why not reading 'Learning Perl' O'Reilly book)

#use strict;

use English;

use Spreadsheet::ParseExcel;

# gparser-like options:

#          option label => [ref to var to hold opt value, "description", default value or undef for no default]

%gopts = ( file   => [\$file,     "Name of input excel spreadsheet file", undef],

	   skip   => [\$skip,     "Number of rows to skip at the beginning of sheet", 0],

	   table  => [\$tablename,"Name of output table", "mytable"],

	   rows   => [\$nrows,    "Number of rows to extract", undef],

	   sheet  => [\$sheetn,   "Sheet number to convert from the workbook, counting from 1", 1],

	   coldefs=> [\$coldefs,  "Column definitions as in SQL", "auto"],

	   nodata => [\$nodata,   "No data character(s) used in the Excel table", ""],

	   help   => [\$help,     "Help, of course", undef],

	   debug  => [\$DEBUG,    "Debugging flag, for developers only", 0]);

#function defined below:

&parse_opts();

#-- Open and look for obvious errors

my $wkbk =

  Spreadsheet::ParseExcel::Workbook->Parse($file);

if( !defined $wkbk->{Worksheet}) {die "Error:couldn't parse file $file\n"}

my($iR, $iC, $sheet, $ncols, $roffset, $rsize);

$sheet = @{$wkbk->{Worksheet}}[--$sheetn]; #-- Numbering starts at 1 for the user

$ncols = $sheet->{MaxCol} or die "Error:the specified sheet $sheetn does not contain data\n";

$ncols -= $sheet->{MinCol} if defined $sheet->{MinCol} ;

$roffset = $sheet->{MinRow}-1;

$rsize = $sheet->{MaxRow} - $sheet->{MinRow};

die "Error:the specified worksheet seems to contain only one line\n" if $rsize == 0;

$roffset += $skip;

$lastrow = ( defined $nrows

	     ? $nrows + $roffset -1

	     : $sheet->{MaxRow} );

die "Invalid skip option: the sheet only has $rsize rows" if $roffset >= $rsize - 1;

my (@types, @sqltypes, @firstrow, @titlerow);

if($coldefs ne "auto"){

  #-- We have user-defined column definitions

  #-- Check them

  $coldefs =~ s/^\s*//;

  $coldefs =~ s/\s*$//;

  @defs = split ",", $coldefs;

  foreach $i (0..$#defs){

    ($colname, $typedef) = split /\s+/,$defs[$i],2;

    die "Column specification $i: can't parse SQL type definition '$typedef' (should be INTEGER, DOUBLE PRECISION, CHAR).\n" if $typedef !~ /INTEGER|DOUBLE PRECISION|CHAR/i;

    die "Column name '$colname' for column $i contains spurious characters (no spaces permitted in list).\n" if $colname !~ /[a-zA-Z][a-zA-Z_0-9]*/;

    push @sqltypes, $typedef;

    push @titles, $colname;

  }

}else{

  #-- Inspect file for types:

  #-- First estimate initial types from the first row of data

  @firstrow = @{$sheet->{Cells}[$roffset+1]};

  @types = map { $_->{Type}} @firstrow;

  %cvt = (Text=>'CHAR',Numeric=>'INTEGER',Date=>'DOUBLE');

  @sqltypes = map { $cvt{$_} } @types;

  @lens = map { 0 } @types;

  print STDERR "\nTypes:", join ";", @types if $DEBUG;

  print STDERR "\nInitial sqltypes:", join ";", @sqltypes if $DEBUG;

  #-- Then adjust widths and numeric type from the data

  for(my $iR = $roffset ; $iR <= $lastrow ; $iR++) {

    for(my $iC = $sheet->{MinCol} ;$iC <= $sheet->{MaxCol} ; $iC++) {

      $cell = $sheet->{Cells}[$iR][$iC];

      next if !defined $cell;

      $cellvalue = $cell->Value;

      if($types[$iC] eq 'Text'){

	$thislength = length( $cellvalue );

	$lens[$iC] = $thislength if $thislength > $lens[$iC];

      }else{

	if( $cellvalue =~ /[\.,]/ ){

	  $sqltypes[$iC] = 'DOUBLE PRECISION';

	}

	if( $cellvalue =~ /[a-df-z]/ ){

	  $sqltypes[$iC] = 'CHAR'; $lens[$iC] = length( $cellvalue);

	}

      }

    }

  }

  foreach $i (0..$#sqltypes){

    if( $sqltypes[$i] eq 'CHAR' ){

      $sqltypes[$i] .= "($lens[$i])";

    }

  }

  print STDERR "\nAdjusted sqltypes:", join ";", @sqltypes if $DEBUG;

  #-- Generate field names from the title row

  @titlerow = @{$sheet->{Cells}[$roffset]};

  print STDERR "\nTitlerow:", join ";", map { defined $_ ? $_->Value : "" } @titlerow if $DEBUG;

  $varname = "V000";

  @titles = map {

    /^[^a-zA-Z]/ ? $varname++ : $_

  } map {

    if( defined $_ && length > 0 ) {$_=$_->Value;y/a-z/A-Z/;s/[^a-zA-Z_0-9]/_/g}

    else { $_=$varname++ }

    $_;

  } @titlerow;

  map { $istitle{$_}++ } @titles;

  foreach $i (reverse 0..$#titles){

    if( $istitle{$titles[$i]} > 1){

      $titles[$i] .= --$istitle{$titles[$i]};

    }

  }

  while( $#titles < $ncols ){ #Missing titles, according to the size of the sheet

    push @titles, $varname++;

    push @sqltypes, "CHAR(32)";

  }

  print STDERR "\nTitles:" ,join ";", @titles if $DEBUG;

  print STDERR "\n" if $DEBUG;

}

#-- Write out

print "CREATE TABLE $tablename (";

print join ",", map {"$titles[$_] $sqltypes[$_]"} (0..$#titles);

print ");\n";

if($coldefs eq "auto"){

  $lastcol = $sheet->{MaxCol};

}else{

  $lastcol = $#sqltypes + $sheet->{MinCol};

  foreach $i (reverse 0..$#sqltypes){

    $sqltypes[$i + $sheet->{MinCol}] = $sqltypes[$i];

  }

}

for(my $iR = $roffset+1 ; $iR <= $lastrow ; $iR++) {

  print "INSERT INTO $tablename VALUES(";

  print join ",", map {

    my $c = $sheet->{Cells}[$iR][$_];

#    defined $c ? '"'.&cast($c->Value,$sqltypes[$_]).'"' : NULL

    defined $c ? ''.&cast($c->Value,$sqltypes[$_]).'' : NULL

  } ($sheet->{MinCol}..$lastcol);

  print ");\n"

}

sub cast($$){

  my ($value, $sqltype) = @_;

  if( length($value)>0 ){

    if ($value eq $nodata){

      $value = "NULL";  # no data coded with char

    }else{

      if( $sqltype =~ /CHAR\s*\((\d+)\)/i ){

        $value =~ s/[\n\r]/ /gm;

        $value =~ s/"/\\"/g;

        $value =~ s/'/\\'/g;

#        $value = substr( $value, 0, $1 );

        $value = '\''.substr( $value, 0, $1 ).'\'';

      }elsif( $sqltype =~ /DOUBLE PRECISION/i ){

        $value += 0;

      }elsif( $sqltype =~ /INTEGER/i ){

        $value = int $value;

      }else{

        die "Unknown SQL type '$sqltype'; can't typecast '$value' to that type.\n";

      }

    }

  }else{

      $value = "NULL";  # no data

  }

}

sub parse_opts(){

  for $o (sort keys %gopts){

    if( defined $gopts{$o}[2] ){

      ${$gopts{$o}[0]} = $gopts{$o}[2];

    }

    for $arg (@ARGV){

      $arg =~ /^\Q$o\E(?:\s*=\s*(.+)$)?/;

      if( length($1)>0 ){

	${$gopts{$o}[0]} = $1;

      }elsif( $& ){

	${$gopts{$o}[0]} = 1;

      }

    }

  }

  if($help){

    select STDERR;

    print "\n$PROGRAM_NAME : extract sheets from an excel workbook and\n";

    print "produce SQL statements that create the database\n";

    print "\nArguments (use grass style, i.e. arg=value):\n";

    foreach (keys %gopts){ $longest = $longest < length() ? length() : $longest }

    foreach $arg (grep {!/help/} keys %gopts){

      print "  $arg".(" "x($longest+2-length $arg));

      print $gopts{$arg}[1];

      print " (default: ".$gopts{$arg}[2].")" if defined $gopts{$arg}[2];

      print "\n";

    }

    select STDOUT;

    die "\n";

  }

}