Search

Recent Posts

Tags


« | Main | »

Outlook 2002 Contacts CSV to vCard via Bash & Awk

By Dale Reagan | December 22, 2012

Yes, I know you can import stuff easily into ‘the cloud’ – I don’t care to share my contacts (directly) with any ‘cloud vendor’; I simply want to move my contacts from my old Cellphone to my new Android phone.

Exporting a contact list from Outlook (Ancient version, 2002) is simple using CSV (comma separated values) files.

Android pop-email can import the ‘vCard’ format.

Converting CSV to vCard is simple:

  1. review the CSV file (from Outlook export) and decide on which fields you want to use.
  2. review the vCard format and decide the same (VCard data format:   http://en.wikipedia.org/wiki/VCard)
  3. extract desired fields from the CSV and convert to vCard

CSV headers | Outlook 2002

The first line of a CSV file (from Outlook) should contains a header line describing the data fields – something like:

“Title”,”First Name”,”Middle Name”,”Last Name”,”Suffix”,”Company”,”Department”,”Job Title”,”Business Street”,”Business Street 2″,”Business Street 3″,”Business City”,”Business State”,”Business Postal Code”,”Business Country”,”Home Street”,”Home Street 2″,”Home Street 3″,”Home City”,”Home State”,”Home Postal Code”,”Home Country”,”Other Street”,”Other Street 2″,”Other Street 3″,”Other City”,”Other State”,”Other Postal Code”,”Other Country”,”Assistant’s Phone”,”Business Fax”,”Business Phone”,”Business Phone 2″,”Callback”,”Car Phone”,”Company Main Phone”,”Home Fax”,”Home Phone”,”Home Phone 2″,”ISDN”,”Mobile Phone”,”Other Fax”,”Other Phone”,”Pager”,”Primary Phone”,”Radio Phone”,”TTY/TDD Phone”,”Telex”,”Account”,”Anniversary”,”Assistant’s Name”,”Billing Information”,”Birthday”,”Business Address PO Box”,”Categories”,”Children”,”Company Yomi”,”Directory Server”,”E-mail Address”,”E-mail Type”,”E-mail Display Name”,”E-mail 2 Address”,”E-mail 2 Type”,”E-mail 2 Display Name”,”E-mail 3 Address”,”E-mail 3 Type”,”E-mail 3 Display Name”,”Gender”,”Given Yomi”,”Government ID Number”,”Hobby”,”Home Address PO Box”,”Initials”,”Internet Free Busy”,”Keywords”,”Language”,”Location”,”Manager’s Name”,”Mileage”,”Notes”,”Office Location”,”Organizational ID Number”,”Other Address PO Box”,”Priority”,”Private”,”Profession”,”Referred By”,”Sensitivity”,”Spouse”,”Surname Yomi”,”User 1″,”User 2″,”User 3″,”User 4″,”Web Page”

Ok, that’s kinda messy – here is a numbered list of fields generated via Bash/tr/Awk/Paste:

head -1 My_CSV_file.csv | grep "," | tr ',' '\n' | \   
           awk '{printf "%3d. %-20s\n", NR, $0}'  | \ 
           paste - - | tee CSV.hdrs.list.txt

The above set of commands produces a two-column list (of course, your headers may be different):

  1. "Title"                      2. "First Name"             
  3. "Middle Name"                4. "Last Name"              
  5. "Suffix"                     6. "Company"                
  7. "Department"                 8. "Job Title"              
  9. "Business Street"           10. "Business Street 2"      
 11. "Business Street 3"         12. "Business City"          
 13. "Business State"            14. "Business Postal Code"   
 15. "Business Country"          16. "Home Street"            
 17. "Home Street 2"             18. "Home Street 3"          
 19. "Home City"                 20. "Home State"             
 21. "Home Postal Code"          22. "Home Country"           
 23. "Other Street"              24. "Other Street 2"         
 25. "Other Street 3"            26. "Other City"             
 27. "Other State"               28. "Other Postal Code"      
 29. "Other Country"             30. "Assistant's Phone"      
 31. "Business Fax"              32. "Business Phone"         
 33. "Business Phone 2"          34. "Callback"               
 35. "Car Phone"                 36. "Company Main Phone"     
 37. "Home Fax"                  38. "Home Phone"             
 39. "Home Phone 2"              40. "ISDN"                   
 41. "Mobile Phone"              42. "Other Fax"              
 43. "Other Phone"               44. "Pager"                  
 45. "Primary Phone"             46. "Radio Phone"            
 47. "TTY/TDD Phone"             48. "Telex"                  
 49. "Account"                   50. "Anniversary"            
 51. "Assistant's Name"          52. "Billing Information"    
 53. "Birthday"                  54. "Business Address PO Box"
 55. "Categories"                56. "Children"               
 57. "Company Yomi"              58. "Directory Server"       
 59. "E-mail Address"            60. "E-mail Type"            
 61. "E-mail Display Name"       62. "E-mail 2 Address"       
 63. "E-mail 2 Type"             64. "E-mail 2 Display Name"  
 65. "E-mail 3 Address"          66. "E-mail 3 Type"          
 67. "E-mail 3 Display Name"     68. "Gender"                 
 69. "Given Yomi"                70. "Government ID Number"   
 71. "Hobby"                     72. "Home Address PO Box"    
 73. "Initials"                  74. "Internet Free Busy"     
 75. "Keywords"                  76. "Language"               
 77. "Location"                  78. "Manager's Name"         
 79. "Mileage"                   80. "Notes"                  
 81. "Office Location"           82. "Organizational ID Number"
 83. "Other Address PO Box"      84. "Priority"               
 85. "Private"                   86. "Profession"             
 87. "Referred By"               88. "Sensitivity"            
 89. "Spouse"                    90. "Surname Yomi"           
 91. "User 1"                    92. "User 2"                 
 93. "User 3"                    94. "User 4"                 
 95. "Web Page"

Ok, that's a bit easier to review. For my use I'm interested in vCard Fields - to create my list I use:

head -1 My_CSV_file.csv | grep “,” | tr ‘,’ ‘\n’ | \
      awk ‘{printf “%3d. %-20s\n”, NR, $0}’ | \      
      egrep -i ‘last n|first n|e-mail ad|”home ph”|mobile ph|”business ph”|web|notes|”company”‘

The numbers  2, 4, 6, 8, 32, …,95  are important – these are the fields (column locations) that we will pass to ‘awk’.

  2. "First Name"             
  4. "Last Name"              
  6. "Company"                
  8. "Job Title"              
 32. "Business Phone"         
 38. "Home Phone"             
 41. "Mobile Phone"           
 59. "E-mail Address"         
 80. "Notes"                  
 95. "Web Page"

Ok, I’ve identified the fields that I want to use – what does vCard formatted data look like?  From the WikiPedia link above I see that the only required fields (which vary based on vCard version) example:

BEGIN:VCARD
Version:2.1
N:Last;First;;Title;
END:VCARD

Looking a bit further into the format I note that you essentially have ‘qualifiers’ or ‘descriptors’ associated with ‘base fields’ – this allows you to re-use your ‘base’ items (i.e. like phone, address, etc.)  An example with descriptions ‘borrowed’ from WikiPedia:

#########
BEGIN:VCARD
VERSION:2.1

N:Gump;Forrest | A structured representation of the name of the person, place or thing associated with the vCard object.

FN:Forrest Gump | The formatted name string associated with the vCard object.

ORG:Bubba Gump Shrimp Co. | The name and optionally the unit(s) of the organization associated with the vCard object. This property is based on the X.520 Organization Name attribute and the X.520 Organization Unit attribute.

TITLE:Shrimp Man | Specifies the job title, functional position or function of the individual associated with the vCard object within an organization.

TEL;WORK;VOICE: (111) 555-1212
TEL;HOME;VOICE: (404) 555-1212 | The canonical number string for a telephone number for telephony communication with the vCard object.

EMAIL: bubbagump@somewhere.com |  electronic mail communication with the vCard object

REV: 12/21/12 | A timestamp for the last time the vCard was updated – noting that we are still here along with the Mayan calender…

END:VCARD
###########

I decide to EXPORT a few contacts from my Android phone – then I will review the structure and use that for the ‘conversion’…

Guess What?  You cannot easily EDIT/DELETE contacts via the Phone interface…. Hmmm – steps to delete a contact:

  1. select Phone
  2. select Contact List
  3. select small triangle (guessing from ‘image’ ) to the left of the phone number
  4. select the contact ‘icon’ – center-right of screen
  5. select ‘settings’ (three dots, top right-hand of screen)
  6. now you can edit/delete the contact

At this point I decided to pre-edit the contact list in Outlook – time to remove tons of old, gig-related contacts (need  to whittle down from ~500 to something more reasonable…)  Once the list is pruned then I will export as CSV from Outlook – steps are:

  1. edit/prune Outlook Contact list (using Outlook)
  2. File -> Import and Export
  3. Export to a file
  4. Comma Separated Values (DOS)
  5. Select the ‘Contacts’ Folder (noting that you can also export any folder in the list)
  6. Set the output file location and name
  7. Map Custom Fields (you can limit what gets exported and wind up with a much smaller file to deal with)
  8. Finish

Now I copy the CSV file to my Linux system and run the commands below to create a vCard file that can be imported into the ‘phone contacts’ on the Nexus 4 (Android phone.)  The Bash script below can be used to create vCard data from Outlook 2002 CSV ‘contact data‘ – use at your own discretion/risk.


#!/bin/bash
# (C) 2012 Dale Reagan | Outlook.csv.2.vcard.sh
# http://web-tech.ga-usa.com/?=vcard 
# #################################
# Convert Outlook 2002 CSV to vCard
### set a limit - use some number larger than your number of contacts
## to convert all contacts ; default (no args) = 10 contacts
NUM="$1"
if [[ "${NUM}" -lt 1 ]] ; then NUM=10 ; fi

## our selector fields - if data is present then export via Awk
# 32. "Business Phone"         
# 38. "Home Phone"             
# 41. "Mobile Phone"           
# 59. "E-mail Address" 

## exclude 'header' line
EXCLUDE="suffix|title|address|web page|check "

## file names
IN_FILE=outlook-contacts-pruned-2012.CSV
OUT_FILE=New.contacts.vcf

## using Awk with conditions:
# only print if data present in selected 'fields'
# group if-condition by enclosing multiple
# print statements within curly braces
# Yes, you could use ONE awk print statement
# but the code below is MUCH easier to read/change...

## send desired entries to Awk for 'selection' 
## and 'conversion' to vCard format
cat ${IN_FILE} | head -${NUM} | \
   egrep -vi -e "${EXCLUDE}" | \
   awk -F ',' '{if($32 != "" || $38!= "" || $41 != "" || $59 != "") {
      printf "BEGIN:VCARD\nVERSION:2.1\n" ; ## Required Header
      printf "N:%s;%s\n", $2, $4    ; ## Required
      printf "FN:%s %s\n", $2, $4   ;
      printf "ORG:%s\n", $6         ;
      printf "TEL;WORK:%s\n", $32   ;
      printf "TEL;HOME:%s\n", $38   ;
      printf "TEL;MOBILE:%s\n", $41 ;
      printf "EMAIL:%s\n", $59      ;
      printf "URL:%s\n", $95        ;
      printf "END:VCARD\n\n"}         ## Required footer
    }' | \
    sed -e 's/"//g' -e 's/: /:/g' -e 's/^M//g' -e 's/:;/:/g' > ${OUT_FILE}
## last line above 'sed' - remove double quotes (")
## strip 'control codes' - may not be needed

## add a CR/LF to the output - needed for DOS/Windows viewing
## may NOT be needed for importing...
cat ${OUT_FILE} | \
         grep -i "[a-z]" | \
         awk '{printf "%s\r\n", $0}' > mod.${OUT_FILE}
echo "###########"
ls -ltr *.vcf

You can easily modify the Bash script above to include additional ‘data fields’ for inclusion in your vCard output  – just add appropriate ‘printf’ lines within the Awk conversion section.  As always, your output should vary, at least a little bit.  🙂

Topics: Computer Technology, Mobile Web, Unix-Linux-Os, Web Problem Solving, Web Technologies | Comments Off on Outlook 2002 Contacts CSV to vCard via Bash & Awk

Comments are closed.


________________________________________________
YOUR GeoIP Data | Ip: 73.21.121.1
Continent: NA | Country Code: US | Country Name: United States
Region: | State/Region Name: | City:
(US only) Area Code: 0 | Postal code/Zip:
Latitude: 38.000000 | Longitude: -97.000000
Note - if using a mobile device your physical location may NOT be accurate...
________________________________________________

Georgia-USA.Com - Web Hosting for Business
____________________________________