Creating CSV Files
While data can be manually entered into the system, the most efficient method for uploading the data is to use one of the CSV data templates already available for all appropriate business objects.
The first step is to create a .csv file (comma delimited) from the business object of the portal being loaded.
The following is an example using Organization (W1-Organization):
-
Navigate to Admin, select Location, select Organization, and then select Search.
-
Search for and select a record.
-
Expand the Record Information section and click the "Organization" Business Object link to navigate to the Business Object portal.
-
Click the View Schema link on the Main tab and then copy the elements from the business object to a CSV file to create your data.
|
1 |
Sequence |
Primary Key |
Field 01 |
Field 02 |
Field 03 |
|---|---|---|---|---|---|
|
2 |
1 |
Organization |
Organization Type |
Disposition |
description |
|
3 |
2 |
nodeId |
nodeType |
nodeDisposition |
description |
|
4 |
3 |
Group |
W1AC |
Group 1 |
|
|
5 |
4 |
Group |
W1AC |
Group 2 |
Notes:
-
Columns in the top row match the elements in the list of the request business object (header).
-
The row with the lowest sequence number (1) is the description of xpaths to help users read the file and is ignored by the processing.
-
The business object processing treats the row with the second lowest sequence number (2) not as data, but as the xpath to move data to the target business object, in this case, W1-Organization.
-
Note that the configuration of some business object mean that this cell may start with a “+” sign. Also note that the file always treats this cell as a formula and adds an “=” sign to the front of the data string, as indicated by “#NAME?” (For example: =+nodeId/nodeType). You must reformat the cell to “Text” and remove the “=” sign.
-
-
Rows with sequence number three (3) and up are treated as data.
-
For objects where the primary key is a system generated ID (such as the case with Organization), the Primary Key column cells blank.
Note: When adding records that reference look up values use the lookup field value, not the description. For instance, the Disposition field references the NODE_DPOS_FLG lookup, for which the "Active" field value is "W1AC". For fields that reference other objects, use the code, not the description.
Other Formatting Issues
The following information addresses known formatting issues in working with the CSV data template:
-
Dates need to be formatted as yyyy-mm-dd (2020-10-27).
-
Date and Time should be formatted as yyyy-mm-dd-hh.mm.ss.
-
Columns based on lookup values that have leading zeroes will need to match the values in the CSV file.
-
A link needs to be created when you want to include the “parent”, which has a system generated ID, in the same file with the “child”. For example, if you want to populate the Parent Organization’s ID in the same file, you would need to insert a link (for example, @3, which is the @ symbol plus the Sequence #) in the row where the system generated ID would reside.
-
If multiple rows contain the same Primary Key, only the last row is loaded. All values for the Primary Key need to be on the same row.