1 package SL::Helper::Csv;
9 use Params::Validate qw(:all);
10 use List::MoreUtils qw(all pairwise firstidx);
12 use Rose::Object::MakeMethods::Generic scalar => [ qw(
13 file encoding sep_char quote_char escape_char header profile
14 numberformat dateformat ignore_unknown_columns strict_profile is_multiplexed
15 _row_header _io _csv _objects _parsed _data _errors all_cvar_configs case_insensitive_header
16 _multiplex_datatype_position
19 use SL::Helper::Csv::Dispatcher;
20 use SL::Helper::Csv::Error;
26 my %params = validate(@_, {
27 sep_char => { default => ';' },
28 quote_char => { default => '"' },
29 escape_char => { default => '"' },
30 header => { type => ARRAYREF, optional => 1 },
31 profile => { type => ARRAYREF, optional => 1 },
36 ignore_unknown_columns => 0,
38 case_insensitive_header => 0,
40 my $self = bless {}, $class;
42 $self->$_($params{$_}) for keys %params;
44 $self->_io(IO::File->new);
45 $self->_csv(Text::CSV_XS->new({
47 sep_char => $self->sep_char,
48 quote_char => $self->quote_char,
49 escape_char => $self->escape_char,
58 my ($self, %params) = @_;
61 return if ! $self->_check_multiplexed;
62 return if ! $self->_check_header;
63 return if ! $self->_check_multiplex_datatype_position;
64 return if ! $self->dispatcher->parse_profile;
65 return if ! $self->_parse_data;
76 my ($self, %params) = @_;
77 croak 'must parse first' unless $self->_parsed;
79 $self->_make_objects unless $self->_objects;
80 return $self->_objects;
94 my ($self, %params) = @_;
96 $self->encoding($self->_guess_encoding) if !$self->encoding;
98 $self->_io->open($self->file, '<' . $self->_encode_layer)
99 or die "could not open file " . $self->file;
104 # check, if data is multiplexed and if all nessesary infos are given
105 sub _check_multiplexed {
106 my ($self, %params) = @_;
108 $self->is_multiplexed(0);
110 # If more than one profile is given, it is multiplexed.
111 if ($self->profile) {
112 my @profile = @{ $self->profile };
113 if (scalar @profile > 1) {
114 # Each profile needs a class and a row_ident
115 my $info_ok = all { defined $_->{class} && defined $_->{row_ident} } @profile;
118 "missing class or row_ident in one of the profiles for multiplexed data",
122 # If header is given, there needs to be a header for each profile
123 # and no empty headers.
124 if ($info_ok && $self->header) {
125 my @header = @{ $self->header };
126 my $t_ok = scalar @profile == scalar @header;
129 "number of headers and number of profiles must be the same for multiplexed data",
132 $info_ok = $info_ok && $t_ok;
134 $t_ok = all { scalar @$_ > 0} @header;
137 "no empty headers are allowed for multiplexed data",
140 $info_ok = $info_ok && $t_ok;
142 $self->is_multiplexed($info_ok);
147 # ok, if not multiplexed
152 my ($self, %params) = @_;
155 $header = $self->header;
157 my $n_header = ($self->is_multiplexed)? scalar @{ $self->profile } : 1;
158 foreach my $p_num (0..$n_header - 1) {
159 my $h = $self->_csv->getline($self->_io);
162 $self->_csv->error_input,
163 $self->_csv->error_diag,
167 if ($self->is_multiplexed) {
168 push @{ $header }, $h;
175 # Special case: utf8 BOM.
176 # certain software (namely MS Office and notepad.exe insist on prefixing
177 # data with a discouraged but valid byte order mark
178 # if not removed, the first header field will not be recognized
180 my $h = ($self->is_multiplexed)? $header->[0] : $header;
182 if ($h && $h->[0] && $self->encoding =~ /utf-?8/i) {
183 $h->[0] =~ s/^\x{FEFF}//;
187 # check, if all header fields are parsed well
188 if ($self->is_multiplexed) {
189 return unless $header && all { $_ } @$header;
191 return unless $header;
194 # Special case: human stupidity
195 # people insist that case sensitivity doesn't exist and try to enter all
196 # sorts of stuff. at this point we've got a profile (with keys that represent
197 # valid methods), and a header full of strings. if two of them match, the user
198 # most likely meant that field, so rewrite the header
199 if ($self->case_insensitive_header) {
200 die 'case_insensitive_header is only possible with profile' unless $self->profile;
202 my $h_aref = ($self->is_multiplexed)? $header : [ $header ];
204 foreach my $h (@{ $h_aref }) {
206 keys %{ $self->profile->[$p_num]->{profile} || {} },
208 for my $name (@names) {
209 for my $i (0..$#$h) {
210 $h->[$i] = $name if lc $h->[$i] eq lc $name;
218 return $self->header($header);
221 sub _check_multiplex_datatype_position {
224 return 1 if !$self->is_multiplexed; # ok if not multiplexed
226 my @positions = map { firstidx { 'datatype' eq lc($_) } @{ $_ } } @{ $self->header };
227 my $first_pos = $positions[0];
228 if (all { $first_pos == $_ } @positions) {
229 $self->_multiplex_datatype_position($first_pos);
232 $self->_push_error([0,
233 "datatype field must be at the same position for all datatypes for multiplexed data",
241 my ($self, %params) = @_;
245 my $row = $self->_csv->getline($self->_io);
247 my $header = $self->_header_by_row($row);
249 @hr{@{ $header }} = @$row;
252 last if $self->_csv->eof;
253 # Text::CSV_XS 0.89 added record number to error_diag
254 if (qv(Text::CSV_XS->VERSION) >= qv('0.89')) {
256 $self->_csv->error_input,
257 $self->_csv->error_diag,
261 $self->_csv->error_input,
262 $self->_csv->error_diag,
263 $self->_io->input_line_number,
267 last if $self->_csv->eof;
270 $self->_data(\@data);
271 $self->_push_error(@errors);
277 my ($self, $row) = @_;
279 # initialize lookup hash if not already done
280 if ($self->is_multiplexed && ! defined $self->_row_header ) {
281 $self->_row_header({ pairwise { no warnings 'once'; $a->{row_ident} => $b } @{ $self->profile }, @{ $self->header } });
284 if ($self->is_multiplexed) {
285 return $self->_row_header->{$row->[$self->_multiplex_datatype_position]}
287 return $self->header;
292 ':encoding(' . $_[0]->encoding . ')';
296 my ($self, %params) = @_;
299 local $::myconfig{numberformat} = $self->numberformat if $self->numberformat;
300 local $::myconfig{dateformat} = $self->dateformat if $self->dateformat;
302 for my $line (@{ $self->_data }) {
303 my $tmp_obj = $self->dispatcher->dispatch($line);
304 push @objs, $tmp_obj;
307 $self->_objects(\@objs);
311 my ($self, %params) = @_;
313 $self->{_dispatcher} ||= $self->_make_dispatcher;
316 sub _make_dispatcher {
317 my ($self, %params) = @_;
319 die 'need a header to make a dispatcher' unless $self->header;
321 return SL::Helper::Csv::Dispatcher->new($self);
324 sub _guess_encoding {
330 my ($self, @errors) = @_;
331 my @new_errors = ($self->errors, map { SL::Helper::Csv::Error->new(@$_) } @errors);
332 $self->_errors(\@new_errors);
344 SL::Helper::Csv - take care of csv file uploads
350 my $csv = SL::Helper::Csv->new(
351 file => \$::form->{upload_file},
352 encoding => 'utf-8', # undef means utf8
353 sep_char => ',', # default ';'
354 quote_char => '\'', # default '"'
355 escape_char => '"', # default '"'
356 header => [ qw(id text sellprice word) ], # see later
357 profile => [ { profile => { sellprice => 'sellprice_as_number'},
358 class => 'SL::DB::Part' } ],
361 my $status = $csv->parse;
362 my $hrefs = $csv->get_data;
363 my $objects = $csv->get_objects;
365 my @errors = $csv->errors;
371 Text::CSV already offers good functions to get lines out of a csv file, but in
372 most cases you will want those lines to be parsed into hashes or even objects,
373 so this model just skips ahead and gives you objects.
375 Its basic assumptions are:
379 =item You do know what you expect to be in that csv file.
381 This means first and foremost that you have knowledge about encoding, number and
382 date format, csv parameters such as quoting and separation characters. You also
383 know what content will be in that csv and what L<Rose::DB> is responsible for
384 it. You provide valid header columns and their mapping to the objects.
386 =item You do NOT know if the csv provider yields to your expectations.
388 Stuff that does not work with what you expect should not crash anything, but
389 give you a hint what went wrong. As a result, if you remember to check for
390 errors after each step, you should be fine.
392 =item Data does not make sense. It's just data.
394 Almost all data imports have some type of constraints. Some data needs to be
395 unique, other data needs to be connected to existing data sets. This will not
396 happen here. You will receive a plain mapping of the data into the class tree,
401 This module can handle multiplexed data of different class types. In that case
402 multiple profiles with classes and row identifiers must be given. Multiple
403 headers may also be given or read from csv data. Data must contain the row
404 identifier in the column named 'datatype'.
414 Standard constructor. You can use this to set most of the data.
418 Do the actual work. Will return true ($self actually) if success, undef if not.
422 Parse the data into objects and return those.
424 This method will return an arrayref of all objects.
428 Returns an arrayref of the raw lines as hashrefs.
432 Return all errors that came up during parsing. See error handling for detailed
443 The file which contents are to be read. Can be a name of a physical file or a
444 scalar ref for memory data.
448 Encoding of the CSV file. Note that this module does not do any encoding
449 guessing. Know what your data is. Defaults to utf-8.
457 Same as in L<Text::CSV>
459 =item C<header> \@HEADERS
461 If given, it contains an ARRAY of the header fields for not multiplexed data.
462 Or an ARRAYREF for each different class type for multiplexed data. These
463 ARRAYREFS are the header fields which are an array of columns. In this case
464 the first lines are not used as a header. Empty header fields will be ignored
467 If not given, headers are taken from the first n lines of data, where n is the
468 number of different class types.
470 In case of multiplexed data there must be a column named 'datatype'. This
471 column must be given in each header and must be at the same position in each
476 classic data of one type:
477 [ 'name', 'street', 'zipcode', 'city' ]
479 multiplexed data with two different types:
480 [ [ 'datatype', 'ordernumber', 'customer', 'transdate' ],
481 [ 'datatype', 'partnumber', 'qty', 'sellprice' ] ]
483 =item C<profile> [{profile => \%ACCESSORS, class => class, row_ident => ri},]
485 This is an ARRAYREF to HASHREFs which may contain the keys C<profile>, C<class>
488 The C<profile> is a HASHREF which may be used to map header fields to custom
491 [ {profile => { listprice => listprice_as_number }} ]
493 In this case C<listprice_as_number> will be used to read in values from the
496 In case of a One-To-One relationship these can also be set over
497 relationships by separating the steps with a dot (C<.>). This will work:
499 [ {profile => { customer => 'customer.name' }} ]
501 And will result in something like this:
503 $obj->customer($obj->meta->relationship('customer')->class->new);
504 $obj->customer->name($csv_line->{customer})
506 But beware, this will not try to look up anything in the database. You will
507 simply receive objects that represent what the profile defined. If some of
508 these information are unique, and should be connected to preexisting data, you
509 will have to do that for yourself. Since you provided the profile, it is
510 assumed you know what to do in this case.
512 If no profile is given, any header field found will be taken as is.
514 If the path in a profile entry is empty, the field will be subjected to
515 C<strict_profile> and C<case_insensitive_header> checking, will be parsed into
516 C<get_data>, but will not be attempted to be dispatched into objects.
518 If C<class> is present, the line will be handed to the new sub of this class,
519 and the return value used instead of the line itself.
521 C<row_ident> is a string to recognize the right profile and class for each data
522 line in multiplexed data. It must match the value in the column 'dataype' for
525 In case of multiplexed data, C<class> and C<row_ident> must be given.
528 class => 'SL::DB::Order',
532 class => 'SL::DB::OrderItem',
534 profile => {sellprice => sellprice_as_number}
537 =item C<ignore_unknown_columns>
539 If set, the import will ignore unkown header columns. Useful for lazy imports,
540 but deactivated by default.
542 =item C<case_insensitive_header>
544 If set, header columns will be matched against profile entries case
545 insensitive, and on match the profile name will be taken.
547 Only works if a profile is given, will die otherwise.
549 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
550 columns will be accepted.
552 =item C<strict_profile>
554 If set, all columns to be parsed must be specified in C<profile>. Every header
555 field not listed there will be treated like an unknown column.
557 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
558 columns will be accepted.
562 =head1 ERROR HANDLING
564 After parsing a file all errors will be accumulated into C<errors>.
565 Each entry is an object with the following attributes:
567 raw_input: offending raw input,
568 code: Text::CSV error code if Text:CSV signalled an error, 0 else,
569 diag: error diagnostics,
570 line: position in line,
571 col: estimated line in file,
573 Note that the last entry can be off, but will give an estimate.
581 sep_char, quote_char, and escape_char are passed to Text::CSV on creation.
582 Changing them later has no effect currently.
586 Encoding errors are not dealt with properly.
592 Dispatch to child objects, like this:
594 $csv = SL::Helper::Csv->new(
607 class => SL::DB::Part,
613 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>