DBD::CSV Update

PERL_fresher has asked for the wisdom of the Perl Monks concerning the following question:

Hello Fellow monks I need some help with the update of one of the CSV files that Im trying to do. Basically one of the file has set of records which I dont want to update for example

Souce Data
X~Y~Z
1~2~3
col1~col2~col3~col4~col5
A~B~C~D~E
[download]

Above is the file format. what i need to do is to start updating this table from row 3 where the actual data is

my $dbh = DBI->connect("dbi:CSV:",undef,undef,{
                        f_dir           =>      $_ENV::TempFilesDIR,
                        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)]



$dbh->do('UPDATE cimp_money SET forename=?,surname=? WHERE emp_no=?',u
+ndef,$wrk_forename,$wrk_surname,$wrk_emp_no) or die $DBI::errstr;
[download]

Basically updates are happening correctly but the issue Im facing is since the header records i.e. first 2 lines are just 3 columns and details records has 4 columns its assuming that even in first 2 rows also it has a header and its updating ~ to row 1 and 2 which is what I want to remove now

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

Comment on DBD::CSV Update Select or Download Code

Replies are listed 'Best First'.
Re: DBD::CSV Update by CountZero (Bishop) on May 28, 2015 at 15:51 UTC
Some of the basic rules of relational databases say that you MUST have a field name for each of the fields and that all the records in the table must have an entry for each of the fields. SQL wrongly allows you to have "NULL" for some of these entries although that is abhorrent and will one day cause you trouble. It therefore means that your "database file" is not in an acceptable format: the first row should be field names and nothing but field names and all other rows should be data and nothing but data. Anything else can only lead to problems and errors. Skipping some rows as Anonymous Monk suggests may save your bacon now but should only be considered as a stop-gap measure. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply]
Re: DBD::CSV Update by Anonymous Monk on May 28, 2015 at 11:18 UTC
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 Read more... (4 kB)	[reply] [d/l] [select]
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]
Re^3: DBD::CSV Update by soonix (Chancellor) on Jun 01, 2015 at 10:37 UTC
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]
Re^4: DBD::CSV Update by Tux (Canon) on Jun 01, 2015 at 13:19 UTC
Re^5: DBD::CSV Update by soonix (Chancellor) on Jun 02, 2015 at 09:38 UTC
Re: DBD::CSV Update by Anonymous Monk on May 28, 2015 at 09:50 UTC
Code tags aren't just for code you know, they're for that too, you should update your node and put this "file format" inside code tags	[reply]
Re^2: DBD::CSV Update by Anonymous Monk on May 28, 2015 at 09:55 UTC
heh, s/that/thata/data/:P	[reply]
Re^3: DBD::CSV Update by PERL_fresher (Novice) on May 28, 2015 at 10:04 UTC
I am looking to add here something which will basically read the csv file from 3rd file and now do anything with first 2 lines. Is it possible? `$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)]` [download]	[reply] [d/l]
Re^4: DBD::CSV Update by Anonymous Monk on May 28, 2015 at 10:47 UTC