| Troubador 2004-05-22, 11:32 am |
| On Wed, 14 Apr 2004 16:26:22 +0000, E wrote:
> Im doing perl for an online store and wondering if someone knew a way (a
> script) to get my text files in to my mysql database?
Hi, E. I had this same issue facing me just a few w s ago. This is a
quick script to parse a (fixed-length) text file, then drop it into a
MySQL database. *NOTE* Be sure you create the database and tables _before_
you use this. Also, I developed this on a Windows box -- be sure to change
the shebang line if you're on a *nix box. Hopefully this will give you a
starting point.
Hope that helps!
/troubador
================= BEGIN CODE =====================
#!C:/perl/bin/perl.exe -w
use DBI();
$file = 'FILENAME';
$dbh = DBI->connect("dbi:mysql:database_name", "user_name", "password") or
die "Connecting: $DBI::errstr";
$sql_fmt = qq/
INSERT INTO main_mc
( zip1, zip2, senate_district, house_district, centroid_type, record_type, peculiarity_flag )
VALUES
(?, ?, ?, ?, ?, ?, ?)
/;
$sth = $dbh->prepare($sql_fmt);
open (FILE, $file) or die "Sorry, couldn't open $file for reading: $!";
while ( <FILE> ) {
$line = $_;
chomp($line);
$zip1 = substr($line, 0, 5);
$zip2 = substr($line, 5, 4);
$cong_dist = substr($line, 9, 2);
$senate_dist = substr($line, 11, 3);
$house_dist = substr($line, 14, 3);
$st_census_code= substr($line, 17, 2);
$co_census_code= substr($line, 19, 3);
$centroid_type = substr($line, 22, 1);
$record_type = substr($line, 23, 2);
$peculiarity_flag = substr($line, 25, 1);
$sth->execute( $zip1, $zip2, $senate_dist, $house_dist, $centroid_type, $record_type, $peculiarity_flag );
print "Entered: $zip1:$zip2:$senate_dist:$house_dist:$ce
ntroid_type:$record_type:$peculiarity_fl
ag\n";
$sth->finish;
} #foreach $line
$dbh->disconnect;
close (FILE) or die "Sorry. Couldn't close $file: $!";
print "DONE!\n\n";
exit 0;
================= END CODE =====================
|