in reply to DBD::CSV Update

use  $dbh->{'csv_tables'}->{'cimp_money'}    = { skip_rows => 2, ... and it should skip them .... but this isn't exactly documented official api so use at your own risk 
#!/usr/bin/perl --
use strict;
use warnings;
use DBI;
use DBD::CSV;

#~ Souce Data
my $data = q{X~Y~Z
1~2~3
col1~col2~col3~col4~col5
A~B~C~D~E
};
use Path::Tiny qw/ path /;
path( 'goner/Aviva_cimp_money_positive.txt' )->touchpath;
path( 'goner/Aviva_cimp_money_positive.txt' )->spew_raw( $data );

#~ Output Data
#~ X~Y~Z~~
#~ 1~2~3~~
#~ col1~col2~col3~col4~col5
#~ A~B~C~D~E


my $dbh = DBI->connect("dbi:CSV:",undef,undef,{
#~                         f_dir           =>      $_ENV::TempFilesDIR
+,
                        f_dir           =>      'goner',
                        csv_sep_char    =>      "~",
                        csv_quote_space =>      0}) or die $DBI::errst
+r;

$dbh->{'csv_tables'}->{'cimp_money'}    = { 'file'     => 'Aviva_cimp_
+money_positive.txt',
                                             col_names => [qw(rcrd_typ
+e seq_no emp_no forename surname initial ni_no ttl gender dob emp_cnt
+rbtn emplyr_ctr
                                                              tax_peri
+od tax_yr exch_amt)],
#~                                                                 csv
+_skip_first_row  => 'yes',
#~                                                                 ski
+p_rows => 4, ## haha skip them all
                                                                skip_r
+ows => 2, ## haha skip them all
                                                          }; 
#~ dd( $dbh->{csv_tables} );


#~ $dbh->do('UPDATE cimp_money SET forename=?,surname=? WHERE emp_no=?
+',undef,$wrk_forename,$wrk_surname,$wrk_emp_no) or die $DBI::errstr;
#~ $dbh->do('UPDATE cimp_money SET forename=?,surname=? WHERE emp_no=?
+',undef,'$wrk_forename','$wrk_surname','$wrk_emp_no') or die $DBI::er
+rstr;
#~ $dbh->do('UPDATE cimp_money SET forename=?,surname=? WHERE emp_no=?
+',undef,'$wrk_forename','$wrk_surname',3) or die $DBI::errstr;
$dbh->do('UPDATE cimp_money SET forename=?,surname=? WHERE emp_no=?',u
+ndef,'$wrk_forename','$wrk_surname','col3') or die $DBI::errstr;


print path( 'goner/Aviva_cimp_money_positive.txt' )->lines_raw(  );
path( 'goner' )->remove_tree;
#~ dd( { %$dbh } ); dd( $dbh->{csv_tables} );
#~ dd( $dbh->{csv_tables} );
__END__
$ perl fresher-dbi-dbd-csv-1128113.pl
X~Y~Z
1~2~3
col1~col2~col3~$wrk_forename~$wrk_surname~~~~~~~~~~
A~B~C~D~E~~~~~~~~~~

[download]

Replies are listed 'Best First'.
Re^2: DBD::CSV Update
by Tux (Canon) on May 28, 2015 at 14:49 UTC

    The skip_rows option will skip valid CSV-rows (and will croak otherwise). Though the hint is good, I think that it doesn't currently do as required by the OP: it skips CSV lines after the header.

    When not passing the col_names like you did, it will skip the count of lines as passed like this after reading the header line, so in given example, that would still result in just 3 fields. That obviously needs a new option that would skip lines before the header line in whatever format, so a CSV file like this:

    This file holds the report
for sales over August 2013

id,count,description,price
FAIL,,Placeholder,0.00
\N,,,
10D2,4,Drill 9mm,0.80
11E1,1,Festool DRC 18/4.515.15

    [download]

    could be opened and correctly dealt with with code like:

    #!/usr/bin/perl

use 5.20.0;
use warnings;

use DBI;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_dir          => ".",
    csv_auto_diag  => 1,
    });

$dbh->{csv_tables}{stock} = {
    file        => "stock.csv",
    skip_rows   => 2,
    skip_before => 3,  # nonexisting option
    # look ma, no col_names
    };

my $sth = $dbh->prepare ("select * from stock");
$sth->execute;
while (my $row = $sth->fetch) {
    say "@$row";
    }

$dbh->do ("update foo set price = 420 where id = '11E1'");
$dbh->disconnect;

    [download]

    The new option however has no control over the format of the lines. I'll consider adding a new option for this.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
      Would it be a sensible suggestion to use a byte offset (as in seek) for this option?
      That would make it possible to have binary data in that "header".
[reply]

        Sounds like a recipe for failure. How do you think Joe Programmer will check the exact amount of *bytes* to the real data? Think UTF-8 and JPEG.

        As an alternative, one could think of a start-of-data-tag.

        Both however will only make life for DBD::CSV harder, as it has to put the same data back on the same place on updates. I'm still not convinced this would all be worth the trouble.


        Enjoy, Have FUN! H.Merijn
[reply]

In Section Seekers of Perl Wisdom