asked on

Converting to DBI database

Hi,

I'm currently using open/close to access/modify my data.csv:

Contents of data.csv:
# OS | Q1 | Q2 | Q3 | Q4 |
windows|8|9|7|3|4|3|3|6|3|3|2|5|3
redhat |8|10|5|9|6|1|4|3|2|7|6|7|6
solaris|7|4|5|6|8|3|8|5|1|8|3|7|3
novell |3|6|7|3|8|4|0|5|3|9|4|6|2

How do i convert my below codes to DBI, I need some knowledge on
how to use/manipulate DBI to access my data.csv and modify the contents.

Please help, thanks!

My codes:
$product = param("product");
my $ans1 = param("answerone");
my $ans2 = param("answertwo");
my $ans3 = param("answerthree");
my $ans4 = param("answerfour");

# Reading from CSV File
my $file = "../../data/data.csv";
open(FILE, "+<$file") || die "can't open $file: $!";
flock(FILE, LOCK_EX);

my @contents = <FILE>;
if ($product eq "windows") { $i = 1; };
if ($product eq "redhat") { $i = 2; };
if ($product eq "solaris") { $i = 3; };
if ($product eq "novell") { $i = 4; };

# Increment Survey Answers value
chomp $contents[$i];
my @temprow = split(/\|/, $contents[$i]);
$temprow[$ans1]++;
$temprow[$ans2]++;
$temprow[$ans3]++;
$temprow[$ans4]++;
$temprow[13]++;
$contents[$i] = join("|", @temprow)."\n";

seek FILE,0,0;

# Writing to CSV File
print FILE @contents;
close(FILE) || die "can't close $file: $!";

ozo

use strict;
use DBI;
my $dbh = DBI->connect(qq{DBI:CSV:f_dir=../../data/;csv_sep_char=|;csv_eol=\n;"});
$dbh->{'csv_tables'}->{'data'} = {
file => 'data.csv',
col_names => ['product', 'q01'..'q13'],
};

eval{
my $sth = $dbh->prepare("SELECT ".(
join",",map{sprintf"q%02d",$_}$ans1,$ans2,$ans3,$ans4,13
)." FROM data WHERE product = ?");
$sth->execute($product);
die "product '$product' not found" unless my $row = $sth->fetchrow_hashref;
$_++ for values %$row;
$sth = $dbh->prepare("UPDATE data SET ".(
join",",map{"$_=$row->{$_}"}keys %$row
)." WHERE product = ?");
$sth->execute($product);
};
warn "SQL database error: $@" if $@;

ozo

Actually, that can have a problem if $ans1==0 or $ans1==$ans2

jedistar

ASKER

thanks i haven't tried it as i'm trying to code it fr scratch.
let say my contents are

data.csv:
windows|8|9|7|3|4|3|3|6|3|3|2|5|3
redhat |8|10|5|9|6|1|4|3|2|7|6|7|6
solaris|7|4|5|6|8|3|8|5|1|8|3|7|3
novell |3|6|7|3|8|4|0|5|3|9|4|6|2

what if the file is empty? hmm how do i create list of zero values.

Contents of data.csv:
windows|0|0|0|0|0|0|0|0|0|0|0|0
redhat |0|0|0|0|0|0|0|0|0|0|0|0|
solaris|0|0|0|0|0|0|0|0|0|0|0|0|
novell |0|0|0|0|0|0|0|0|0|0|0|0|

Lastly, is my format of csv correct?

thanks.

ozo

You probably don't want the spaces after redhat and novell
otherwise, the data.csv format above works fine with the DBI statements above

ASKER CERTIFIED SOLUTION

ozo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial