LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Blogs > catkin
User Name
Password

Notices

Rate this Entry

OTRS: importing an Outlook contacts format CSV to the Address Book

Posted 06-26-2011 at 10:24 AM by catkin

Hello

Here's a script and associated SQL to import data from an Outlook contacts format CSV file into OTRS' Address Book.

They were developed and tested using:
  • An Outlook contacts format CSV file generated by export from Google Mail.
  • gnu awk 3.1.8 on Slackware64 13.1.
  • OTRS 3.0.8 running on Debian Squeeze 64 bit using mysqld and mysql 5.1.49.
The awk script prints usage if run with the -h option.

A sample usage:
Code:
bin/Outlook_CSV_to_OTRS_CSV.awk -u1000 \
    -i "Test inputs/contacts3.csv" \
    -l /tmp/Outlook_CSV_to_OTRS_CSV.log \
    -o /tmp/contacts3.csv
The attached file has had .txt added to its name to suit LQ's upload file name restrictions; it can sanely be removed after download. Alternatively, especially for those without an LQ registration, it can be downloaded from http://pastebin.com/1pK7i9Uj.

The script does all it practically can to sanitise the contacts data and where it fudges the data to meet OTRS "not null" database column requirements it annotates the value with a "fudged" comment to facilitate manual clean up in OTRS after loading. All the same it's probably worth running the script and checking the log to identify data issues and fixing them before running the script for the last time. If doing so, it's a lot easier to load the Outlook contacts format CSV file into a spreadsheet editor rather than use a text editor.

To load the output CSV into OTRS (only the '/tmp/contacts3.csv' should need changing):
Code:
load data infile '/tmp/contacts3.csv'
into table customer_user
fields terminated by ',' optionally enclosed by '"' escaped by "\\"
lines terminated by '\n' 
ignore 1 lines
(
change_by, 
@change_t, 
city, 
country, 
create_by, 
@create_t, 
customer_id, 
email, 
fax, 
first_name, 
last_name, 
login, 
phone, 
mobile, 
street, 
title, 
valid_id, 
zip 
)
set 
change_time = STR_TO_DATE(@change_t, '%Y-%m-%d %H:%i:%s'), 
create_time = STR_TO_DATE(@create_t, '%Y-%m-%d %H:%i:%s')
;
In case of any feedback, problems etc. please comment in this blog entry.

Best

Charles
Attached Files
File Type: txt Outlook_CSV_to_OTRS_CSV.awk.txt (30.8 KB, 14 views)
Posted in Uncategorized
Views 2857 Comments 0
« Prev     Main     Next »
Total Comments 0

Comments

 

  



All times are GMT -5. The time now is 06:47 PM.

Main Menu
Advertisement

My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration