1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
#!/usr/bin/perl -w
#This perl script attempts to convert a Microsoft Outlook 2000 email
#address list that has been exported as Comma Separated Values (DOS)
#into Pronto mail that is stored in an SQL database.
#
#Copyright Adam Warner and distributed under the terms of the
#GNU General Public License.
use DBI;
if ($#ARGV<3)
{
print "\nMSOutlookAddresses2SQLPronto.pl Version 1.01\n\nThis perl script attempts to convert a Microsoft Outlook 2000 email\naddress list that has been exported as Comma Separated Values (DOS)\ninto Pronto mail that is stored in an SQL database.\n\nCopyright Adam Warner and distributed under the terms of the GNU General Public License.\n";
print "\nYou must supply four to seven arguments:\n";
print " (1) The filename to import\n";
print " (2) The type of database (e.g. mysql)\n";
print " (3) The name of the database (i.e. your username)\n";
print "(4-7) the parts of the name and the order to import:\n";
print " Where \"T\" is the Title\n";
print " \"F\" is the First Name\n";
print " \"M\" is the Middle Name\n";
print " and \"L\" is the Last Name\n";
print "\nFor example:\n";
print "\$ ./MSOutlookAddresses2SQLPronto.pl addresses.CSV mysql bob T F M L\n\n";
exit;
}
$filename=$ARGV[0];
$database="DBI:$ARGV[1]:$ARGV[2]";
#Arrays start at zero, thus five values are initialized even though a maximum of 1-4 elements are used
@nameord = ("","","","",""); @nameordvar = ("","","","","");
#Read in T, F, M, L arguments
$i=3;
while ($ARGV[$i])
{
$nameord[$i-2]=uc(substr($ARGV[$i],0,1));
$i++;
}
$nameparts=$i-3;
$firstnamepos=0; $lastnamepos=0; #Used for adding a comma after lastname, if necessary
for ($i=1; $i<=$nameparts; $i++)
{
if ($nameord[$i] eq "T") { $nameordvar[$i] = '$title'; }
if ($nameord[$i] eq "F") { $nameordvar[$i] = '$firstname'; $firstnamepos=$i; }
if ($nameord[$i] eq "M") { $nameordvar[$i] = '$middlename'; }
if ($nameord[$i] eq "L") { $nameordvar[$i] = '$lastname'; $lastnamepos=$i; }
}
if ($lastnamepos<$firstnamepos) { $nameordvar[$lastnamepos] = '$lastname,'; }
$constructfullname = "$nameordvar[1] $nameordvar[2] $nameordvar[3] $nameordvar[4]";
#Read database and determine current maximum index number
$dbh = DBI->connect($database);
$statement = "select id from addresses";
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$rv = $sth->execute or die "Can't execute $statement: $sth->errstr\n";
$highid=0;
while ($id = $sth->fetchrow_array)
{
if ($highid<$id) { $highid=$id; }
}
open ($addresses,$filename) || die "Can't open $filename\n";
$line=<$addresses>; #Kill the first column headings entry
$id=$highid+1;
$addalladdresses = "n";
while ($line=<$addresses>) # $line is non-zero until EOF
{
$_= $line;
my $title = $1 if /\"?(.*?)\"?,/;
$_= $line;
my $firstname = $1 if /,\"?(.*?)\"?,/;
$_= $line;
my $middlename = $1 if /,.*?,\"?(.*?)\"?,/;
$_= $line;
my $lastname = $1 if /,.*?,.*?,\"?(.*?)\"?,/;
$_= $line;
my $email = $1 if /,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,\"?(.*?)\"?,/;
#Let's formulate the name as desired
$fullname = $constructfullname;
$fullname =~ s/(\$\w+)/$1/eeg;
#And eliminate any beginning or trailing spaces
$fullname =~ s/^\s+//;
$fullname =~ s/\s+$//;
#And eliminate any double or triple spaces
$fullname =~ s/ / /;
$fullname =~ s/ / /;
#Time to update the database
$statement = "insert into addresses (id,alias,address) values (\"$id\",\"$fullname\",\"$email\")";
if ($addalladdresses eq "n")
{
print "\n$statement\nExecute above statement/add email address [y(es)/n(no)/a(ll)]? ";
$update=lc(substr(<STDIN>,0,1));
if ($update eq "a") { $addalladdresses = "y"; }
}
if ($update eq "y" | $addalladdresses eq "y")
{
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$rv = $sth->execute or die "Can't execute $statement: $sth->errstr\n";
$id=$id+1;
}
}
print "Done.\n";
|
