?
Solved

Perl CSV to Access- IF RECORD DOESNT EXIST

Posted on 2010-11-20
1
Medium Priority
?
544 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 26

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

743 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