Commit | Line | Data |
7909b81e |
1 | package 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 | |
26 | SQL::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 |
38 | Parses an Excel spreadsheet file for SQL::Translator. You can then |
39 | turn the data into a database tables or graphs. |
19de1991 |
40 | |
41 | =cut |
42 | |
43 | use strict; |
44 | use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; |
45 | $DEBUG = 0 unless defined $DEBUG; |
46 | |
47 | use Data::Dumper; |
7909b81e |
48 | use Spreadsheet::ParseExcel; |
19de1991 |
49 | use Exporter; |
50 | use base qw(Exporter); |
51 | |
52 | @EXPORT_OK = qw(parse); |
53 | |
19de1991 |
54 | # ------------------------------------------------------------------- |
55 | sub parse { |
56 | my ( $translator, $data ) = @_; |
7909b81e |
57 | my $parsed = { |
58 | table1 => { |
59 | "type" => undef, |
19de1991 |
60 | "indices" => [ { } ], |
7909b81e |
61 | "fields" => { }, |
19de1991 |
62 | }, |
63 | }; |
64 | |
7909b81e |
65 | my $tr = Spreadsheet::ParseExcel->new; |
66 | $tr->Parse( $data ); |
67 | my $r = 1; # For now we will assume all column names are in the first row |
68 | my $c = 0; |
69 | |
70 | # |
71 | # Mikey, what's going on here? |
72 | # |
73 | my @parsed = map { return $tr->{'Cells'}[$r][$c] } ( |
74 | $c = $tr->{'MinCol'}; |
75 | $c <= $tr->{'MaxCol'}; # Is "<=" right? |
76 | $c++; |
77 | ); |
78 | |
79 | for ( my $i = 0; $i < @parsed; $i++ ) { |
80 | $parsed->{'table1'}->{'fields'}->{$parsed[$i]} = { |
81 | type => 'field', |
19de1991 |
82 | order => $i, |
83 | name => $parsed[$i], |
84 | |
7909b81e |
85 | # Default datatype is 'char' |
86 | data_type => 'char', |
19de1991 |
87 | |
88 | # default size is 8bits; something more reasonable? |
89 | size => 255, |
90 | null => 1, |
7909b81e |
91 | default => '', |
19de1991 |
92 | is_auto_inc => undef, |
93 | |
94 | # field field is the primary key |
95 | is_primary_key => ($i == 0) ? 1 : undef, |
96 | } |
97 | } |
98 | |
99 | |
100 | # Field 0 is primary key, by default, so add an index |
7909b81e |
101 | for ($parsed->{'table1'}->{'indices'}->[0]) { |
102 | $_->{'type'} = 'primary_key'; |
103 | $_->{'name'} = undef; |
104 | $_->{'fields'} = [ $parsed[0] ]; |
19de1991 |
105 | } |
106 | |
19de1991 |
107 | return $parsed; |
19de1991 |
108 | } |
109 | |
110 | 1; |
111 | |
19de1991 |
112 | =pod |
113 | |
7909b81e |
114 | =head1 AUTHORS |
19de1991 |
115 | |
7909b81e |
116 | Mike Mellilo <mmelillo@users.sourceforge.net>, |
117 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt> |
19de1991 |
118 | |
119 | =head1 SEE ALSO |
120 | |
121 | perl(1), Spreadsheet::ParseExcel. |
122 | |
123 | =cut |