Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Convert CSV export from Outlook - MySQL

by George_Sherston (Vicar)
on Jun 24, 2002 at 22:36 UTC ( #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/ line 197
... which I am at a loss to understand.)

use strict;
use DBI;
use utilities;     # see below

my @cols = qw/
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;
... you may also wish to know that, which is my own module, contains the following, inter alia:

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


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

    return $ary[0];


# 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/ line 171. Missing first row at /usr/local/share/perl/5.6.1/DBD/ line 171. Arunav Mandal.
Log In?

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (3)
As of 2020-08-09 05:33 GMT
Find Nodes?
    Voting Booth?
    Which rocket would you take to Mars?

    Results (54 votes). Check out past polls.