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: ... 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"; } |
|
---|
Replies are listed 'Best First'. | |
---|---|
•Re: Convert CSV export from Outlook - MySQL
by merlyn (Sage) on Jun 24, 2002 at 23:45 UTC | |
by George_Sherston (Vicar) on Jun 25, 2002 at 00:03 UTC | |
by Anonymous Monk on Dec 19, 2002 at 14:51 UTC | |
by Anonymous Monk on Dec 06, 2007 at 15:53 UTC |
Back to
Code Catacombs