Last month I had the great pleasure of presenting the Excel to CI utility at the Collaborate Conference in Denver. The utility is nothing more than a application engine that reads xml spreadsheet 2003 into a user defined staging record and maps that data into any CI you specify. What sepeareates the utility from delivered Excel to CI is the ability to schedule the load, call CI methods while processing the transaction and controlling the order in which the data is processed into the CI.
Below is an example template that we used to adjust tax balances in 2012. I like this example in particular because the component requires you to save the data in mid processing and then continue with the corrections. To my knowledge Excel to CI would not handle this type of requirement.
The template also shows how you can hard code a value into the template, by using the equals and then typing the value you want to pass to the field in the CI.
Below is the run control for the utility, it requires the user to specify the file they want to load and the Template Definition they want to use to parse and process the file. Please note, that if the template has EMPLID mapped to a field in the CI, then the attached file must have a column with EMPLID in the first row. All fields used by the template are required on the file. And the first row of the spreadsheet is used to determine where the data exists. This means if Column A Row 1 is EMPLID, then the utility is expecting all the data in Column A to be EMPLID's. Also, don't attempt to hide data from the utility, it will find it and process all data that exists on the spreadsheet. It will also evaluate every spreadsheet within the workbook, so if you only want to process the data in Sheet 1, then only upload a workbook with Sheet 1 in it.
All Errors are returned in an xml spreadsheet 2003, that way you can easily reprocess the errors.
Down Load the Project
Seems to be interesting... I would like to download it and check it out and then will provide my comments...
ReplyDeleteThanks Deepak, please download the utility and let me know what you think. Personally, I have not written any CI code in years, I use the utility to build templates for other application engines and even Integration Broker message handlers. Most of the logic is stored in Funclibs and can be accessed from any peoplecode event.
ReplyDeleteHi Kevin.. its long time though...
DeleteWe have downloaded this project and used it. For single CI's its working fine.
Could you please guide us if we have to use multiple CI's to complete a transaction.
For example - personal data ci to generate employee id and then pass this employee id to job data ci to create job data row.. so that the hiring process is completed. This is just an example, we have to achieve the same in Finance application where 3 ci's are involved.
The utility is designed to only call one ci, but I have called the main function from message peoplecode or application engine peoplecode. What I am saying is you can use the main function of the utility to call any number of CI's. We have an acquisition process that we use to loads data into a temp record using file layouts and then call the process stage function in the utility to create the person and the job data.
DeleteKevin - I am getting an error - No Parse Cell - when running the load. Any suggestions?
ReplyDelete@Nicholas
ReplyDeleteMake sure your header columns are on Row 1 of your spreadsheet.
Kevin - Thanks! My problem was that I did not delete the second and third sheet from the workbook. Which is a warning you gave us. So , my bad! Worked great. Thanks! Our team is hoping to use this quite a bit.
ReplyDeletePlease provide me link to download, the link on this page is not working.
ReplyDeleteYour company is blocking the content, try downloading it at home or ask you data security to get the project for you.
DeleteKevin - I am trying to use this Excel to CI to load ACA data into CI_ACA_ELEIGIBILITY. I am getting an error - More than one data record in scroll -- make fields from non-primary record related display: DERIVED_BEN_LBL.LBL_ACA_TEXT. (15,22)
ReplyDeleteI do not get an error when I use the Component Test Tool in App Designer to add a row of data. Can you offer any insights? Also - have you made any changes to the original function that we can use.
This is a peculiar error, open up the component in the IDE and try to validate the component.
DeleteThis comment has been removed by a blog administrator.
ReplyDelete