Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Reading from a file and writing the content into a database

by Kiko (Scribe)
on Mar 31, 2000 at 21:32 UTC ( #6600=perlquestion: print w/replies, xml ) Need Help??
Kiko has asked for the wisdom of the Perl Monks concerning the following question:

Hey, I'm trying to read from a file and then take the information from that file parse it, and insert it into an access database. I know how to read from a file and write to that file, and i also know how to read and write to an access database. Any help will be greatly appreciated. Thanks, Kiko
  • Comment on Reading from a file and writing the content into a database

Replies are listed 'Best First'.
Re: Reading from a file and writing the content into a database
by btrott (Parson) on Mar 31, 2000 at 22:00 UTC
    Okay... well, then which part are you having trouble with, since you know how to read the file, and you know how to write to the database?

    When I've done similar things in the past (though not using an Access database), I've used DBI to write to the database. I've just looked at CPAN, and there seems to be an ODBC plugin for DBI (DBD::ODBC).

    I don't know what kind of information you have in the file, but in general, I'd do something like this:

    use DBI; # connect to the database my $dbh = DBI->connect('dbi:ODBC:foo', 'username', 'password') or die "Can't connect: ", $DBI::errstr; # prepare a SQL statement to insert your data # (using placeholders ("?") so you only have to # compile the statement once) my $sth = $dbh->prepare(<<SQL) or die "Can't prepare: ", $dbh->errstr; insert into data (data_1, data_2) values (?, ?) SQL # open up your data file open FH, "data_file" or die "Can't open: $!"; while (<FH>) { chomp; # parse your data out of the file my($d1, $d2) = split /\t/; # assuming tab-separated data # mess around with your data here # .... # insert your data into the database $sth->execute($d1, $d2); } close FH; $sth->finish; $dbh->disconnect;
      my $sth = $dbh->prepare(<<SQL) or die "Can't prepare: ", $dbh->errstr; insert into data (data_1, data_2) values (?, ?) SQL
      You can do that with a here doc? that's just amazing. How in the world did you figure that out?
      #!perl/bin/perl use win32; use Win32::ODBC; use CGI qw(:standard); $data="ibd_lib.txt"; $test="test.txt"; my (@statement); # array of sql statements my $db=new Win32::ODBC("DSN=bisx;UID=;PWD="); ### report connection status if (!$db) { print "\nunable to connect to database!\n"; die(); } ### Open File for Reading open(FR, "< $data") || die "cannot open $data for reading: $!"; ###open(FW, "> $test") || die "cannot create $test: $!"; while(<FR>) { ### Write to Database push @statement, "INSERT INTO UserTestingE (ID) VALUES (\t$_\n)"; ### print FW $_; } ### Close the file close(FR) || die "cant close $ibd_lib: $!"; ###close(FW) || die "cant close $test: $!" ### Close the database $db->Close();
      I don't have the DBI module i get this error: Can't locate in @INC (@INC contains:

      The only way that i know how to write to an access databse is using the Win32::ODBC module just like above. I just don't know if i'm doing it right or what is going on. Please take a look at it and tell me what you think. the file is a comma delimited file that has about 10,000 lines and i need to take each line and insert it one after another in an access database. Thanks, Elvin

        You may want to try and download DBI, simply because it's good. But I'll try and give you some pointers with what you're using.

        For one thing, you build the @statements array but you never actually use it! Is this a test version that you have here, or what? The data won't get into the database without you actually calling the insert statements on the db. From quickly scanning the docs, it looks like you'd call the "Sql" method for each of your insert statements. So:

        for my $sql (@statements) { $db->Sql($sql); }
        Another thing: if you have a file with comma-delimited fields, why aren't you split-ing each line of the file somehow? I highly doubt that you actually want to insert the comma-separated string into the database verbatim... do you?

        With that in mind, you'll have to create a table in the database that reflects the layout of your comma-delimited file, so that you can split the line, then insert each field into its appropriate field in the db.

        Also, in your SQL statements:

        push @statement, "INSERT INTO UserTestingE (ID) VALUES (\t$_\n)";
        Why do you have the "\t" and "\n" values? Do you actually want those in your db fields? Also, you need to quote the value:
        push @statement, "INSERT INTO UserTestingE (ID) VALUES ('\t$_\n')";
        Else you'll get a SQL error, I think. W/r/t the quoting, you'd be better off using some built-in method to quote values (like built-in to Win32::ODBC), but I didn't see one when I looked at the docs.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://6600]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2018-02-21 07:52 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (276 votes). Check out past polls.