9.2 Add Assets Using Excel to CI Utility
Purpose: The purpose of this workbook is to upload data from Excel into PeopleSoft using the Component Interface to execute business logic for each transaction. This source workbook contains both worksheets and Excel VBA code modules. The Worksheets can be copied to other workbooks for distribution without copying the code modules.
Audience: Finance staff.
The PeopleTools Excel to Component Interface utility enables you to add assets from a predefined spreadsheet template. You can use the utility to upload new asset information during a conversion or when adding assets acquired from a reorganization. You can add financial data and physical data. Once the data is uploaded, it is available for the Transaction Loader process to process the asset additions as financial add transactions (FAD).
Note: The amount of data that can be uploaded from a spreadsheet is limited by the spreadsheet utility. Consequently, this method of conversion is inappropriate for large-volume data conversions.
Add Assets Using Excel to Component Interface Utility
The PeopleSoft system provides a template to import and add transactions financial data from the spreadsheet to Asset Management tables with the Excel to Component Interface utility.
The following is an overview of the steps to add assets using import data from an Excel spreadsheet. This assumes that the user will already have the Excel file to follow along with to compare to the screenshots throughout the QRG.
Connection Information Sheet
The information provided on this sheet is required to create a new template or submit data to the database. You will need to specify environment information as well as information in regard to how each transaction should be handled. The Action will be filled in automatically. By default, character fields which are left blank or only contain blanks will not be submitted for update. There is an option to override this setting.
- Enter the database connect information on the template Connect Information tab. You will need:
- PeopleSoft web server name for your specific environment
- HTTP port
- Portal name
- Default local Node
Here you create the template that you are going to use. The template is based upon the structure of a Component Interface on the PeopleSoft database.
- Select the predefined component interface (for this example we use: DEPT_CF) to populate the template on the Template tab. Your value may be different.
- Select the fields that are used to enter data.
- Select the Add-Ins menu at the top of the Excel file. There are a number of choices in this sub-menu. We will concern ourselves with only two:
- New Template: When prompted, enter the name of your PeopleSoft User ID, password, and the Component Interface you wish to use. The connect information previously provided is used to retrieve the PeopleSoft Component Interface properties. The Component Interface structure is displayed graphically.
- New Data Input: This button copies the selected input cells into the Data Input sheet. The Data Input sheet becomes active, and you will be prompted before the structures and data on that sheet are deleted.
- Select New Data Input button to format the Data Input tab with the selected data fields from the Template tab, or the component interface.
Data Input Sheet
Here you enter data values for submission to the PeopleSoft database.
Stage Data for Submission - The data entered on the Data Input sheet is then staged in hierarchical form in preparation for submission to the database.
- Input data for all identified and selected record levels on the Data Input tab.
- Next you will stage the input data to the Staging & Submissions tab by going back to the Add-Ins menu and selecting the Stage Data for Submission button.
- Review the data that is ready for staging.
Once the button is selected, the spreadsheet automatically advances to the Staging & Submission tab.
Staging & Submission Sheet
The last step is to format the data and submit to the database. Once again, we go to the Add-Ins menu to review our choices.
Submit Data: This submits the data to the database. Your PeopleSoft User ID and Password will be required. Each row submitted is marked with the reply from the database which will either be OK, Warning, or Error.
Post Results: This posts the status for each row submitted to the database on the Data Input sheet so that data in a status of error can be corrected and resubmitted.
- Select the Submit Data button.
- A pagelet displays asking for the following PeopleSoft information:
- User ID
- Submit data for error-checking and transmission to the database, if successful.
- Errors are returned to the Staging & Submission Data tab for review.
- Select the Post Results option to return processing errors to the Data Input tab for reworking.
- Submit data to the PeopleSoft Asset Management database as needed to successfully transmit all input data to Asset Management.
- Preview the financial and physical data, using the menu option for importing data through a spreadsheet.
- Run the Transaction Loader process to add assets to the database.
- Process complete.