Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Extracting data from a pipe delimited text file into a table in Access

by pfaut (Priest)
on May 03, 2003 at 02:01 UTC ( #255230=note: print w/replies, xml ) Need Help??


in reply to Extracting data from a pipe delimited text file into a table in Access

I don't know what Access is complaining about but here's some hints to clean up your code.

  • You don't need a 'use' statement for the DBD backend you will be using. DBI takes care of loading a driver based on the connect string you provide.
  • Prepare your statements outside the loop, execute them inside. Once prepared, a statement can be reused an unlimited number of times.
  • You can take the list returned by split and pass it directly to $sth->execute(). You don't have to use all of those nasty looking scalars (BTW, ++ for using placeholders).
  • You should add 'use strict' to the top of your script and declare your variables. This will help you catch a lot of errors at compile time.
# !/usr/bin/perl -w use strict; use DBI qw(:sql_types); my $dsn = "dbi:ODBC:Daily_Rec"; my $dbh = DBI->connect($dsn,'','', {RaiseError=>1,PrintError=>1,AutoCommit=>1} ); open(DAT, "c:/Temp/heldreceipts_TempFile.txt") or die "could not open Held Receipts file: $!"; my $query = qq{insert into Held_Receipts values (?,?,?,?,?,?,?,?)}; my $sth = $dbh->prepare($query) or die "cannot prepare query: $DBI::errstr"; while(<DAT>) { chomp; my @line = split /\|/; $sth->execute(@line) or die "Execute error: $DBI::errstr"; } $sth->finish(); $dbh->disconnect();

Update: I think TVSET's advice is good. If you can, attempt to run the statement through some other interface and see if it complains, too.

90% of every Perl application is already written.
dragonchild

Replies are listed 'Best First'.
Re: Re: Extracting data from a pipe delimited text file into a table in Access
by Anonymous Monk on May 05, 2003 at 13:14 UTC
    Thanks for your help! when i run your script i dont get any errors and the table does not get populated.
    also when i print out $query
    i get:
    insert into Held_Receipts values (?,?,?,?,?,?,?,?)
    any help would be greatly appreciated!!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://255230]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (8)
As of 2021-03-01 10:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?