Allow embedded comments a la the PG parser, store the comments; also strip
[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;
ecc063ba 46$VERSION = sprintf "%d.%02d", q$Revision: 1.7 $ =~ /(\d+)\.(\d+)/;
19de1991 47
7909b81e 48use Spreadsheet::ParseExcel;
19de1991 49use Exporter;
ab0aa010 50use SQL::Translator::Utils qw(debug normalize_name);
51
19de1991 52use base qw(Exporter);
53
54@EXPORT_OK = qw(parse);
55
8c7f5c7b 56my %ET_to_ST = (
57 'Text' => 'VARCHAR',
58 'Date' => 'DATETIME',
59 'Numeric' => 'DOUBLE',
60);
61
19de1991 62# -------------------------------------------------------------------
ab0aa010 63# parse($tr, $data)
64#
65# Note that $data, in the case of this parser, is unuseful.
66# Spreadsheet::ParseExcel works on files, not data streams.
67# -------------------------------------------------------------------
19de1991 68sub parse {
ab0aa010 69 my ($tr, $data) = @_;
8c7f5c7b 70 my $filename = $tr->filename || return;
71 my $wb = Spreadsheet::ParseExcel::Workbook->Parse( $filename );
72 my $schema = $tr->schema;
73 my $table_no = 0;
74
8c7f5c7b 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 my $table = $schema->add_table( name => $table_name );
ab0aa010 85
8c7f5c7b 86 for my $col ( $cols[0] .. $cols[1] ) {
87 my $cell = $ws->Cell(0, $col);
88 my $col_name = normalize_name( $cell->{'Val'} );
89 my $data_type = ET_to_ST( $cell->{'Type'} );
90
8c7f5c7b 91 my $field = $table->add_field(
92 name => $col_name,
93 data_type => $data_type,
94 default_value => '',
95 size => 255,
96 is_nullable => 1,
97 is_auto_increment => undef,
98 ) or die $table->error;
99
100 if ( $col == 0 ) {
101 $table->primary_key( $field->name );
102 $field->is_primary_key(1);
ab0aa010 103 }
19de1991 104 }
105 }
106
f62bd16c 107 return 1;
ab0aa010 108}
19de1991 109
ab0aa010 110sub ET_to_ST {
111 my $et = shift;
112 $ET_to_ST{$et} || $ET_to_ST{'Text'};
19de1991 113}
114
1151;
116
8c7f5c7b 117# -------------------------------------------------------------------
118# Education is an admirable thing,
119# but it is as well to remember that
120# nothing that is worth knowing can be taught.
121# Oscar Wilde
122# -------------------------------------------------------------------
123
19de1991 124=pod
125
7909b81e 126=head1 AUTHORS
19de1991 127
7909b81e 128Mike Mellilo <mmelillo@users.sourceforge.net>,
ab0aa010 129darren chamberlain E<lt>dlc@users.sourceforge.netE<gt>
7909b81e 130Ken Y. Clark E<lt>kclark@cpan.orgE<gt>
19de1991 131
132=head1 SEE ALSO
133
134perl(1), Spreadsheet::ParseExcel.
135
136=cut