ExcelToCI Utility in PeopleSoft
ExcelToCI is a PeopleTools utility delivered by PeopleSoft to load the PeopleSoft data to the PeopleSoft system through CI validation from an Excel worksheet. This reduces the developers work to develop the new process to use the CI for loading the information into PeopleSoft environment.
The Component Interface which will be used through the ExcelToCI utility should be tested through the component interface tester in three-tier mode. Testing the component interface using the tester helps to troubleshoot any problems before running the component interface through this utility.
The User who connects through the ExcelToCI Utility to load the data should have full access to the iScript WEBLIB_SOAPTOCI. The Macros should be enabled in the Excel worksheet
A Microsoft Excel spreadsheet has a limitation of 252 columns and 65,000 rows.
The Utility resides in the ‘$PS_HOME’ directory under the folder named ‘excel’ with the filename as ‘ExcelToCI.xls’.
ExcelToCI contains five worksheets which helps us to follow the Step by Step procedures.
The Coversheet provides a brief overview of the process flow and functionality of the tool.
2. Connect Information:
The Connect Information worksheet collects the environment information to connect to the Peoplesoft database and build the template based on the Component Interface provided. The details can be collected from the System Administrator
Template worksheet helps to build the template structure in the Excel based on the Component Interface structure. The Template worksheet contains the ‘Template Actions’ toolbar which helps to build us the new template.
Click the ‘New Template’ to load the Component Interface information in the work sheet. Once Clicked, the login credentials and CI name is collected and after successful validation, the template structure loads in the worksheet.
It is not required to provide all the information in the CI. However all the key values and required field information should be available for the CI loading to be successful. Select all the fields for which the information needs to be loaded and click ‘Select Input Cell’. The selected input cell will be displayed in the different color format
There may be a data load where multiple data rows needs to be loaded at level 1 or level 2 scroll levels. It can be handled by selecting the input cell and using the ‘Insert New Child’ option in the ‘Template Actions’ toolbar. In our example, there may be multiple roles assigned to an employee, Hence a new child row is inserted in the template.
Once you selected all the required data information, select the ‘New Data Input’ option in the ‘Template Actions’ toolbar to load the template structure in the ‘Data Input’ worksheet
Data Input: Enter all the data which needs to be loaded in the ‘Data Input’ worksheet and click the ‘Stage Data for Submission’ option to load the information. When the option is selected the data values are loaded to ‘Staging & Submission’ worksheet in the structured format. The multiple roles will be displayed in the subsequent columns.
Staging & Submission:
Staging & Submission worksheet displays the content in the structured format and provides the option to submit the information to the PeopleSoft environment. The login credentials are validated again and data values are loaded in to the PeopleSoft system. If the data was not successfully loaded in to the PeopleSoft environment the Status column was displayed with error. The data can be loaded back to ‘Data Input’ worksheet by selection ‘Post Results’ option and the data can be corrected.