EXCEL To CI

Follow the following steps to implement the Excel To CI Utility

1) Grant access to the WEBLIB_SOAPTOCI iScript

Grant the above mentioned iscript in one of the permission lists of the user who running it.

2)  Enable Developer Menu in Microsoft Excel.

• ·         Go to Excel Options. Click on the checkbox of Show Developer tab in the Ribbon.

     3) Make sure the macros are enabled (Microsoft Excel 2007).

·         Open  ExcelToCI.xls file which is located in the Excel folder of the PS_HOME.

·         Save As the ExcelToCI.xls Into Excel Macro-Enabled Workbook (xlsm format).

·         The workbook name must have .xslm extension .

4) Now open the Excel sheet , Need to supply the connection information and CI name associated with the component in further tabs

Open the connect information tab from the Excel sheet.

Most of the time above mentioned details you can get from the URL of the PIA of the any random page , some need to collect from the DBA.

Web Server Machine Name - Name of the web server connected to 

Protocol - HTTP/HTTPS

HTTP Port - Port Number Web server is using 

Portal - Portal name using , Default is EMPLOYEE

PeopleSoft Site Name - PeopleSoft Site Name that was given at the time of installation of PIA ,Default one is ps

Node - PeoplSoft node name , Default one is PT_LOCAL

Language Code -ENG

Chunking Factor -No of rows of data to be transmitted to DB at one time , Default one is 40

Error Threshold - It can be set if we want to stop the import into DB after certain no of errors reached 

Action - This will be automatically set when the CI is set

The types of actions available are based on the structure of the component interface. The actions are:

·      Create.

This option is available if the component interface has create keys. Use this mode when new keys are being added at level 0.

·      Update.

This option is available if the component interface does not have create keys. Use this mode if you are adding new children to an existing parent.

·      UpdateData.

Use this option to update specific non-key values that already exist. The system uses the keys to locate the row, and when a match is found, the row is updated with new data. If a key match is not found by the system, it displays an error message indicating which collection was missing a key match.

When using the UpdateData action, you must provide all keys for the collection for the system to modify the data.

Most of the time you can fill the data by the PIA login URL 

i.e   http://hrms90v:9180/psp/HRMS90/?cmd=login

                 Web Server :  hrms90v
                 Protocol : http
                 Port : 9180
                 PeopleSoft Site Name : HRMS90

5)Connect to Database to create template and submit data
  Now go to Template tab and supply the Username ,password and CI name

Click on Add-Ins From menu and select New Template

Prompt will appear 

      Fill the details of ID, PWD of Application Designer that you created CI and  CI name, Which you want to Import the data through your component .

     Once submitting this utility will import the structure of the CI in the following way into Template tab.

   Collection : Scroll Data of the underlying component

   Property : Fields on the page    

   Record Type : Indicates the number related to scroll 

     The level 0 scroll record is always represented by 000. Level 1 scroll records appear with numbers that start with 100 and always have 00 as the last two numbers.

     Level 2 scrolls are identified by numbers that start with the identifier of their level 1 parent and end with a 0.

     Level 3 scrolls are identified by the first number from the level 1 parent, the second number from its level 2 parent, and then the third number from its own position in the list.

     The numbers for each scroll level are incremented based on the number of records that exist at that level. For example, level 0 would be 000, level 1 would be 100, level 2 would be 110, and so on.

    Field Type :Data Type of the field

    Field Length : Length of the field

    Key : Indicates the key structure of the field , This information comes from the record structure   it self

    Sequence : The sequence number represents the property order in the template

     Now make the fields into Gray by selecting them and click on Select Input Cell from menu , Here gray indicates that those are fields that you are including in your Template , Template looks like in the following way.

     Now Click on New Data Input from Menu to make the final template which accepts data from user.

    New Data Input :

Builds a new data input sheet based upon the selected input cells. When you build a new data input sheet, the system prompts you as to whether you want to overwrite the existing sheet. If you select Yes, a new data input sheet is created, overwriting the former one

Once after clicking it , It prompts you in the following way

Clc

on Yes , now we will enter into Data Input tab

6) Sample data is presented in the following way

Once the data entry is done proceed with clicking Stage Data for Submission button from the menu

7) Finally system will automatically enters into final tab Staging & Submission tab

 8) Finally click on Submit Data , It prompts for ID,Pwd .Supply the values & click on OK . It automatically populates the data into DB.