Commit | Line | Data |
7909b81e |
1 | package SQL::Translator::Parser::Excel; |
19de1991 |
2 | |
19de1991 |
3 | =head1 NAME |
4 | |
5 | SQL::Translator::Parser::Excel - parser for Excel |
6 | |
7 | =head1 SYNOPSIS |
8 | |
9 | use SQL::Translator; |
19de1991 |
10 | |
11 | my $translator = SQL::Translator->new; |
854b7210 |
12 | $translator->parser('Excel'); |
19de1991 |
13 | |
14 | =head1 DESCRIPTION |
15 | |
854b7210 |
16 | Parses an Excel spreadsheet file using Spreadsheet::ParseExcel. |
19de1991 |
17 | |
5eb4a350 |
18 | =head1 OPTIONS |
19 | |
20 | =over |
21 | |
22 | =item * scan_fields |
23 | |
24 | Indicates that the columns should be scanned to determine data types |
25 | and field sizes. True by default. |
26 | |
27 | =back |
28 | |
19de1991 |
29 | =cut |
30 | |
31 | use strict; |
f27f9229 |
32 | use warnings; |
0c04c5a2 |
33 | our ($DEBUG, @EXPORT_OK); |
ab0aa010 |
34 | $DEBUG = 0 unless defined $DEBUG; |
e0583e2c |
35 | our $VERSION = '1.59_01'; |
19de1991 |
36 | |
7909b81e |
37 | use Spreadsheet::ParseExcel; |
19de1991 |
38 | use Exporter; |
ab0aa010 |
39 | use SQL::Translator::Utils qw(debug normalize_name); |
40 | |
19de1991 |
41 | use base qw(Exporter); |
42 | |
43 | @EXPORT_OK = qw(parse); |
44 | |
8c7f5c7b |
45 | my %ET_to_ST = ( |
46 | 'Text' => 'VARCHAR', |
47 | 'Date' => 'DATETIME', |
48 | 'Numeric' => 'DOUBLE', |
49 | ); |
50 | |
19de1991 |
51 | # ------------------------------------------------------------------- |
ab0aa010 |
52 | # parse($tr, $data) |
53 | # |
54 | # Note that $data, in the case of this parser, is unuseful. |
55 | # Spreadsheet::ParseExcel works on files, not data streams. |
56 | # ------------------------------------------------------------------- |
19de1991 |
57 | sub parse { |
ab0aa010 |
58 | my ($tr, $data) = @_; |
5eb4a350 |
59 | my $args = $tr->parser_args; |
8c7f5c7b |
60 | my $filename = $tr->filename || return; |
61 | my $wb = Spreadsheet::ParseExcel::Workbook->Parse( $filename ); |
62 | my $schema = $tr->schema; |
63 | my $table_no = 0; |
64 | |
8c7f5c7b |
65 | my $wb_count = $wb->{'SheetCount'} || 0; |
66 | for my $num ( 0 .. $wb_count - 1 ) { |
67 | $table_no++; |
68 | my $ws = $wb->Worksheet( $num ); |
69 | my $table_name = normalize_name( $ws->{'Name'} || "Table$table_no" ); |
ab0aa010 |
70 | |
71 | my @cols = $ws->ColRange; |
72 | next unless $cols[1] > 0; |
73 | |
8c7f5c7b |
74 | my $table = $schema->add_table( name => $table_name ); |
ab0aa010 |
75 | |
5eb4a350 |
76 | my @field_names = (); |
8c7f5c7b |
77 | for my $col ( $cols[0] .. $cols[1] ) { |
78 | my $cell = $ws->Cell(0, $col); |
79 | my $col_name = normalize_name( $cell->{'Val'} ); |
80 | my $data_type = ET_to_ST( $cell->{'Type'} ); |
5eb4a350 |
81 | push @field_names, $col_name; |
8c7f5c7b |
82 | |
8c7f5c7b |
83 | my $field = $table->add_field( |
84 | name => $col_name, |
85 | data_type => $data_type, |
86 | default_value => '', |
87 | size => 255, |
88 | is_nullable => 1, |
89 | is_auto_increment => undef, |
90 | ) or die $table->error; |
91 | |
92 | if ( $col == 0 ) { |
93 | $table->primary_key( $field->name ); |
94 | $field->is_primary_key(1); |
ab0aa010 |
95 | } |
19de1991 |
96 | } |
5eb4a350 |
97 | |
98 | # |
99 | # If directed, look at every field's values to guess size and type. |
100 | # |
ea93df61 |
101 | unless ( |
5eb4a350 |
102 | defined $args->{'scan_fields'} && |
103 | $args->{'scan_fields'} == 0 |
104 | ) { |
105 | my %field_info = map { $_, {} } @field_names; |
106 | |
107 | for( |
108 | my $iR = $ws->{'MinRow'} == 0 ? 1 : $ws->{'MinRow'}; |
ea93df61 |
109 | defined $ws->{'MaxRow'} && $iR <= $ws->{'MaxRow'}; |
5eb4a350 |
110 | $iR++ |
111 | ) { |
ea93df61 |
112 | for ( |
5eb4a350 |
113 | my $iC = $ws->{'MinCol'}; |
ea93df61 |
114 | defined $ws->{'MaxCol'} && $iC <= $ws->{'MaxCol'}; |
5eb4a350 |
115 | $iC++ |
116 | ) { |
117 | my $field = $field_names[ $iC ]; |
118 | my $data = $ws->{'Cells'}[ $iR ][ $iC ]->{'_Value'}; |
5b9183fb |
119 | next if !defined $data || $data eq ''; |
5eb4a350 |
120 | my $size = [ length $data ]; |
121 | my $type; |
122 | |
123 | if ( $data =~ /^-?\d+$/ ) { |
124 | $type = 'integer'; |
125 | } |
ea93df61 |
126 | elsif ( |
127 | $data =~ /^-?[,\d]+\.[\d+]?$/ |
5eb4a350 |
128 | || |
ea93df61 |
129 | $data =~ /^-?[,\d]+?\.\d+$/ |
5eb4a350 |
130 | || |
ea93df61 |
131 | $data =~ /^-?\.\d+$/ |
5eb4a350 |
132 | ) { |
133 | $type = 'float'; |
ea93df61 |
134 | my ( $w, $d ) = |
135 | map { s/,//g; length $_ || 1 } |
7461b4ef |
136 | split( /\./, $data ) |
137 | ; |
138 | $size = [ $w + $d, $d ]; |
5eb4a350 |
139 | } |
140 | else { |
141 | $type = 'char'; |
142 | } |
143 | |
144 | for my $i ( 0, 1 ) { |
145 | next unless defined $size->[ $i ]; |
146 | my $fsize = $field_info{ $field }{'size'}[ $i ] || 0; |
147 | if ( $size->[ $i ] > $fsize ) { |
148 | $field_info{ $field }{'size'}[ $i ] = $size->[ $i ]; |
149 | } |
150 | } |
151 | |
152 | $field_info{ $field }{ $type }++; |
153 | } |
154 | } |
155 | |
156 | for my $field ( keys %field_info ) { |
9979410c |
157 | my $size = $field_info{ $field }{'size'} || [ 1 ]; |
ea93df61 |
158 | my $data_type = |
159 | $field_info{ $field }{'char'} ? 'char' : |
5b9183fb |
160 | $field_info{ $field }{'float'} ? 'float' : |
161 | $field_info{ $field }{'integer'} ? 'integer' : 'char'; |
162 | |
163 | if ( $data_type eq 'char' && scalar @$size == 2 ) { |
164 | $size = [ $size->[0] + $size->[1] ]; |
165 | } |
5eb4a350 |
166 | |
167 | my $field = $table->get_field( $field ); |
5b9183fb |
168 | $field->size( $size ) if $size; |
5eb4a350 |
169 | $field->data_type( $data_type ); |
170 | } |
171 | } |
19de1991 |
172 | } |
173 | |
f62bd16c |
174 | return 1; |
ab0aa010 |
175 | } |
19de1991 |
176 | |
ab0aa010 |
177 | sub ET_to_ST { |
178 | my $et = shift; |
179 | $ET_to_ST{$et} || $ET_to_ST{'Text'}; |
19de1991 |
180 | } |
181 | |
182 | 1; |
183 | |
8c7f5c7b |
184 | # ------------------------------------------------------------------- |
185 | # Education is an admirable thing, |
186 | # but it is as well to remember that |
187 | # nothing that is worth knowing can be taught. |
188 | # Oscar Wilde |
189 | # ------------------------------------------------------------------- |
190 | |
19de1991 |
191 | =pod |
192 | |
7909b81e |
193 | =head1 AUTHORS |
19de1991 |
194 | |
7909b81e |
195 | Mike Mellilo <mmelillo@users.sourceforge.net>, |
5eb4a350 |
196 | darren chamberlain E<lt>dlc@users.sourceforge.netE<gt>, |
11ad2df9 |
197 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt>. |
19de1991 |
198 | |
199 | =head1 SEE ALSO |
200 | |
5eb4a350 |
201 | Spreadsheet::ParseExcel, SQL::Translator. |
19de1991 |
202 | |
203 | =cut |