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 (map { $_ => $_ } keys %{ $self->profile->[$p_num]->{profile} || {} }),
207 (map { $_ => $self->profile->[$p_num]{mapping}{$_} } keys %{ $self->profile->[$p_num]->{mapping} || {} }),
209 for my $name (keys %names) {
210 for my $i (0..$#$h) {
211 $h->[$i] = $names{$name} if lc $h->[$i] eq lc $name;
219 return $self->header($header);
222 sub _check_multiplex_datatype_position {
225 return 1 if !$self->is_multiplexed; # ok if not multiplexed
227 my @positions = map { firstidx { 'datatype' eq lc($_) } @{ $_ } } @{ $self->header };
228 my $first_pos = $positions[0];
229 if (all { $first_pos == $_ } @positions) {
230 $self->_multiplex_datatype_position($first_pos);
233 $self->_push_error([0,
234 "datatype field must be at the same position for all datatypes for multiplexed data",
242 my ($self, %params) = @_;
246 my $row = $self->_csv->getline($self->_io);
248 my $header = $self->_header_by_row($row);
252 "Cannot get header for row. Maybe row name and datatype field not matching.",
258 @hr{@{ $header }} = @$row;
261 last if $self->_csv->eof;
262 # Text::CSV_XS 0.89 added record number to error_diag
263 if (qv(Text::CSV_XS->VERSION) >= qv('0.89')) {
265 $self->_csv->error_input,
266 $self->_csv->error_diag,
270 $self->_csv->error_input,
271 $self->_csv->error_diag,
272 $self->_io->input_line_number,
276 last if $self->_csv->eof;
279 $self->_data(\@data);
280 $self->_push_error(@errors);
286 my ($self, $row) = @_;
288 # initialize lookup hash if not already done
289 if ($self->is_multiplexed && ! defined $self->_row_header ) {
290 $self->_row_header({ pairwise { no warnings 'once'; $a->{row_ident} => $b } @{ $self->profile }, @{ $self->header } });
293 if ($self->is_multiplexed) {
294 return $self->_row_header->{$row->[$self->_multiplex_datatype_position]}
296 return $self->header;
301 ':encoding(' . $_[0]->encoding . ')';
305 my ($self, %params) = @_;
308 local $::myconfig{numberformat} = $self->numberformat if $self->numberformat;
309 local $::myconfig{dateformat} = $self->dateformat if $self->dateformat;
311 for my $line (@{ $self->_data }) {
312 my $tmp_obj = $self->dispatcher->dispatch($line);
313 push @objs, $tmp_obj;
316 $self->_objects(\@objs);
320 my ($self, %params) = @_;
322 $self->{_dispatcher} ||= $self->_make_dispatcher;
325 sub _make_dispatcher {
326 my ($self, %params) = @_;
328 die 'need a header to make a dispatcher' unless $self->header;
330 return SL::Helper::Csv::Dispatcher->new($self);
333 sub _guess_encoding {
339 my ($self, @errors) = @_;
340 my @new_errors = ($self->errors, map { SL::Helper::Csv::Error->new(@$_) } @errors);
341 $self->_errors(\@new_errors);
345 $_[0]->dispatcher->_specs
356 SL::Helper::Csv - take care of csv file uploads
362 my $csv = SL::Helper::Csv->new(
363 file => \$::form->{upload_file},
364 encoding => 'utf-8', # undef means utf8
365 sep_char => ',', # default ';'
366 quote_char => '\'', # default '"'
367 escape_char => '"', # default '"'
368 header => [ qw(id text sellprice word) ], # see later
369 profile => [ { profile => { sellprice => 'sellprice_as_number'},
370 class => 'SL::DB::Part' } ],
373 my $status = $csv->parse;
374 my $hrefs = $csv->get_data;
375 my $objects = $csv->get_objects;
377 my @errors = $csv->errors;
383 Text::CSV already offers good functions to get lines out of a csv file, but in
384 most cases you will want those lines to be parsed into hashes or even objects,
385 so this model just skips ahead and gives you objects.
387 Its basic assumptions are:
391 =item You do know what you expect to be in that csv file.
393 This means first and foremost that you have knowledge about encoding, number and
394 date format, csv parameters such as quoting and separation characters. You also
395 know what content will be in that csv and what L<Rose::DB> is responsible for
396 it. You provide valid header columns and their mapping to the objects.
398 =item You do NOT know if the csv provider yields to your expectations.
400 Stuff that does not work with what you expect should not crash anything, but
401 give you a hint what went wrong. As a result, if you remember to check for
402 errors after each step, you should be fine.
404 =item Data does not make sense. It's just data.
406 Almost all data imports have some type of constraints. Some data needs to be
407 unique, other data needs to be connected to existing data sets. This will not
408 happen here. You will receive a plain mapping of the data into the class tree,
413 This module can handle multiplexed data of different class types. In that case
414 multiple profiles with classes and row identifiers must be given. Multiple
415 headers may also be given or read from csv data. Data must contain the row
416 identifier in the column named 'datatype'.
426 Standard constructor. You can use this to set most of the data.
430 Do the actual work. Will return true ($self actually) if success, undef if not.
434 Parse the data into objects and return those.
436 This method will return an arrayref of all objects.
440 Returns an arrayref of the raw lines as hashrefs.
444 Return all errors that came up during parsing. See error handling for detailed
455 The file which contents are to be read. Can be a name of a physical file or a
456 scalar ref for memory data.
460 Encoding of the CSV file. Note that this module does not do any encoding
461 guessing. Know what your data is. Defaults to utf-8.
469 Same as in L<Text::CSV>
471 =item C<header> \@HEADERS
473 If given, it contains an ARRAY of the header fields for not multiplexed data.
474 Or an ARRAYREF for each different class type for multiplexed data. These
475 ARRAYREFS are the header fields which are an array of columns. In this case
476 the first lines are not used as a header. Empty header fields will be ignored
479 If not given, headers are taken from the first n lines of data, where n is the
480 number of different class types.
482 In case of multiplexed data there must be a column named 'datatype'. This
483 column must be given in each header and must be at the same position in each
488 classic data of one type:
489 [ 'name', 'street', 'zipcode', 'city' ]
491 multiplexed data with two different types:
492 [ [ 'datatype', 'ordernumber', 'customer', 'transdate' ],
493 [ 'datatype', 'partnumber', 'qty', 'sellprice' ] ]
495 =item C<profile> PROFILE_DATA
497 The profile mapping csv to the objects.
499 See section L</PROFILE> for information on this topic.
501 =item C<ignore_unknown_columns>
503 If set, the import will ignore unkown header columns. Useful for lazy imports,
504 but deactivated by default.
506 =item C<case_insensitive_header>
508 If set, header columns will be matched against profile entries case
509 insensitive, and on match the profile name will be taken.
511 Only works if a profile is given, will die otherwise.
513 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
514 columns will be accepted.
516 =item C<strict_profile>
518 If set, all columns to be parsed must be specified in C<profile>. Every header
519 field not listed there will be treated like an unknown column.
521 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
522 columns will be accepted.
528 The profile is needed for mapping csv data to the accessors in the data object.
530 The basic structure is:
532 PROFILE := [ CLASS_PROFILE, CLASS_PROFILE* ]
534 profile => { ACCESSORS+ },
536 row_ident => $row_ident,
537 mapping => { MAPPINGS* },
539 ACCESSORS := $field => $accessor
540 MAPPINGS := $alias => $field
542 The C<ACCESSORS> may be used to map header fields to custom
546 listprice => 'listprice_as_number',
549 In this case C<listprice_as_number> will be used to store the values from the
552 In case of a One-To-One relationship these can also be set over
553 relationships by separating the steps with a dot (C<.>). This will work:
555 customer => 'customer.name',
557 And will result in something like this:
559 $obj->customer($obj->meta->relationship('customer')->class->new);
560 $obj->customer->name($csv_line->{customer})
562 Beware, this will not try to look up anything in the database! You will
563 simply receive objects that represent what the profile defined. If some of
564 these information are unique, or should be connected to preexisting data, you
565 will have to do that for yourself. Since you provided the profile, it is
566 assumed you know what to do in this case.
568 If no profile is given, any header field found will be taken as is.
570 If the path in a profile entry is empty, the field will be subjected to
571 C<strict_profile> and C<case_insensitive_header> checking and will be parsed
572 into C<get_data>, but will not be attempted to be dispatched into objects.
574 C<class> must be present. A new instance will be created for each line before
577 C<row_ident> is used to determine the correct profile in multiplexed data and
578 must be given there. It's not used in non-multiplexed data.
580 If C<mappings> is present, it must contain a hashref that maps strings to known
581 fields. This can be used to add custom profiles for known sources, that don't
582 comply with the expected header identities.
584 Without strict profiles, mappings can also directly map header fields that
585 should end up in the same accessor.
587 With case insensitive headings, mappings will also modify the headers, to fit
588 the expected profile.
590 Mappings can be identical to known fields and will be prefered during lookup,
591 but will not replace the field, meaning that:
595 description => 'description',
598 name => 'description',
602 will work as expected, and shortname will not end up in description. This also
603 works with the case insensitive option. Note however that the case insensitive
604 option will not enable true unicode collating.
607 Here's a full example:
611 class => 'SL::DB::Order',
615 class => 'SL::DB::OrderItem',
617 profile => { sellprice => 'sellprice_as_number' },
618 mapping => { 'Verkaufspreis' => 'sellprice' }
622 =head1 ERROR HANDLING
624 After parsing a file all errors will be accumulated into C<errors>.
625 Each entry is an object with the following attributes:
627 raw_input: offending raw input,
628 code: Text::CSV error code if Text:CSV signalled an error, 0 else,
629 diag: error diagnostics,
630 line: position in line,
631 col: estimated line in file,
633 Note that the last entry can be off, but will give an estimate.
635 Error handling is also known to break on new Perl versions and need to be
636 adjusted from time to time due to changes in Text::CSV_XS.
644 sep_char, quote_char, and escape_char are passed to Text::CSV on creation.
645 Changing them later has no effect currently.
649 Encoding errors are not dealt with properly.
655 Dispatch to child objects, like this:
657 $csv = SL::Helper::Csv->new(
670 class => SL::DB::Part,
676 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>