Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Convert CSV export from Outlook - MySQL

by George_Sherston (Vicar)
on Jun 24, 2002 at 22:36 UTC ( [id://176972]=sourcecode: print w/replies, xml ) Need Help??
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";
}
Replies are listed 'Best First'.
•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

      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
        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

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2024-03-29 14:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found