Added rules to catch common (but useless) statements.
[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
19de1991 55# -------------------------------------------------------------------
ab0aa010 56# parse($tr, $data)
57#
58# Note that $data, in the case of this parser, is unuseful.
59# Spreadsheet::ParseExcel works on files, not data streams.
60# -------------------------------------------------------------------
19de1991 61sub parse {
ab0aa010 62 my ($tr, $data) = @_;
63 my $filename = $tr->filename || return;
64 my $wb = Spreadsheet::ParseExcel::Workbook->Parse($filename);
65 my (%parsed, $wb_count, $num);
66 my $table_no = 0;
67
5c129a69 68 $wb_count = $wb->{'SheetCount'} || 0;
ab0aa010 69 for $num (0 .. $wb_count - 1) {
70 my $ws = $wb->Worksheet($num);
71 my $name = $ws->{Name} || ++$table_no;
72
73 $name = normalize_name($name);
74
75 my @cols = $ws->ColRange;
76 next unless $cols[1] > 0;
77
78 $parsed{$name} = {
79 table_name => $name,
80 type => undef,
81 indices => [ {} ],
82 fields => { },
83 };
84
85 for my $col ($cols[0] .. $cols[1]) {
86 my $cell = $ws->Cell(0, $col);
87 $parsed{$name}->{'fields'}->{$cell->{Val}} = {
88 type => 'field',
89 order => $col,
90 name => $cell->{Val},
91
92 # Default datatype is 'char'
93 data_type => ET_to_ST($cell->{Type}),
94
95 # default size is 8bits; something more reasonable?
96 size => [ 255 ],
97 null => 1,
98 default => '',
99 is_auto_inc => undef,
100
101 # field field is the primary key
102 is_primary_key => ($col == 0) ? 1 : undef,
103 }
19de1991 104 }
105 }
106
ab0aa010 107 return \%parsed;
108}
19de1991 109
ab0aa010 110my %ET_to_ST = (
111 'Text' => 'VARCHAR',
112 'Date' => 'DATETIME',
113 'Numeric' => 'DOUBLE',
114);
115sub ET_to_ST {
116 my $et = shift;
117 $ET_to_ST{$et} || $ET_to_ST{'Text'};
19de1991 118}
119
1201;
121
19de1991 122=pod
123
7909b81e 124=head1 AUTHORS
19de1991 125
7909b81e 126Mike Mellilo <mmelillo@users.sourceforge.net>,
ab0aa010 127darren chamberlain E<lt>dlc@users.sourceforge.netE<gt>
7909b81e 128Ken Y. Clark E<lt>kclark@cpan.orgE<gt>
19de1991 129
130=head1 SEE ALSO
131
132perl(1), Spreadsheet::ParseExcel.
133
134=cut