1 package SL::Helper::Csv;
9 use Params::Validate qw(:all);
10 use List::MoreUtils qw(all pairwise);
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
18 use SL::Helper::Csv::Dispatcher;
19 use SL::Helper::Csv::Error;
25 my %params = validate(@_, {
26 sep_char => { default => ';' },
27 quote_char => { default => '"' },
28 escape_char => { default => '"' },
29 header => { type => ARRAYREF, optional => 1 },
30 profile => { type => ARRAYREF, optional => 1 },
35 ignore_unknown_columns => 0,
37 case_insensitive_header => 0,
39 my $self = bless {}, $class;
41 $self->$_($params{$_}) for keys %params;
43 $self->_io(IO::File->new);
44 $self->_csv(Text::CSV_XS->new({
46 sep_char => $self->sep_char,
47 quote_char => $self->quote_char,
48 escape_char => $self->escape_char,
57 my ($self, %params) = @_;
60 return if ! $self->_check_multiplexed;
61 return if ! $self->_check_header;
62 return if ! $self->dispatcher->parse_profile;
63 return if ! $self->_parse_data;
74 my ($self, %params) = @_;
75 croak 'must parse first' unless $self->_parsed;
77 $self->_make_objects unless $self->_objects;
78 return wantarray ? @{ $self->_objects } : $self->_objects;
92 my ($self, %params) = @_;
94 $self->encoding($self->_guess_encoding) if !$self->encoding;
96 $self->_io->open($self->file, '<' . $self->_encode_layer)
97 or die "could not open file " . $self->file;
102 # check, if data is multiplexed and if all nessesary infos are given
103 sub _check_multiplexed {
104 my ($self, %params) = @_;
106 $self->is_multiplexed(0);
108 # If more than one profile is given, it is multiplexed.
109 if ($self->profile) {
110 my @profile = @{ $self->profile };
111 if (scalar @profile > 1) {
112 # Each profile needs a class and a row_ident
113 my $info_ok = all { defined $_->{class} && defined $_->{row_ident} } @profile;
116 "missing class or row_ident in one of the profiles for multiplexed data",
120 # If header is given, there need to be a header for each profile
121 # and no empty headers.
122 if ($info_ok && $self->header) {
123 my @header = @{ $self->header };
124 my $t_ok = scalar @profile == scalar @header;
127 "number of headers and number of profiles must be the same for multiplexed data",
130 $info_ok = $info_ok && $t_ok;
132 $t_ok = all { scalar @$_ > 0} @header;
135 "no empty headers are allowed for multiplexed data",
138 $info_ok = $info_ok && $t_ok;
140 $self->is_multiplexed($info_ok);
145 # ok, if not multiplexed
150 my ($self, %params) = @_;
153 $header = $self->header;
155 my $n_header = ($self->is_multiplexed)? scalar @{ $self->profile } : 1;
156 foreach my $p_num (0..$n_header - 1) {
157 my $h = $self->_csv->getline($self->_io);
160 $self->_csv->error_input,
161 $self->_csv->error_diag,
165 if ($self->is_multiplexed) {
166 push @{ $header }, $h;
173 # Special case: utf8 BOM.
174 # certain software (namely MS Office and notepad.exe insist on prefixing
175 # data with a discouraged but valid byte order mark
176 # if not removed, the first header field will not be recognized
178 my $h = ($self->is_multiplexed)? $header->[0] : $header;
180 if ($h && $h->[0] && $self->encoding =~ /utf-?8/i) {
181 $h->[0] =~ s/^\x{FEFF}//;
185 # check, if all header fields are parsed well
186 if ($self->is_multiplexed) {
187 return unless $header && all { $_ } @$header;
189 return unless $header;
192 # Special case: human stupidity
193 # people insist that case sensitivity doesn't exist and try to enter all
194 # sorts of stuff. at this point we've got a profile (with keys that represent
195 # valid methods), and a header full of strings. if two of them match, the user
196 # mopst likely meant that field, so rewrite the header
197 if ($self->case_insensitive_header) {
198 die 'case_insensitive_header is only possible with profile' unless $self->profile;
200 my $h_aref = ($self->is_multiplexed)? $header : [ $header ];
202 foreach my $h (@{ $h_aref }) {
204 keys %{ $self->profile->[$p_num]->{profile} || {} },
206 for my $name (@names) {
207 for my $i (0..$#$h) {
208 $h->[$i] = $name if lc $h->[$i] eq lc $name;
216 return $self->header($header);
220 my ($self, %params) = @_;
224 my $row = $self->_csv->getline($self->_io);
226 my $header = $self->_header_by_row($row);
228 @hr{@{ $header }} = @$row;
231 last if $self->_csv->eof;
232 # Text::CSV_XS 0.89 added record number to error_diag
233 if (qv(Text::CSV_XS->VERSION) >= qv('0.89')) {
235 $self->_csv->error_input,
236 $self->_csv->error_diag,
240 $self->_csv->error_input,
241 $self->_csv->error_diag,
242 $self->_io->input_line_number,
246 last if $self->_csv->eof;
249 $self->_data(\@data);
250 $self->_push_error(@errors);
256 my ($self, $row) = @_;
258 # initialize lookup hash if not already done
259 if ($self->is_multiplexed && ! defined $self->_row_header ) {
260 $self->_row_header({ pairwise { no warnings 'once'; $a->{row_ident} => $b } @{ $self->profile }, @{ $self->header } });
263 if ($self->is_multiplexed) {
264 return $self->_row_header->{$row->[0]}
266 return $self->header;
271 ':encoding(' . $_[0]->encoding . ')';
275 my ($self, %params) = @_;
278 local $::myconfig{numberformat} = $self->numberformat if $self->numberformat;
279 local $::myconfig{dateformat} = $self->dateformat if $self->dateformat;
281 for my $line (@{ $self->_data }) {
282 my $tmp_obj = $self->dispatcher->dispatch($line);
283 push @objs, $tmp_obj;
286 $self->_objects(\@objs);
290 my ($self, %params) = @_;
292 $self->{_dispatcher} ||= $self->_make_dispatcher;
295 sub _make_dispatcher {
296 my ($self, %params) = @_;
298 die 'need a header to make a dispatcher' unless $self->header;
300 return SL::Helper::Csv::Dispatcher->new($self);
303 sub _guess_encoding {
309 my ($self, @errors) = @_;
310 my @new_errors = ($self->errors, map { SL::Helper::Csv::Error->new(@$_) } @errors);
311 $self->_errors(\@new_errors);
323 SL::Helper::Csv - take care of csv file uploads
329 my $csv = SL::Helper::Csv->new(
330 file => \$::form->{upload_file},
331 encoding => 'utf-8', # undef means utf8
332 sep_char => ',', # default ';'
333 quote_char => '\'', # default '"'
334 escape_char => '"', # default '"'
335 header => [ qw(id text sellprice word) ], # see later
336 profile => [ { profile => { sellprice => 'sellprice_as_number'},
337 class => 'SL::DB::Part' } ],
340 my $status = $csv->parse;
341 my $hrefs = $csv->get_data;
342 my @objects = $csv->get_objects;
344 my @errors = $csv->errors;
350 Text::CSV offeres already good functions to get lines out of a csv file, but in
351 most cases you will want those line to be parsed into hashes or even objects,
352 so this model just skips ahead and gives you objects.
354 Its basic assumptions are:
358 =item You do know what you expect to be in that csv file.
360 This means first and foremost you have knowledge about encoding, number and
361 date format, csv parameters such as quoting and separation characters. You also
362 know what content will be in that csv and what L<Rose::DB> is responsible for
363 it. You provide valid header columns and their mapping to the objects.
365 =item You do NOT know if the csv provider yields to your expectations.
367 Stuff that does not work with what you expect should not crash anything, but
368 give you a hint what went wrong. As a result, if you remember to check for
369 errors after each step, you should be fine.
371 =item Data does not make sense. It's just data.
373 Almost all data imports have some type of constraints. Some data needs to be
374 unique, other data needs to be connected to existing data sets. This will not
375 happen here. You will receive a plain mapping of the data into the class tree,
380 This module can handle multiplexed data of different class types. In that case
381 multiple profiles with classes and row identifiers must be given. Multiple
382 headers may also be given or read from csv data. Data must contain the row
383 identifier in the first column and it's field name must be 'datatype'.
393 Standard constructor. You can use this to set most of the data.
397 Do the actual work. Will return true ($self actually) if success, undef if not.
401 Parse the data into objects and return those.
403 This method will return list or arrayref depending on context.
407 Returns an arrayref of the raw lines as hashrefs.
411 Return all errors that came up during parsing. See error handling for detailed
422 The file which contents are to be read. Can be a name of a physical file or a
423 scalar ref for memory data.
427 Encoding of the CSV file. Note that this module does not do any encoding
428 guessing. Know what your data is. Defaults to utf-8.
436 Same as in L<Text::CSV>
438 =item C<header> \@HEADERS
440 If given, it contains an ARRAY of the header fields for not multiplexed data.
441 Or an ARRAYREF for each different class type for multiplexed data. These
442 ARRAYREFS are the header fields which are an array of columns. In this case
443 the first lines are not used as a header. Empty header fields will be ignored
446 If not given, headers are taken from the first n lines of data, where n is the
447 number of different class types.
449 In case of multiplexed data the first column must be named 'datatype'. This
450 name must be given in the header.
454 classic data of one type:
455 [ 'name', 'street', 'zipcode', 'city' ]
457 multiplexed data with two different types
458 [ [ 'datatype', 'ordernumber', 'customer', 'transdate' ],
459 [ 'datatype', 'partnumber', 'qty', 'sellprice' ] ]
461 =item C<profile> [{profile => \%ACCESSORS, class => class, row_ident => ri},]
463 This is an ARRAYREF to HASHREFs which may contain the keys C<profile>, C<class>
466 The C<profile> is a HASHREF which may be used to map header fields to custom
469 [ {profile => { listprice => listprice_as_number }} ]
471 In this case C<listprice_as_number> will be used to read in values from the
474 In case of a One-To-One relationsship these can also be set over
475 relationsships by sparating the steps with a dot (C<.>). This will work:
477 [ {profile => { customer => 'customer.name' }} ]
479 And will result in something like this:
481 $obj->customer($obj->meta->relationship('customer')->class->new);
482 $obj->customer->name($csv_line->{customer})
484 But beware, this will not try to look up anything in the database. You will
485 simply receive objects that represent what the profile defined. If some of
486 these information are unique, and should be connected to preexisting data, you
487 will have to do that for yourself. Since you provided the profile, it is
488 assumed you know what to do in this case.
490 If no profile is given, any header field found will be taken as is.
492 If the path in a profile entry is empty, the field will be subjected to
493 C<strict_profile> and C<case_insensitive_header> checking, will be parsed into
494 C<get_data>, but will not be attempted to be dispatched into objects.
496 If C<class> is present, the line will be handed to the new sub of this class,
497 and the return value used instead of the line itself.
499 C<row_ident> is a string to recognize the right profile and class for each data
500 line in multiplexed data. It must match the value in the column 'dataype' for
503 In case of multiplexed data, C<class> and C<row_ident> must be given.
506 class => 'SL::DB::Order',
510 class => 'SL::DB::OrderItem',
512 profile => {sellprice => sellprice_as_number}
515 =item C<ignore_unknown_columns>
517 If set, the import will ignore unkown header columns. Useful for lazy imports,
518 but deactivated by default.
520 =item C<case_insensitive_header>
522 If set, header columns will be matched against profile entries case
523 insensitive, and on match the profile name will be taken.
525 Only works if a profile is given, will die otherwise.
527 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
528 columns will be accepted.
530 =item C<strict_profile>
532 If set, all columns to be parsed must be specified in C<profile>. Every header
533 field not listed there will be treated like an unknown column.
535 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
536 columns will be accepted.
540 =head1 ERROR HANDLING
542 After parsing a file all errors will be accumulated into C<errors>.
543 Each entry is an object with the following attributes:
545 raw_input: offending raw input,
546 code: Text::CSV error code if Text:CSV signalled an error, 0 else,
547 diag: error diagnostics,
548 line: position in line,
549 col: estimated line in file,
551 Note that the last entry can be off, but will give an estimate.
559 sep_char, quote_char, and escape_char are passed to Text::CSV on creation.
560 Changing them later has no effect currently.
564 Encoding errors are not dealt with properly.
570 Dispatch to child objects, like this:
572 $csv = SL::Helper::Csv->new(
585 class => SL::DB::Part,
591 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>