Category: |
E-Mail Programs |
Author/Contact Info |
/msg George_Sherston |
Description: |
No rocket science - only posted here to save you quarter of an hour, and because it's the solution to a SOPW I posted. Does what it says on the can. Use the File Export utility in Outlook to create DOS CSV files from all your email folders. Put all these CSV files (I cut off the file extension - you may find you need to do this) in an otherwise empty directory. Change "db" below to the name of your directory. Create a MySQL table with the columns listed in @cols below, plus an extra auto-increment column "ID". (I made all the below cols TINYTEXTs except for Body which was LONGTEXT - database experts will use more optimal definitions.) NB you may also find, if you are executing the script on a different machine from that on which the CSV files were originally written (and if one's *NIX and the other M$) that DBI doesn't read your CSV files without you artificially put in MS linebreaks between records - this took me some hair-tearing to figure out.
(At the risk of being --ed by my fellow zealots, I should acknowledge that I ran this without warnings because with warnings, although it does what I wanted it to do, it raises the following:
Argument "" isn't numeric in scalar assignment at /usr/lib/perl5/site_
+perl/5.6.1/DBD/CSV.pm line 197
... which I am at a loss to understand.) |
#!/usr/bin/perl
use strict;
use DBI;
use utilities; # see below
my @cols = qw/
Subject
Body
FromName
FromAddress
FromType
ToName
ToAddress
ToType
CCName
CCAddress
CCType
BCCName
BCCAddress
BCCType
BillingInformation
Categories
Importance
Mileage
Sensitivity
/;
chdir 'db'; # or wherever you've put the files
my %insert;
my $dbh = DBI->connect("DBI:mysql:database=jbr", "admin", "1263");
my $csv = DBI->connect("DBI:CSV:") or die "connect" . $DBI::errstr;
my $count = 0;
for (<*>) {
print "\nstarting $_ . . . . ";
my $sth = $csv->prepare("SELECT * FROM $_") or die $DBI::errstr;
$sth->execute() or die $DBI::errstr;
my $n;
while (my @ref = $sth->fetchrow_array) {
@insert{@cols} = @ref;
$n = InsertAndGetID($dbh, 'email', \%insert, 'ID');
}
print "done - $n lines so far";
$count = $n;
$sth->finish();
}
$dbh->disconnect();
$csv->disconnect();
... you may also wish to know that utilities.pm, which is my own module, contains the following, inter alia:
sub
InsertAndGetID
{
#---------------------------------------------------------------
# inserts an entry into a db and gets the auto_increment ID
#---------------------------------------------------------------
my $dbh = shift;
my $table = shift;
my $Inserts = shift;
my $IDCol = shift;
$Inserts->{$IDCol} = 'NULL';
$dbh->do("LOCK TABLES $table WRITE") or die $dbh->errstr;
InsertMultipleValues($dbh,$table,$Inserts);
$sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM $table") or die
+ $dbh->errstr;
$sth->execute or die $dbh->errstr;
my @ary = $sth->fetchrow_array or die $dbh->errstr;
$dbh->do("UNLOCK TABLES") or die $dbh->errstr;
$sth->finish;
return $ary[0];
}
sub
InsertMultipleValues
{
#---------------------------------------------------------------
# Inserts contents of a hashref into the db table specified
#---------------------------------------------------------------
my $dbh = shift;
my $table = shift;
my $Inserts = shift;
my @cols = keys %$Inserts;
my @vals = @$Inserts{@cols};
my $cols = join ',', @cols;
my $places = '?,' x @vals;
chop $places;
my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES ($place
+s)") or die $dbh->errstr;
$sth->execute(@vals) or die "$dbh->errstr : $table";
}
|
•Re: Convert CSV export from Outlook - MySQL
by merlyn (Sage) on Jun 24, 2002 at 23:45 UTC
|
Maybe I'm missing it, but I don't see the DBD::CSV doing anything for you that Text::CSV wouldn't do faster, better, and cheaper. You're pulling out the columns in their natural order, and using them all.
Am I missing something, or are you using a jackhammer to drive a nail?
-- Randal L. Schwartz, Perl hacker | [reply] [Watch: Dir/Any] |
|
You're quite right... that's to say, I'm not using DBD::CSV because I want all its features, only because I know how to use it. As an enthusiast for power tools, the idea of using a jackhammer to drive a nail appeals... sure does drive that nail. But you're quite right, if I were optimising for anything but my own time, I'd want something without the overhead - and you're quite right to point that out here for benefit of posterity. /me takes his lumps :)
§ George Sherston
| [reply] [Watch: Dir/Any] |
|
I used your script but whenever I run the perl script it inserts a blank row in mysql. Can anyone tell me why it happens and it the command line I get also this error.
DBD::CSV::st execute failed: Missing first row at /usr/local/share/perl/5.6.1/DBD/CSV.pm line 171.
Missing first row at /usr/local/share/perl/5.6.1/DBD/CSV.pm line 171.
Arunav Mandal.
dibun@hotmail.com
| [reply] [Watch: Dir/Any] |
|
|
|