With a little side of applesauce...

Friday, September 26, 2008

Thunderbird - Preparing a csv file for importing into Gmail

I was sorely mistaken about Gmail now taking a slightly modified csv import from Thunderbird :( Apparently, it just throws everything into "notes", which works visually in the web GUI, but is not searched when auto-completing email addresses. So.... I modified my meeting maker import scripts to import thunderbird contacts as well.

Here is the awk statement to prepare a csv file (don't forget to remove the header line):
cat tbird_export.txt  | sed 's/,/|/g' |  tr -d '\015' | sed 's/"//g' | awk -F\| '{print $1,$2"|"$5"|"$12,$14,$15,$16,$17"|"$8"|"$7"|"$9"|"$3,$4,$6,$10,$11,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36 }' > tbird_import.txt


Here is the csv field breakdown:

   1-2           5      12-17         8            7            9         3-6,10-11,18-36
contact name | email | address | phone home | phone work | phone mobile | Notes


1 - First Name
2 - Last Name
3 - Display Name
4 - Nickname
5 - Primary Email
6 - Secondary Email
7 - Work Phone
8 - Home Phone
9 - Fax Number
10 - Pager Number
11 - Mobile Number
12 - Home Address
13 - Home Address 2
14 - Home City
15 - Home State
16 - Home ZipCode
17 - Home Country
18 - Work Address
19 - Work Address 2
20 - Work City
21 - Work State
22 - Work ZipCode
23 - Work Country
24 - Job Title
25 - Department
26 - Organization
27 - Web Page 1
28 - Web Page 2
29 - Birth Year
30 - Birth Month
31 - Birth Day
32 - Custom 1
33 - Custom 2
34 - Custom 3
35 - Custom 4
36 - Notes


Here is the run.sh:
#!/bin/sh
####################
#
# Copyright 200 Shannon Eric Peevey <speeves@stolaf.edu>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
#####################
#
# Contacts Import/Export script
# - exports contacts from the existing systems, and re-imports from the gmail systems.
#
####################

# load our common library
. ./common.sh

### Initialize variables
TMP="/tmp/"
IMPORT_FILE_PATH=""
DOMAIN="mydomain.edu"
MAX_RESULTS="10000"


usage="
---------------------------
Usage: ${0} [-hadbupo]

Help Options
-h This message

Actions
-a Action to perform
(
i|contactstogoogle - import contacts into gmail
ix|xmltogoogle - import contacts into gmail from gdata api export
e|contactsfromgoogle - export contacts from gmail

)

Variables
-u user account email address
-p user account password
-o import/export file (used to import contacts into gmail - see README.txt for more information)"
-d path to import file directory


while getopts "a:d:b:u:p:o:h" OPT
do
case $OPT in
a )
ACTION=$OPTARG
;;
u )
USERNAMETMP=$OPTARG
USERNAME=$(echo $USERNAMETMP | cut -d\@ -f1)
;;
p )
PASSWORD=$OPTARG
;;
o )
IMPORT_FILE=$OPTARG
;;
d )
IMPORT_FILE_PATH="${OPTARG}/"
;;
h )
echo "$usage"
exit 1
;;
\?)
echo "$usage"
exit 1;;
esac
done
# remove the flags from $@
shift $((${OPTIND} - 1))

# grab the action that we need to perform
case $ACTION in
i|contactstogoogle )
contactstogoogle
;;
ix|xmltogoogle )
xmltogoogle
;;
e|contactsfromgoogle )
contactsfromgoogle
;;
esac



And, the library file, common.sh:
#!/bin/sh
####################
#
# Copyright 2009 Shannon Eric Peevey <speeves@stolaf.edu>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
#####################
#
# Contacts Import/Export Library
# - exports contacts from the existing systems, and re-imports from the gmail systems.
#
####################

contactstogoogle()
{
# set tmpfolder
CONTACTFILE="${TMP}${USERNAME}.xml"
LOG_DATE=$(date +%d/%b/%Y:%X\ %z)

# let's wrap our migration steps here to simplify the function above
authtoken=$(getauthtoken)

### now we handle the import file
IMPORT_FILE_LOCATION="${IMPORT_FILE_PATH}${IMPORT_FILE}"
while read abook_line
do
contact_name=$(echo $abook_line | cut -d\| -f1)
contact_email=$(echo $abook_line | cut -d\| -f2)
contact_address=$(echo $abook_line | cut -d\| -f3)
contact_phone_home=$(echo $abook_line | cut -d\| -f4)
contact_phone_work=$(echo $abook_line | cut -d\| -f5)
contact_phone_mobile=$(echo $abook_line | cut -d\| -f6)
notes="$(echo $abook_line | awk -F\| '{print $7 }')"

if [ "$contact_name" != "" ]; then
usertitle="${contact_name}"
else
usertitle="Unnamed Contact"
fi

if [ "${contact_email}" != "" ]; then
xmlcreateimport
postentryimport
fi
done < ${IMPORT_FILE_LOCATION}

}

xmltogoogle()
{
# set tmpfolder
#CONTACTFILE="${TMP}${USERNAME}.xml"
LOG_DATE=$(date +%d/%b/%Y:%X\ %z)

# let's wrap our migration steps here to simplify the function above
authtoken=$(getauthtoken)

### now we handle the import file
for f in $(ls ${IMPORT_FILE_PATH})
do
CONTACTFILE="${IMPORT_FILE_PATH}${f}"
postentryimportbatch
done
}


contactsfromgoogle()
{
# set tmpfolder
LOG_DATE=$(date +%d/%b/%Y:%X\ %z)

# let's wrap our migration steps here to simplify the function above
authtoken=$(getauthtoken)

### now we handle the import file
EXPORT_FILE_LOCATION="${IMPORT_FILE_PATH}${IMPORT_FILE}"

totalcontacts=$(postentryexportgettotal)
echo ${totalcontacts}
INDEX=1
MAX_RESULTS=99
iter=1
while [ ${INDEX} -lt $(( $totalcontacts + 99 )) ];
do
#xmlcreateexport
postentryexport > ${EXPORT_FILE_LOCATION}.${iter}
iter=$(( $iter + 1 ))
INDEX=$(( $INDEX + 99 ))
done

}

getauthtoken()
{
# use curl to get auth token
if [ "$USERNAME" = "" ] || [ "$PASSWORD" = "" ]; then
echo "$usage"
exit 1
else
curl -s https://www.google.com/accounts/ClientLogin -d Email=${USERNAME}@${DOMAIN} -d Passwd=${PASSWORD} -d accountType=HOSTED -d source=Google-cURL-Example-${USERNAME} -d service=cp | grep Auth
fi
}

postentryimport()
{
# use curl to post the xml batch feed that was created
# http://www.google.com/m8/feeds/contacts/liz%40gmail.com/full
curl -v -s --url http://www.google.com/m8/feeds/contacts/${USERNAME}@${DOMAIN}/full --header "Authorization: GoogleLogin ${authtoken}" --header "Content-Type: application/atom+xml" --data "@${CONTACTFILE}"
}

postentryimportbatch()
{
# use curl to post the xml batch feed that was created
# http://www.google.com/m8/feeds/contacts/liz%40gmail.com/full
curl -v -s --url http://www.google.com/m8/feeds/contacts/${USERNAME}@${DOMAIN}/full/batch --header "Authorization: GoogleLogin ${authtoken}" --header "Content-Type: application/atom+xml" --data "@${CONTACTFILE}" | tidy -xml -indent -quiet

}

postentryexport()
{
# use curl to post the xml batch feed that was created
# http://www.google.com/m8/feeds/contacts/liz%40gmail.com/full
curl -v -s --url http://www.google.com/m8/feeds/contacts/${USERNAME}@${DOMAIN}/full?start-index=${INDEX}\&max-results=${MAX_RESULTS} --header "Authorization: GoogleLogin ${authtoken}" --header "Content-Type: application/atom+xml"
}

postentryexportgettotal()
{
# use curl to post the xml batch feed that was created
# http://www.google.com/m8/feeds/contacts/liz%40gmail.com/full
curl -v -s --url http://www.google.com/m8/feeds/contacts/${USERNAME}@${DOMAIN}/full?max-results=${MAX_RESULTS} --header "Authorization: GoogleLogin ${authtoken}" --header "Content-Type: application/atom+xml" | tidy -xml -indent -quiet | grep '<openSearch:totalResults>' | cut -d\> -f2 | cut -d\< -f1
}


xmlcreateimport()
{
echo "<atom:entry xmlns:atom='http://www.w3.org/2005/Atom' xmlns:gd='http://schemas.google.com/g/2005'>" > ${CONTACTFILE}
echo " <atom:category scheme='http://schemas.google.com/g/2005#kind' term='http://schemas.google.com/contact/2008#contact' />" >> ${CONTACTFILE}
echo " <atom:title type='text'>${usertitle}</atom:title>" >> ${CONTACTFILE}
echo " <atom:content type='text'>${notes: -Notes}</atom:content>" >> ${CONTACTFILE}
echo " <gd:email rel='http://schemas.google.com/g/2005#work' address=\"${contact_email}\" />" >> ${CONTACTFILE}
echo " <gd:email rel='http://schemas.google.com/g/2005#home' address=\"${contact_email}\" />" >> ${CONTACTFILE}
if [ "${contact_phone_home}" != "" ]; then
echo " <gd:phoneNumber rel='http://schemas.google.com/g/2005#home' primary='true'>${contact_phone_home}</gd:phoneNumber>" >> ${CONTACTFILE}
fi
if [ "${contact_phone_work}" != "" ]; then
echo " <gd:phoneNumber rel='http://schemas.google.com/g/2005#work' >${contact_phone_work}</gd:phoneNumber>" >> ${CONTACTFILE}
fi
if [ "${contact_phone_mobile}" != "" ]; then
echo " <gd:phoneNumber rel='http://schemas.google.com/g/2005#mobile' >${contact_phone_mobile}</gd:phoneNumber>" >> ${CONTACTFILE}
fi
if [ "${contact_address}" != "" -a "${contact_address}" != " " ]; then
echo " <gd:postalAddress rel='http://schemas.google.com/g/2005#other' primary='true'>${contact_address}</gd:postalAddress>" >> ${CONTACTFILE}
fi
echo "</atom:entry>" >> ${CONTACTFILE}
}

No comments: