[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Perl CSV to Access- IF RECORD DOESNT EXIST

Posted on 2010-11-20
1
Medium Priority
?
547 Views
Last Modified: 2012-05-10
The code below works correctly and inserts values from the CSV file into the Access Database. How do I adjust the script to check and see if the record from the CSV file already exists in the database?

I do not want to import duplicate records. Thanks.
use DBI;

$fhour="00";
$count=0;
$finterval=12;

do {

$csvfile="C:/WeatherModel/degrib/bin/Model/Output/gfs_2010111900_" . $fhour. ".csv";

$dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=c:/weathermodel/weathermodel.mdb', '', '');
$sth = $dbh->prepare( "INSERT INTO GFS_Forecasts VALUES (?,?,?,?,?,?)" );

open CSV, $csvfile;


$x=0;
while(<CSV>) {


if ($x>0){
	  chomp;
  my @fields = split /,/;	

  $sth->execute(@fields);
}
$x++;

}

Open in new window

0
Comment
Question by:kyle972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 27

Accepted Solution

by:
wilcoxon earned 2000 total points
ID: 34181242
I think this will do what you want.  I know Access has some eccentricities so I'm not positive this will work - if not, let me know and I'll work around whatever the error is.

You will have to change the fieldX names in the sql after the comment line with XXX to match what is in the database.
use strict;
use warnings;
use DBI;

my $fhour="00";
my $count=0;
my $finterval=12;

# code seems to be incomplete - this do is never closed
# do {

my $csvfile="C:/WeatherModel/degrib/bin/Model/Output/gfs_2010111900_" . $fhour. ".csv";

my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=c:/weathermodel/weathermodel.mdb', '', '');
my $sth = $dbh->prepare( "INSERT INTO GFS_Forecasts VALUES (?,?,?,?,?,?)" );
# XXX - need to change field names to match database
my $sth2 = $dbh->prepare("select count(*) from GFS_Forecasts where field1 = ? and field2 = ? and field3 = ? and field4 = ? and field5 = ? and field6 = ?");

open CSV, $csvfile;

$x=0;
while(<CSV>) {
    if ($x>0){
        chomp;
        my @fields = split /,/;       
        $sth2->execute(@fields);
        my $cnt = $sth2->fetchrow_arrayref()->[0];
        $sth->execute(@fields) unless $cnt;
    }
    $x++;
}

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question