Added sorting of tables, other cosmetic changes.
[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / Excel.pm
CommitLineData
7909b81e 1package SQL::Translator::Parser::Excel;
19de1991 2
3# -------------------------------------------------------------------
19de1991 4# Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>,
5# darren chamberlain <darren@cpan.org>,
7909b81e 6# Chris Mungall <cjm@fruitfly.org>,
7# Mike Mellilo <mmelillo@users.sourceforge.net>
19de1991 8#
9# This program is free software; you can redistribute it and/or
10# modify it under the terms of the GNU General Public License as
11# published by the Free Software Foundation; version 2.
12#
13# This program is distributed in the hope that it will be useful, but
14# WITHOUT ANY WARRANTY; without even the implied warranty of
15# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16# General Public License for more details.
17#
18# You should have received a copy of the GNU General Public License
19# along with this program; if not, write to the Free Software
20# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21# 02111-1307 USA
22# -------------------------------------------------------------------
23
24=head1 NAME
25
26SQL::Translator::Parser::Excel - parser for Excel
27
28=head1 SYNOPSIS
29
30 use SQL::Translator;
31 use SQL::Translator::Parser::Excel;
32
33 my $translator = SQL::Translator->new;
34 $translator->parser("SQL::Translator::Parser::Excel");
35
36=head1 DESCRIPTION
37
7909b81e 38Parses an Excel spreadsheet file for SQL::Translator. You can then
39turn the data into a database tables or graphs.
19de1991 40
41=cut
42
43use strict;
ab0aa010 44use vars qw($DEBUG $VERSION @EXPORT_OK);
45$DEBUG = 0 unless defined $DEBUG;
19de1991 46
7909b81e 47use Spreadsheet::ParseExcel;
19de1991 48use Exporter;
ab0aa010 49use SQL::Translator::Utils qw(debug normalize_name);
50
19de1991 51use base qw(Exporter);
52
53@EXPORT_OK = qw(parse);
54
8c7f5c7b 55my %ET_to_ST = (
56 'Text' => 'VARCHAR',
57 'Date' => 'DATETIME',
58 'Numeric' => 'DOUBLE',
59);
60
19de1991 61# -------------------------------------------------------------------
ab0aa010 62# parse($tr, $data)
63#
64# Note that $data, in the case of this parser, is unuseful.
65# Spreadsheet::ParseExcel works on files, not data streams.
66# -------------------------------------------------------------------
19de1991 67sub parse {
ab0aa010 68 my ($tr, $data) = @_;
8c7f5c7b 69 my $filename = $tr->filename || return;
70 my $wb = Spreadsheet::ParseExcel::Workbook->Parse( $filename );
71 my $schema = $tr->schema;
72 my $table_no = 0;
73
74 my %parsed;
75 my $wb_count = $wb->{'SheetCount'} || 0;
76 for my $num ( 0 .. $wb_count - 1 ) {
77 $table_no++;
78 my $ws = $wb->Worksheet( $num );
79 my $table_name = normalize_name( $ws->{'Name'} || "Table$table_no" );
ab0aa010 80
81 my @cols = $ws->ColRange;
82 next unless $cols[1] > 0;
83
8c7f5c7b 84 $parsed{ $table_name } = {
85 table_name => $table_name,
ab0aa010 86 type => undef,
87 indices => [ {} ],
88 fields => { },
89 };
8c7f5c7b 90 my $table = $schema->add_table( name => $table_name );
ab0aa010 91
8c7f5c7b 92 for my $col ( $cols[0] .. $cols[1] ) {
93 my $cell = $ws->Cell(0, $col);
94 my $col_name = normalize_name( $cell->{'Val'} );
95 my $data_type = ET_to_ST( $cell->{'Type'} );
96
97 $parsed{ $table_name }{'fields'}{ $col_name } = {
ab0aa010 98 type => 'field',
99 order => $col,
8c7f5c7b 100 name => $col_name,
101 data_type => $data_type,
ab0aa010 102 size => [ 255 ],
103 null => 1,
104 default => '',
105 is_auto_inc => undef,
106
8c7f5c7b 107 # first field is the primary key
ab0aa010 108 is_primary_key => ($col == 0) ? 1 : undef,
8c7f5c7b 109 };
110
111 my $field = $table->add_field(
112 name => $col_name,
113 data_type => $data_type,
114 default_value => '',
115 size => 255,
116 is_nullable => 1,
117 is_auto_increment => undef,
118 ) or die $table->error;
119
120 if ( $col == 0 ) {
121 $table->primary_key( $field->name );
122 $field->is_primary_key(1);
ab0aa010 123 }
19de1991 124 }
125 }
126
ab0aa010 127 return \%parsed;
128}
19de1991 129
ab0aa010 130sub ET_to_ST {
131 my $et = shift;
132 $ET_to_ST{$et} || $ET_to_ST{'Text'};
19de1991 133}
134
1351;
136
8c7f5c7b 137# -------------------------------------------------------------------
138# Education is an admirable thing,
139# but it is as well to remember that
140# nothing that is worth knowing can be taught.
141# Oscar Wilde
142# -------------------------------------------------------------------
143
19de1991 144=pod
145
7909b81e 146=head1 AUTHORS
19de1991 147
7909b81e 148Mike Mellilo <mmelillo@users.sourceforge.net>,
ab0aa010 149darren chamberlain E<lt>dlc@users.sourceforge.netE<gt>
7909b81e 150Ken Y. Clark E<lt>kclark@cpan.orgE<gt>
19de1991 151
152=head1 SEE ALSO
153
154perl(1), Spreadsheet::ParseExcel.
155
156=cut