Expense Report CSV Examples
When importing expense reports, you can use multiple-file or single-file upload techniques.
Multiple Files CSV Example
With a multiple-file import, you use one file for the expense report body fields and another file for the Expenses sublist fields. The file holding the body data must include, at a minimum, a value in the Employee field. An External ID is also preferred. For example:
|
External ID |
Employee |
|---|---|
|
101 |
Smith, John |
|
102 |
Brown, Jane |
|
103 |
Martin, Ann |
Each row in the second file must include values for the Category field and, typically, for the Amount field. (If the body data for the record has Use Multiple Currencies set to No, which is the default, then Amount is required.)
Each sublist record must also include a unique identifier that links the sublist record to an entry in the main file. The following example uses the External ID field for this purpose.
|
External ID |
Amount |
Category |
|---|---|---|
|
101 |
25 |
Meals |
|
101 |
756 |
Transportation |
|
102 |
12.50 |
Meals |
|
102 |
389 |
Lodging |
|
103 |
488.50 |
Training |
Multi-Files Example Using Multiple Currencies
In the following example, two import files are used to import expense reports that contain expenses in multiple currencies. In this case, your main import file might look as follows.
|
External ID |
Date |
Employee |
Use Multiple Currencies |
|---|---|---|---|
|
321 |
8/14/2017 |
Agneta Simpson |
Yes |
The linked file for the Expenses sublist might look as follows. When you map these fields to the fields of the expenses sublist, map the external ID to the Line ID field, and map Line Reference Number to Ref No.
|
External ID |
Line Reference Number |
Category |
Amount |
Currency |
Date |
Tax Code |
|---|---|---|---|---|---|---|
|
321 |
1 |
Entertainment |
450 |
CA CAD |
8/14/2017 |
CA-Zero |
|
321 |
2 |
Travel |
300 |
US USD |
8/14/2017 |
CA-Zero |
Single File CSV Example
With a single-file import, all data for each expense report record must exist on a single row. For this reason, if the sublist data for any expense report record includes more than one expense, you must create a discrete set of columns for each expense. For example, the following sample would create three expense report records. The first two records would each have two expenses and the third would have only one.
This file is an example. If you try to import a file identical to this one, the import fails. There are required fields missing and some fields, although not required for the import assistant, are required for the expense report, based on the value of other fields. For more details, see Expense Report Body Fields and Expense Report Sublist Fields.
|
External ID |
Employee |
Expense Report Expenses 1: Amount |
Expense Report Expenses 1: Category |
Expense Report Expenses 2: Amount |
Expense Report Expenses 2: Category |
|---|---|---|---|---|---|
|
101 |
Smith, John |
25 |
Meals |
756 |
Transportation |
|
102 |
Brown, Jane |
12.50 |
Meals |
389 |
Lodging |
|
103 |
Martin, Ann |
488.50 |
Training |
— |
— |
When naming your columns, you should use the same naming conventions used by the Import Assistant, so that your columns automatically map. For this to happen, each sublist column should have prefixes that begin with “Expense List Expenses” — for example “Expense List Expenses 1:”, “Expense List Expenses 2:”, and so on).