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;
115 # If header is given, there need to be a header for each profile
116 # and no empty headers.
117 if ($info_ok && $self->header) {
118 my @header = @{ $self->header };
119 $info_ok = $info_ok && scalar @profile == scalar @header;
120 $info_ok = $info_ok && all { scalar @$_ > 0} @header;
122 $self->is_multiplexed($info_ok);
127 # ok, if not multiplexed
132 my ($self, %params) = @_;
135 $header = $self->header;
137 my $n_header = ($self->is_multiplexed)? scalar @{ $self->profile } : 1;
138 foreach my $p_num (0..$n_header - 1) {
139 my $h = $self->_csv->getline($self->_io);
142 $self->_csv->error_input,
143 $self->_csv->error_diag,
147 push @{ $header }, $h;
151 # Special case: utf8 BOM.
152 # certain software (namely MS Office and notepad.exe insist on prefixing
153 # data with a discouraged but valid byte order mark
154 # if not removed, the first header field will not be recognized
156 my $h = $header->[0];
157 if ($h && $h->[0] && $self->encoding =~ /utf-?8/i) {
158 $h->[0] =~ s/^\x{FEFF}//;
162 # check, if all header fields are parsed well
163 return unless $header && all { $_ } @$header;
165 # Special case: human stupidity
166 # people insist that case sensitivity doesn't exist and try to enter all
167 # sorts of stuff. at this point we've got a profile (with keys that represent
168 # valid methods), and a header full of strings. if two of them match, the user
169 # mopst likely meant that field, so rewrite the header
170 if ($self->case_insensitive_header) {
171 die 'case_insensitive_header is only possible with profile' unless $self->profile;
174 foreach my $h (@{ $header }) {
176 keys %{ $self->profile->[$p_num]->{profile} || {} },
178 for my $name (@names) {
179 for my $i (0..$#$h) {
180 $h->[$i] = $name if lc $h->[$i] eq lc $name;
188 return $self->header($header);
192 my ($self, %params) = @_;
196 my $row = $self->_csv->getline($self->_io);
198 my $header = $self->_header_by_row($row);
200 @hr{@{ $header }} = @$row;
203 last if $self->_csv->eof;
204 # Text::CSV_XS 0.89 added record number to error_diag
205 if (qv(Text::CSV_XS->VERSION) >= qv('0.89')) {
207 $self->_csv->error_input,
208 $self->_csv->error_diag,
212 $self->_csv->error_input,
213 $self->_csv->error_diag,
214 $self->_io->input_line_number,
218 last if $self->_csv->eof;
221 $self->_data(\@data);
222 $self->_push_error(@errors);
228 my ($self, $row) = @_;
230 # initialize lookup hash if not already done
231 if ($self->is_multiplexed && ! defined $self->_row_header ) {
232 $self->_row_header({ pairwise { $a->{row_ident} => $b } @{ $self->profile }, @{ $self->header } });
235 if ($self->is_multiplexed) {
236 return $self->_row_header->{$row->[0]}
238 return $self->header->[0];
243 ':encoding(' . $_[0]->encoding . ')';
247 my ($self, %params) = @_;
250 local $::myconfig{numberformat} = $self->numberformat if $self->numberformat;
251 local $::myconfig{dateformat} = $self->dateformat if $self->dateformat;
253 for my $line (@{ $self->_data }) {
254 my $tmp_obj = $self->dispatcher->dispatch($line);
255 push @objs, $tmp_obj;
258 $self->_objects(\@objs);
262 my ($self, %params) = @_;
264 $self->{_dispatcher} ||= $self->_make_dispatcher;
267 sub _make_dispatcher {
268 my ($self, %params) = @_;
270 die 'need a header to make a dispatcher' unless $self->header;
272 return SL::Helper::Csv::Dispatcher->new($self);
275 sub _guess_encoding {
281 my ($self, @errors) = @_;
282 my @new_errors = ($self->errors, map { SL::Helper::Csv::Error->new(@$_) } @errors);
283 $self->_errors(\@new_errors);
295 SL::Helper::Csv - take care of csv file uploads
301 my $csv = SL::Helper::Csv->new(
302 file => \$::form->{upload_file},
303 encoding => 'utf-8', # undef means utf8
304 sep_char => ',', # default ';'
305 quote_char => '\'', # default '"'
306 escape_char => '"', # default '"'
307 header => [ [qw(id text sellprice word)] ], # see later
308 profile => [ { profile => { sellprice => 'sellprice_as_number'},
309 class => 'SL::DB::Part' } ],
312 my $status = $csv->parse;
313 my $hrefs = $csv->get_data;
314 my @objects = $csv->get_objects;
316 my @errors = $csv->errors;
322 Text::CSV offeres already good functions to get lines out of a csv file, but in
323 most cases you will want those line to be parsed into hashes or even objects,
324 so this model just skips ahead and gives you objects.
326 Its basic assumptions are:
330 =item You do know what you expect to be in that csv file.
332 This means first and foremost you have knowledge about encoding, number and
333 date format, csv parameters such as quoting and separation characters. You also
334 know what content will be in that csv and what L<Rose::DB> is responsible for
335 it. You provide valid header columns and their mapping to the objects.
337 =item You do NOT know if the csv provider yields to your expectations.
339 Stuff that does not work with what you expect should not crash anything, but
340 give you a hint what went wrong. As a result, if you remember to check for
341 errors after each step, you should be fine.
343 =item Data does not make sense. It's just data.
345 Almost all data imports have some type of constraints. Some data needs to be
346 unique, other data needs to be connected to existing data sets. This will not
347 happen here. You will receive a plain mapping of the data into the class tree,
352 This module can handle multiplexed data of different class types. In that case
353 multiple profiles with classes and row identifiers must be given. Multiple
354 headers may also be given or read from csv data. Data must contain the row
355 identifier in the first column and it's field name must be 'datatype'.
365 Standard constructor. You can use this to set most of the data.
369 Do the actual work. Will return true ($self actually) if success, undef if not.
373 Parse the data into objects and return those.
375 This method will return list or arrayref depending on context.
379 Returns an arrayref of the raw lines as hashrefs.
383 Return all errors that came up during parsing. See error handling for detailed
394 The file which contents are to be read. Can be a name of a physical file or a
395 scalar ref for memory data.
399 Encoding of the CSV file. Note that this module does not do any encoding
400 guessing. Know what your data is. Defaults to utf-8.
408 Same as in L<Text::CSV>
410 =item C<header> \@HEADERS
412 If given, it contains an ARRAYREF for each different class type (i.e. one
413 ARRAYREF if the data is only of one class type). These ARRAYREFS are the header
414 fields which are an array of columns. In this case the first lines are not used
415 as a header. Empty header fields will be ignored in objects.
417 If not given, headers are taken from the first n lines of data, where n is the
418 number of different class types.
422 classic data of one type:
423 [ [ 'name', 'street', 'zipcode', 'city' ] ]
425 multiplexed data with two different types
426 [ [ 'ordernumber', 'customer', 'transdate' ], [ 'partnumber', 'qty', 'sellprice' ] ]
428 =item C<profile> [{profile => \%ACCESSORS, class => class, row_ident => ri},]
430 This is an ARRAYREF to HASHREFs which may contain the keys C<profile>, C<class>
433 The C<profile> is a HASHREF which may be used to map header fields to custom
436 [ {profile => { listprice => listprice_as_number }} ]
438 In this case C<listprice_as_number> will be used to read in values from the
441 In case of a One-To-One relationsship these can also be set over
442 relationsships by sparating the steps with a dot (C<.>). This will work:
444 [ {profile => { customer => 'customer.name' }} ]
446 And will result in something like this:
448 $obj->customer($obj->meta->relationship('customer')->class->new);
449 $obj->customer->name($csv_line->{customer})
451 But beware, this will not try to look up anything in the database. You will
452 simply receive objects that represent what the profile defined. If some of
453 these information are unique, and should be connected to preexisting data, you
454 will have to do that for yourself. Since you provided the profile, it is
455 assumed you know what to do in this case.
457 If no profile is given, any header field found will be taken as is.
459 If the path in a profile entry is empty, the field will be subjected to
460 C<strict_profile> and C<case_insensitive_header> checking, will be parsed into
461 C<get_data>, but will not be attempted to be dispatched into objects.
463 If C<class> is present, the line will be handed to the new sub of this class,
464 and the return value used instead of the line itself.
466 C<row_ident> is a string to recognize the right profile and class for each data
467 line in multiplexed data.
469 In case of multiplexed data, C<class> and C<row_ident> must be given.
472 class => 'SL::DB::Order',
476 class => 'SL::DB::OrderItem',
478 profile => {sellprice => sellprice_as_number}
481 =item C<ignore_unknown_columns>
483 If set, the import will ignore unkown header columns. Useful for lazy imports,
484 but deactivated by default.
486 =item C<case_insensitive_header>
488 If set, header columns will be matched against profile entries case
489 insensitive, and on match the profile name will be taken.
491 Only works if a profile is given, will die otherwise.
493 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
494 columns will be accepted.
496 =item C<strict_profile>
498 If set, all columns to be parsed must be specified in C<profile>. Every header
499 field not listed there will be treated like an unknown column.
501 If both C<case_insensitive_header> and C<strict_profile> is set, matched header
502 columns will be accepted.
506 =head1 ERROR HANDLING
508 After parsing a file all errors will be accumulated into C<errors>.
509 Each entry is an object with the following attributes:
511 raw_input: offending raw input,
512 code: Text::CSV error code if Text:CSV signalled an error, 0 else,
513 diag: error diagnostics,
514 line: position in line,
515 col: estimated line in file,
517 Note that the last entry can be off, but will give an estimate.
525 sep_char, quote_char, and escape_char are passed to Text::CSV on creation.
526 Changing them later has no effect currently.
530 Encoding errors are not dealt with properly.
536 Dispatch to child objects, like this:
538 $csv = SL::Helper::Csv->new(
551 class => SL::DB::Part,
557 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>