Guidelines for Using HCM Spreadsheet Data Loader
You can load data to the HCM Data Loader stage tables from either a data (.dat) file or a spreadsheet. Your approach depends largely on how often you load data, the complexity of the data that you're loading, who performs the bulk update.
HCM Data Loader is designed for the integration specialist, having access to HCM Data Loader allows the user to load data for supported objects. HCM Spreadsheet Data Loader is designed with the business user in mind. You can secure the spreadsheets to implement the data security policies of the spreadsheet user. This topic describes some key features of data loading from spreadsheets. This information helps you to identify the best approach for your business case.
Spreadsheet Templates
You must generate the spreadsheet from a spreadsheet template. Some spreadsheet templates are predefined. To create templates, you can either copy a predefined template and edit it or create a template from scratch.
When to Use Spreadsheets
Spreadsheets offer many advantages. For example:
-
Spreadsheets are easy to use.
-
You can create spreadsheet templates and generate spreadsheets tailored to your business case. For example, you can omit attributes, change attribute order, change attribute labels, and add help text. You can also define different templates for creating and maintaining the same business objects.
-
You can enable business users to load data for selected spreadsheet templates.
-
You can save data periodically from a spreadsheet to the stage tables.
-
Errors are reported in the spreadsheet, and you can correct them there.
-
You can mix your use of spreadsheets with use of .dat files. For example, you can load data from a spreadsheet but maintain it using .dat files.
-
You can use spreadsheets to delete data.
-
In specific cases, spreadsheets are the simpler option. For example, to load an element entry for an element with 5 lines from a .dat file, you supply:
-
1 record for the element entry
-
1 record for each name and value
In a spreadsheet, you supply just 1 spreadsheet line where the values are individual columns on the same line.
-
-
You can restrict the job roles that can access a spreadsheet. For example, you can make the spreadsheet to create new hires available only to the Human Resource Specialist.
Spreadsheet Restrictions
When loading data from a spreadsheet:
-
You can't upload data to attributes whose data type is binary large object or character large object, like images and file attachments.
-
HCM Spreadsheet Data Loader doesn't recognize source keys. Therefore, objects that you load from a spreadsheet have default source keys. To maintain such objects using .dat files, you supply user keys or you can retrieve the source key values to use, using the Integration Object User Key Map Extract.
-
You can't create objects or object components, such as Profile Item, that don't supply user keys. However, you can update such objects if you define a list of values on the local surrogate ID attribute to identify the record to update.
-
You can't update the values of the user-key attributes if they identify the record to update. Therefore, you're recommended to define separate spreadsheets for creating and updating objects. In the spreadsheet for updating an object, you can identify the record to update in one of these ways:
-
Include the local surrogate ID attribute, which has a predefined LOV.
-
Include the primary user key attribute, but configure an LOV for the attribute value. With this approach, the user sees a recognizable value in the spreadsheet.
-
Setup Requirements
All users must set up desktop integration for Microsoft Excel to use HCM Spreadsheet Data Loader.
Excel Security Requirements
You don't need to enable macros or change your ActiveX settings for HCM Spreadsheet Data Loader.