|
The scripts mentioned here below, where used to
import data into the tool, with the idea to auto populated the
system. |
|
The data was presented in an Excel form, which has
be entered into an Access Database. From this file the following
script is used for preparing a file by which the data can be
imported into USVD.
Perl-Script
# Created by Daniel Paessens (HP
EUWS)
# Email: daniel.paessens@hp.be
#
# Purpose: Import new users into database
# Based on
Excelsheet
#
# Version:
0.3
# Filename:
new_users.pl
# File location: c:\DataScripts
# Creation Date: 09/12/2005
# Modified:
09/12/2005
#
use DBI;
$DSN = 'driver=Microsoft Access Driver
(*.mdb);dbq=C:\\DataScript\\data.mdb';
$dba = DBI->connect("dbi:ODBC:$DSN",",") || die("Can't connect
to DB:$DBI::errstr");
my $newusers = "C:\\DataScript\\newusers.txt";
open(NEWUSERS, ">$newusers") || die("Can't open newusers.txt
$!");
print NEWUSERS "Table Contact\n \tc_last_name c_first_name c_dept
c_position";
print NEWUSERS " c_admin_org_id c_org_id c_l_id, c_email_addr
c_public_phone";
print NEWUSERS " c_voice_phone c_fax_phone c_userid zLang\n";
#
# Prepare Query
#
$sth = $dba->prepare('SELECT Users.* FROM Users')
|| die("Can't prepare SQL
statement $DBI::errstr");
#
# Run Query
#
$sth->execute || die("Can't execute SQL statement
$DBI::errstr");
my($LastName, $FirstName, $dept, $postion, $adminorg, $org, $l,
$email, $pulic, $voice, $fax, $userid, $lang );
$sth->bind_columns(\$LastName, \$FirstName, \$dept, \$postion,
\$adminorg, ,\$org, \$l, \$email, \$pulic, \$voice, \$fax,
\$userid, \$lang);
while ($sth->fetch)
{
print NEWUSERS "{\"$LastName\",
\"$FirstName\", \"$dept\", \"$postion\"";
print
NEWUSERS ", \"$adminorg\", \"$org\", \"$l\", \"$email\",
\"$pulic\", \"$voice\"";
print NEWUSERS ", \"$fax\", \"$userid\",
\"$lang\"}\n";
};
Input
file
The script is resulting in the following format
(required by system):
Table
Contact
c_last_name
c_first_name c_dept c_position c_admin_org_id c_org_id c_l_id,
c_email_addr c_public_phone c_voice_phone c_fax_phone c_userid
zLang
{"LastName1", "FirstName",
"HUB", "", "400528", "1310", "400038", "FirsName.LastName1@x.x",
"0032 16 XXXXXX", "", "0032 16 XXXXXX", "FAA532",
"FR"}
{"LastName2", "FirstName",
"Facilities", "", "400524", "400524", "400038",
"FirstName.LastName2@x.x", "0032 16 XXXXXX", "", "0032 16 XXXXXX",
"AFD503", "NL"}
|
|
An attempt was done by providing a CSV file with
an overview of all outstanding calls inside the previous system.
The quality of this file was not conform the standards for
CSV-files. Nevertheless the following script where able to import
the majority without problems.
Initiating
call
# Written by Daniel Paessens (HP -
TSG - EUWS)
# Email: daniel.paessens@hp.com
$file = 'over.csv';
$out = 'out.csv';
$in = 'input.txt';
$desc = 'desc.txt';
open (F, "$file") || die ("Could not open $file!");
open (O, ">$out") || die ("Could not open $out!");
while (<F>)
{
if ($_ =~ m/;\n/)
{
print O $_
}
else {
$_ =~ s/\n/\\\\0012/;
print O "$_";
}
};
close (F);
close (O);
open (O, "$out") || die ("Could not open $out!");
open (I, ">$in") || die ("Could not open $out!");
open (D, ">$desc") || die ("Could not open $out!");
print I "TABLE Call_Req\n";
$ref_num = 530;
print I "\tassignee customer created_via log_agent status type
summary ";
print I "active_flag priority ref_num open_date description
ext_ref\n";
while (<O>) {
($f1, $f2, $f3, $f4, $f5, $f6, $f7, $f8, $f9,
$f10, $f11) = split ';', $_;
$f7=~s/"/*/;
%priority=("1-High"=>"5",
"2-Medium"=>"3",
"3-Low"=>"1",
""=>"1");
$pri=$priority{$f2};
print I "{\"400011\", \"400011\", \"3553\",
\"400011\", \"OP\", \"I\", ";
print I "\"$f7\", \"1\", \"$pri\", \"$ref_num\",
\"01/28/2006 23:40:59\"";
print I ",
\"$f3\\\\0012$f4\\\\0012$f5\\\\";
print I
"0012$f6\\\\0012$f8\\\\0012$f9\",\"$f1\"}\n";
print D "$f1;$f10;$f11;\n";
print "$f1\n";
$ref_num=$ref_num++;
}
This script resulted into 2 files. One for calls
ready to be imported. Second file is necessary for further
treatment. In this case items as description and so on being
entered as first log comment. This was necessary due the fact that
the presented files where not respecting the line length in an
ASCII file (1024 characters).
Description into
first log comment
This is performed by the following
script:
# Written by Daniel Paessens (HP -
TSG - EUWS)
# Email: daniel.paessens@hp.com
use DBI;
$dbh = DBI->connect('dbi:ODBC:AHD', 'sa', 'Passw0rd');
#
# Prepare Query
#
$sth = $dbh->prepare('SELECT AHD.call_req.persid
FROM AHD.call_req WHERE (((AHD.call_req.ext_ref)=?))')
|| die("Can't prepare SQL
statement $DBI::errstr");
$file = 'over2.csv';
$out = 'out2.csv';
$in = 'input2.txt';
open (F, "$file") || die ("Could not open $file!");
open (O, ">$out") || die ("Could not open $out!");
while (<F>)
{
if ($_ =~ m/;\n/)
{
print O $_
}
else {
$_ =~ s/\n/ /;
print O "$_";
}
};
close (F);
close (O);
open (O, "$out") || die ("Could not open $out!");
open (I, ">$in") || die ("Could not open $out!");
print I "TABLE Act_Log\n";
print I "\tanalyst call_req_id description internal last_mod_dt
system_time type\n";
while (<O>) {
($f1, $f2, $f3, $f4, $f5) = split ';', $_;
$f4=~s/\n\r\f/\\0012/;
$f5=~s/"/*/;
$f5=~s/\n\r\f/\\0012/;
$f4=~s/"/*/;
$sth->execute($f1) || die("Can't execute SQL
statement $DBI::errstr");
my $persid =$sth->fetchrow_array();
if ($persid){
print I "{\"400011\", \"$persid\", \"$f4\",
\"0\", \"01/28/2006 23:59:59\", \"01/28/2006 23:59:59\", ";
print I "\"LOG\"}\n";
print "$f1\n";
if ($f5){
print I "{\"400011\", \"$persid\", \"$f5\", \"0\", \"01/28/2006
23:59:59\", \"01/28/2006 23:59:59\", ";
print I "\"LOG\"}\n";
}
}
}
These results into the following file, ready to be
imported:
TABLE
Act_Log
analyst
call_req_id description internal last_mod_dt system_time
type
{"400011", "cr:400021", "Mail
OF johan", "0", "12/04/2005 15:30:00", "01/28/2006 23:59:59",
"LOG"}
{"400011", "cr:400021",
"Beste, Zou ik nieuws kunnen hebben rond Call 16578 aub. Dank bij
voorbaat, Johan X HUB Operations Settlement-Allocation GAS", "0",
"12/04/2005 15:30:00", "01/28/2006 23:59:59",
"LOG"} |
|
Finally, this script is used to import the
activities on the imported calls.
# Written by Daniel Paessens (HP -
TSG - EUWS)
# Email: daniel.paessens@hp.com
use DBI;
$dbh = DBI->connect('dbi:ODBC:AHD', 'sa', 'Passw0rd');
#
# Prepare Query
#
$sth = $dbh->prepare('SELECT AHD.call_req.persid
FROM AHD.call_req WHERE (((AHD.call_req.ext_ref)=?))')
|| die("Can't prepare SQL
statement $DBI::errstr");
$out = 'desc.txt';
$in = 'input3.txt';
open (O, "$out") || die ("Could not open $out!");
open (I, ">$in") || die ("Could not open $out!");
print I "TABLE Act_Log\n";
print I "\tanalyst call_req_id description internal last_mod_dt
system_time type\n";
while (<O>) {
($f1, $f2, $f3) = split ';', $_;
$f3=~s/"/*/;
$f2=~s/"/*/;
$sth->execute($f1) || die("Can't execute SQL
statement $DBI::errstr");
my $persid =$sth->fetchrow_array();
if ($persid){
print I "{\"400011\", \"$persid\", \"$f2\",
\"0\", \"01/28/2006 23:59:59\", \"01/29/2006 01:59:59\", ";
print I "\"LOG\"}\n";
print "$f1\n";
if ($f3){
print I "{\"400011\", \"$persid\", \"$f3\", \"0\", \"01/28/2006
23:59:59\", \"01/29/2006 01:59:59\", ";
print I "\"LOG\"}\n";
}
}
}
The file created as result of this script, is
imported into USVD. The structure is similar as the one mentioned
in previous point. |
|
|
|
|
|
1. Software installation
