Wednesday, September 3, 2008

bad ass upload script

One of our vague requirements was to build into Siebel some upload functionality. Basically an end user should be able to add a csv/txt attachment and have the contents of the file associated to a correspondence record as child records.

To build this, I started with the vanilla correspondence attachment buscomp. This gives us the ability to browse to a csv/txt file (or drag and drop) and have it associated to the Siebel file system. Everything else was custom:

0. add a button to read file and insert contacts
1. check the attachment for valid extension (csv or txt)
2. open the file in memory
3. read line by line
4. parse the line and write to Siebel buscomp fields
5. close the file
6. delete the temp file
7. log any errors
8. attach the log file to the same correspondence record
9. update the attachment record with file processing status

I'll go through each of these high level steps:

1. To check the validity of the file extension a simple function was added to check the attachment buscomp's extension field. If invalid, throw an error and do not proceed.
2. If the file was valid, we passed the attachment id to a business service asynchronously. This business service opens the file and does the processing. I added some nice touches such as multi-select so multiple files could be submitted from the correspondence attachment custom button but here are some key lessons from the development:

a. do not use clib functions b/c there is a known defect related to parsing special chars like accents and umlauts. Instead, use COMCreateObject("Scripting.FileSystemObject") and fso.OpenTextFile(sInputFile, 1)
b. fp.SkipLine() allows you to skip a header row, if your user specifies that one exists
c. fp.ReadLine() allows you take the entire row into memory and use "split" to parse
d. to loop through and write to the child buscomp here is some script:

for(var index = 1; index < sStringArray.length; index++)
var sFieldName = "Field"+" "+index;
if (index < 11)
sStatus = "Completed with Error - Check Log for Details ";
Outputs.SetProperty("sStatus", sStatus);
Clib.fprintf(fpLog, "Contact: " + sStringArray[0] + " has too many values to load. Only the first 10 were loaded. \n");

e. to remove the temp file that is created on siebsrvr use Clib.remove(sInputFile);
f. to add a log file to the attachment buscomp use AttBC.InvokeMethod("CreateFile", sLogFile, "CorrFileName", "N");

No comments: