X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FParser%2FExcel.pm;h=846095a4ef776b39065c9a6b3f298cb47f3fac43;hb=c0ec0e22d3f0e3852c00daac5ef5763010b410c3;hp=3796baf85a924ae591883508d55a5ad32b33b146;hpb=8c7f5c7b14f9cecc8440b04c98b0eb9a35d64b17;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Parser/Excel.pm b/lib/SQL/Translator/Parser/Excel.pm index 3796baf..846095a 100644 --- a/lib/SQL/Translator/Parser/Excel.pm +++ b/lib/SQL/Translator/Parser/Excel.pm @@ -1,26 +1,5 @@ package SQL::Translator::Parser::Excel; -# ------------------------------------------------------------------- -# Copyright (C) 2003 Ken Y. Clark , -# darren chamberlain , -# Chris Mungall , -# Mike Mellilo -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU General Public License as -# published by the Free Software Foundation; version 2. -# -# This program is distributed in the hope that it will be useful, but -# WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -# General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA -# 02111-1307 USA -# ------------------------------------------------------------------- - =head1 NAME SQL::Translator::Parser::Excel - parser for Excel @@ -28,21 +7,32 @@ SQL::Translator::Parser::Excel - parser for Excel =head1 SYNOPSIS use SQL::Translator; - use SQL::Translator::Parser::Excel; my $translator = SQL::Translator->new; - $translator->parser("SQL::Translator::Parser::Excel"); + $translator->parser('Excel'); =head1 DESCRIPTION -Parses an Excel spreadsheet file for SQL::Translator. You can then -turn the data into a database tables or graphs. +Parses an Excel spreadsheet file using Spreadsheet::ParseExcel. + +=head1 OPTIONS + +=over + +=item * scan_fields + +Indicates that the columns should be scanned to determine data types +and field sizes. True by default. + +=back =cut use strict; -use vars qw($DEBUG $VERSION @EXPORT_OK); +use warnings; +our ($DEBUG, @EXPORT_OK); $DEBUG = 0 unless defined $DEBUG; +our $VERSION = '1.59'; use Spreadsheet::ParseExcel; use Exporter; @@ -66,12 +56,12 @@ my %ET_to_ST = ( # ------------------------------------------------------------------- sub parse { my ($tr, $data) = @_; + my $args = $tr->parser_args; my $filename = $tr->filename || return; my $wb = Spreadsheet::ParseExcel::Workbook->Parse( $filename ); my $schema = $tr->schema; my $table_no = 0; - my %parsed; my $wb_count = $wb->{'SheetCount'} || 0; for my $num ( 0 .. $wb_count - 1 ) { $table_no++; @@ -81,32 +71,14 @@ sub parse { my @cols = $ws->ColRange; next unless $cols[1] > 0; - $parsed{ $table_name } = { - table_name => $table_name, - type => undef, - indices => [ {} ], - fields => { }, - }; my $table = $schema->add_table( name => $table_name ); + my @field_names = (); for my $col ( $cols[0] .. $cols[1] ) { my $cell = $ws->Cell(0, $col); my $col_name = normalize_name( $cell->{'Val'} ); my $data_type = ET_to_ST( $cell->{'Type'} ); - - $parsed{ $table_name }{'fields'}{ $col_name } = { - type => 'field', - order => $col, - name => $col_name, - data_type => $data_type, - size => [ 255 ], - null => 1, - default => '', - is_auto_inc => undef, - - # first field is the primary key - is_primary_key => ($col == 0) ? 1 : undef, - }; + push @field_names, $col_name; my $field = $table->add_field( name => $col_name, @@ -122,9 +94,84 @@ sub parse { $field->is_primary_key(1); } } + + # + # If directed, look at every field's values to guess size and type. + # + unless ( + defined $args->{'scan_fields'} && + $args->{'scan_fields'} == 0 + ) { + my %field_info = map { $_, {} } @field_names; + + for( + my $iR = $ws->{'MinRow'} == 0 ? 1 : $ws->{'MinRow'}; + defined $ws->{'MaxRow'} && $iR <= $ws->{'MaxRow'}; + $iR++ + ) { + for ( + my $iC = $ws->{'MinCol'}; + defined $ws->{'MaxCol'} && $iC <= $ws->{'MaxCol'}; + $iC++ + ) { + my $field = $field_names[ $iC ]; + my $data = $ws->{'Cells'}[ $iR ][ $iC ]->{'_Value'}; + next if !defined $data || $data eq ''; + my $size = [ length $data ]; + my $type; + + if ( $data =~ /^-?\d+$/ ) { + $type = 'integer'; + } + elsif ( + $data =~ /^-?[,\d]+\.[\d+]?$/ + || + $data =~ /^-?[,\d]+?\.\d+$/ + || + $data =~ /^-?\.\d+$/ + ) { + $type = 'float'; + my ( $w, $d ) = + map { s/,//g; length $_ || 1 } + split( /\./, $data ) + ; + $size = [ $w + $d, $d ]; + } + else { + $type = 'char'; + } + + for my $i ( 0, 1 ) { + next unless defined $size->[ $i ]; + my $fsize = $field_info{ $field }{'size'}[ $i ] || 0; + if ( $size->[ $i ] > $fsize ) { + $field_info{ $field }{'size'}[ $i ] = $size->[ $i ]; + } + } + + $field_info{ $field }{ $type }++; + } + } + + for my $field ( keys %field_info ) { + my $size = $field_info{ $field }{'size'} || [ 1 ]; + my $data_type = + $field_info{ $field }{'char'} ? 'char' : + $field_info{ $field }{'float'} ? 'float' : + $field_info{ $field }{'integer'} ? 'integer' : 'char'; + + if ( $data_type eq 'char' && scalar @$size == 2 ) { + $size = [ $size->[0] + $size->[1] ]; + } + + my $field = $table->get_field( $field ); + $field->size( $size ) if $size; + $field->data_type( $data_type ); + } + } } - return \%parsed; + return 1; } sub ET_to_ST { @@ -146,11 +193,11 @@ sub ET_to_ST { =head1 AUTHORS Mike Mellilo , -darren chamberlain Edlc@users.sourceforge.netE -Ken Y. Clark Ekclark@cpan.orgE +darren chamberlain Edlc@users.sourceforge.netE, +Ken Y. Clark Ekclark@cpan.orgE. =head1 SEE ALSO -perl(1), Spreadsheet::ParseExcel. +Spreadsheet::ParseExcel, SQL::Translator. =cut