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 expense codes:
- Navigate to Admin, select E, select Expense Code, and then select Search.
- Expand the Record Information zone and click the Expense Code hyperlink for the business object.
- Click the View Schema hyperlink on the Main tab and then copy the elements from the business object to the CSV file and create your data.
| 1 | Sequence | Primary Key | Field 01 | Field 02 |
|---|---|---|---|---|
|
2 |
1 |
Expense Code |
description |
Cost Category |
|
3 |
2 |
expenseCode |
description |
costCategory |
|
4 |
3 |
WD-Inventory |
200 – Inventory |
WD-Stock |
|
5 |
4 |
WD-LaborPremium |
120–Labor - Premium |
WD-Labor |
- Columns in the top row match the elements in the list of the request BO (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 BO processing treats the row with the second lowest sequence
number (2) not as data, but as the xpath to move data to the target
BO, in this case, W1-ExpenseCode.
- Note that the configuration of some BOs 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: =+costCategory/expenseCode). You must reformat the cell to “Text” and remove the “=” sign.
- Rows with sequence number three and up are treated as data.
Other Formatting Issues
The following information addresses known formatting issues in working with the CSV data template:
- Dates need to be formatted as yyyy-m-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. For example, the TD_PRIORITY_FLG value should be “030”, not “30”.
- 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, you can load Organizations but 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.