Dear,
we have a crm system with our customers. this sytem can only export
it’s contents to csv.
So I want to write an import script to import the emailaddress and names
to RT. What is the easiest way and best way to do this? import them to
mysql directly? Use commandline tool? Or is there an example available?
Thanks in advance!
regards,
tommie
Dear,
we have a crm system with our customers. this sytem can only export
it’s contents to csv.So I want to write an import script to import the emailaddress and names
to RT. What is the easiest way and best way to do this? import them to
mysql directly? Use commandline tool? Or is there an example available?
Here’s a perl script that I use to add new users. The source is a CSV
file with the following columns:
Comment - put a ‘#’ in here to skip this row
Last Name
First Name
Email Address
City
State
Country
Group Membership - I think I have an ‘x’ in the column if the user is a
member of that group
The script started out small and grew a little from there. Add whatever
other fields make sense for you, take out what doesn’t, and replace the
groups to your liking. If you know Perl, you should be able to figure
out what it’s doing. If you don’t know Perl, you should find someone
who does to help you out (seriously, your life will be much, much easier
if you know Perl).
Use the script at your own risk…
#!/usr/bin/perl -w
#***** some pragmas *****
use strict;
use 5.008;
use lib “/opt/rt3/lib”;
#***** include some files *****
use Getopt::Long;
use RT;
use RT::Interface::CLI qw(CleanEnv GetCurrentUser);
use RT::Group;
use RT::User;
use RT::User_Overlay;
#***** constants *****
our($EMAIL_DOMAIN) = “oracle.com”;
$RT::MinimumPasswordLength ||= 6;
our($MINLEN) = ($RT::MinimumPasswordLength > 6 ?
$RT::MinimumPasswordLength : 6);
our($MAXLEN) = ($RT::MinimumPasswordLength > 8 ?
$RT::MinimumPasswordLength : 8);
our($GUSERS) = ‘Users’;
our($GDEVMGR) = ‘DevMgr’;
our($GEXECS) = ‘Execs’;
our($GSUPPORT) = ‘Support’;
our($GISR) = ‘ISR’;
our($GENGINF) = ‘EngInf’;
our($GEIMGR) = ‘EIMgr’;
our($GROUPNAMES) = [$GUSERS, $GDEVMGR, $GEXECS, $GSUPPORT, $GISR,
$GENGINF, $GEIMGR];
#***** global vars *****
our($Groups) = {};
#**** parse command-line args *****
my($opts) = {};
GetOptions($opts, “skipadd”, “resetpasswd”, “user=s”) || die qq([ERROR]
Invalid argument(s)\n);
#***** setup RT environment *****
CleanEnv();
RT::LoadConfig();
RT::Init();
my($user) = GetCurrentUser();
die qq([ERROR] No RT user found.\n) unless $user->Id;
#***** load all of the groups *****
foreach my $groupname (@$GROUPNAMES) {
my($group) = RT::Group->new($RT::SystemUser);
$group->LoadUserDefinedGroup($groupname);
die qq([ERROR] Cannot load group “$groupname”\n) unless $group->id;
$Groups->{$groupname} = $group;
}
#***** get ready to parse CSV file *****
my($fname) = shift;
open(IN, “<$fname”) || die qq([ERROR] Cannot open “$fname” for input -
$!\n);
while () {
chomp;
#***** skip blanks and comments *****
next if m{^\s*$};
next if m{^\s*#};
#***** parse the line *****
my($cols) = [split(/,/)];
map($_ ||= "", @$cols);
map(s{^\s+}{}, @$cols);
map(s{\s+$}{}, @$cols);
my($coment, $lastname, $firstname, $email, $city, $state, $country,
$gusers, $gdevmgr, $gexecs, $gsupport, $gisr, $genginf, $geimgr) =
@$cols;
next if (($opts->{'user'}) && ($email ne $opts->{'user'}));
if (! $email) {
print qq([WARNING] User "$firstname $lastname" ($.) has no email
address – skipping\n);
next;
}
#***** massage it some *****
my($realname) = "$firstname $lastname";
my($username) = $email;
my($fullemail) = "$email\@$EMAIL_DOMAIN";
#***** get new user object *****
my $user = RT::User->new($RT::SystemUser);
#***** generate a password *****
my($password) = $user->GenerateRandomPassword($MINLEN, $MAXLEN) ;
#***** load/create the user *****
#----- try to load user (first by email, then by name) -----
my ($id, $msg) = $user->LoadByEmail($fullemail);
if (! $user->id) {
($id, $msg) = $user->Load($username);
}
if ($opts->{'skipadd'}) {
#----- we expected the user to be there already, so it's an error it
they are not -----
die qq([ERROR] Could not load user “$username” – $msg\n) unless
$user->id;
#----- only reset password if asked -----
$user->SetPassword($password) if ($opts->{'resetpasswd'});
}
else {
if ($user->id) {
print “Modifying: $username…\n”;
#----- reset password, realname and such -----
$user->SetName($username);
$user->SetPassword($password);
$user->SetRealName($realname);
$user->SetEmailAddress($fullemail);
$user->SetPrivileged(!0);
$user->SetLang(‘en’);
$user->SetCity($city) if $city;
$user->SetState($state) if $state;
$user->SetCountry($country) if $country;
}
else {
print “Adding: $username…\n”;
my($args) = {
Name => $username,
Password => $password,
RealName => $realname,
EmailAddress => $fullemail,
Privileged => !0,
Lang => ‘en’,
};
$args->{'City'} = $city if $city;
$args->{'State'} = $state if $state;
$args->{'Country'} = $country if $country;
my ($id, $msg) = $user->Create(%$args);
die qq([ERROR] Could not create user "$username" -- $msg\n) unless
$user->id;
}
}
#***** add to the appropriate group *****
my($groupname) = $GUSERS;
$groupname = $GDEVMGR if $gdevmgr;
$groupname = $GEXECS if $gexecs;
$groupname = $GSUPPORT if $gsupport;
$groupname = $GISR if $gisr;
$groupname = $GENGINF if $genginf;
$groupname = $GEIMGR if $geimgr;
my($status, $errmsg) = $Groups->{$groupname}->AddMember($user->id);
die qq([ERROR] Cannot add user ") . $user->id . qq(" to group
“$groupname” - $errmsg\n) unless $status;
#***** send out email *****
my $template = RT::Template->new($RT::SystemUser);
$template->LoadGlobalTemplate('ORA_NewAccountCreated');
unless ( $template->Id ) {
$RT::Logger->crit(qq(Unable to load template
“ORA_NewAccountCreated”));
die qq([ERROR] Unable to load template “ORA_NewAccountCreated”\n);
}
my($result, $message) = $template->Parse(Argument => {UID =>
$username, PWD => $password});
if (! $result) {
$RT::Logger->warning("Template object failed to parse - $message");
die("[ERROR] Template object failed to parse - $message\n");
}
my $MIMEObj = $template->MIMEObj;
$MIMEObj->head->set('To', $user->EmailAddress);
$MIMEObj->head->set('From', $RT::CorrespondAddress);
if ( $RT::MailCommand eq 'sendmailpipe' ) {
eval {
open( MAIL, "|$RT::SendmailPath $RT::SendmailArguments" )
|| die $!;
print MAIL $MIMEObj->as_string;
close(MAIL);
};
if ($@) {
$RT::Logger->crit(“Could not welcome message. -” . $@ );
die(“[ERROR] Could not welcome message. -” . $@ );
}
}
else {
my @mailer_args = ($RT::MailCommand);
local $ENV{MAILADDRESS};
if ( $RT::MailCommand eq 'sendmail' ) {
push @mailer_args, split(/\s+/, $RT::SendmailArguments);
}
elsif ( $RT::MailCommand eq 'smtp' ) {
#----- silence warnings on the following three vars -----
$RT::SMTPFrom ||= ""; $RT::SMTPServer ||= ""; $RT::SMTPDebug ||= "";
$ENV{MAILADDRESS} = $RT::SMTPFrom ||
$MIMEObj->head->get(‘From’);
push @mailer_args, ( Server => $RT::SMTPServer );
push @mailer_args, ( Debug => $RT::SMTPDebug );
}
else {
push @mailer_args, $RT::MailParams;
}
unless ( $MIMEObj->send(@mailer_args) ) {
$RT::Logger->crit("Could not send welcome message." );
die "[ERROR] Could not send welcome message.\n";
}
}
}
#***** EOF ***** EOF ***** EOF ***** EOF ***** EOF ***************
Regards,
joe
Joe Casadonte
joe.casadonte@oracle.com
========== ==========
== The statements and opinions expressed here are my own and do not ==
== necessarily represent those of Oracle Corporation. ==
========== ==========