Ok....so Carey helped me with this offline...and it took a few days to get
right...but here it is
ARS 6.3
Windows 2003
Situation,
I have users wanting to do a two step batch upload of data into the DB.
Step 1 is specify a file to import and get it imported. If no errors are
reported on import then process the records into their destination form. To
accomplish this I built two forms, one is a console that has a few buttons
to allow for adding attachments and loading the data, an attachment pool
with two fields in it...one for the batch upload file and the other for the
import log if there are errors and a table field to show the records that
are imported. Here are the steps I take to get the file in.
1. Use button to attach file to form
2. Use button to set a checkbox and create a Remedy record in the console
- I create a record on the console so that I can use filters to perform the
import and such...I know I could use AL's...but I didn't want to in this
case
3. On Create or Set of console record if the checkbox is checked I use
filters to save the attachment to the server in a designated area with the
name $GUID$.csv...$GUID$ being the name of field ID 179
4. Next filter fires off a setfield $PROCESS$ to execute the attached perl
script, I'll go into the details later..but if the import encounters errors
it will return a value of 1 from the setfields.
5. If previous filter caused an int field to be set to 1 I attach the log
file to the console record in the second attachment field and then delete
the log from the server...in the else action (no errors) I just delete the
import log
That's it for the remedy side...the real magic happens in the perl file and
what it does with the arm file. Before you can import of course you must
create your ARM file using Remedy Import GUI tool. Once the ARM is created
with the appropriate mappings be sure to map the parentID field to some
static value...I used ParentGUID as the static value. Now go in with a text
editor and edit your arm file...the first line of the mapping is the
'mapping name', I set that to the same value as my static parent mapping so
that the perl script can replace them with the same search string. I also
modify the ServerName line so that this mapping can be used on the dev and
production machines without mod
ParentGUID
ServerName: <servername>
Mapping: <field ID of relationship field>=ParentGUID
The perl script accepts two parameters...could be modified to accept more if
you didn't want to hard code some of the hard coded values...
Parm 1 - GUID
Parm 2 - Server
I believe that the script is commented well enough to allow you to modify it
to meet your needs. There is obviously more workflow to push the records
into the destination form once validation has occurred...but this is the
method I used (thanks Carey) to import records associated with my parent
record when the csv did not explicitly reference the parent record. Feel
free to ask any questions
-----Original Message-----
From: L. J. Head [mailto:[EMAIL PROTECTED]
Sent: Friday, December 29, 2006 11:23 AM
To: '[email protected]'
Subject: RE: arimportcmd
Thank you Carey...if you could assist a bit more
I like the idea you presented in 1...and I had considered it...but am not
skilled enough personally to create this file. I am saving the csv file to
the server with the name of the GUID of the record in form A. Perl is my
preferred scripting language so if you have expertise in that I would
appreciate your help writing that script.
I assume it would go something like this
Copy template mapping to guid.arm
Edit guid.arm to change the name of it to some 8 digit equivalent of the
guid Save the new guid.arm file Run arimport with appropriate command line
syntax for new arm
Does that sound about right?
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black
Sent: Friday, December 29, 2006 10:56 AM
To: [email protected]
Subject: Re: arimportcmd
L.J.,
RE:
"
Is there something that can be done with the mapping to allow mapping a
field not in the file with a command line argument?
"
Not to my knowledge.
However here are a few ideas....
1) Edit the mapping file before you call arimportcmd. (This could be
scripted so that you call a wrapper script that does this and then calls
arimportcmd too.)
2) Edit the CSV file before import.
3) Do a second import (maybe to a display only form) so you can do one or
more push actions to "map the records that are not yet mapped" to the
"record that had the CSV file".
4) On import(MERGE) have a filter "look up" the record that spawned the
import process. (Assuming that this will be automated and that the import
process is done via a Run Process action.) Note: If you can not do a select
of Form A based on the data in the file then you might need to create a
"queue/log" form that holds a record that says "Form A", Entry
"000000000000001" is waiting for it's CSV children records.
[ The only problem there is if you can get two of these things going at the
same time. ]
HTH.
--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)
Love, then teach
Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
On 12/29/06, L. J. Head <[EMAIL PROTECTED]> wrote:
> **
>
> ARS 6.3
> Windows 2003
>
> I have Form A that contains an attachment field that has a csv
> attached to it I want to import the records into Form B from the csv
> attached to Form A and I want to have the records in Form B associated
> with the record in Form A
>
> I've got all of the procudures completed except how to associate the
> records in Form B with the record in Form A.
>
> Is there something that can be done with the mapping to allow mapping
> a field not in the file with a command line argument?
____________________________________________________________________________
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
$import_loc = 'c:\progra~1\arsyst~1\scripts'; #location of
arimportcmd file
$arm_file = 'OrderBat.arm'; # name of the import map file
$guid = $ARGV[0]; # GUID of the source record
$arm_file_new = $import_loc.'\\temp\\'.$ARGV[0].'.arm'; # name
of the temp/fixed import map file
$csv_file = $import_loc.'\\temp\\'.$ARGV[0].'.csv'; # name
of the CSV file
$log_file = $import_loc.'\\temp\\'.$ARGV[0].'.log'; # name
of the Log file
$servername = $ARGV[1]; # name of the server being
imported to
# open the ARM file
open(ARM,$import_loc.'\\'.$arm_file) || die "Can not open $arm_file\n";
open(ARM_new,'+>'.$arm_file_new) || die "Can not open $arm_file_new\n";
while (<ARM>) {
# fix the ServerName line so that the arm file is multi server capable
s/ServerName: <servername>/ServerName: $servername/g; # sub in the right
server name
# Change name of ARM to GUID and map it to the Parent ID field
s/ParentGUID/$guid/g;
# Replace template filename with actual csv
s/<filename>/$csv_file/g;
# then print out each line to the new file.
print ARM_new;
}
close ARM; # close the ARM file.
close ARM_new; # close the New ARM file.
#run the import
system("$import_loc\\arimportcmd.exe -u <username> -p <password> -x $servername
-a <tcpport> -m $guid -o $csv_file -d $import_loc\\temp -l $log_file");
#after import has been run delete the new arm and csv file but leaving the log
system("del $arm_file_new");
system("del $csv_file");
open(LOG, $log_file) ||die "Can't open $log_file\n"; #Open the Log file to see
if there are errors
while (<LOG>) {
if ($_ =~ m/errors/i) { #look for the work 'errors' anywhere in the file
print '1'; # return 1 as an output to indicate to Remedy that an error
occurred during import
}
}
close LOG; # close the Log file.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
