You can import Time+Expense items from a Spreadsheet in csv format and optionally automatically create reports and submit them.  You can also send or forward the Time+Expense file via email (e.g. Uber trip reports). Check for import details


Import T+E Items

In the Welcome Tab, click on Spreadsheet / Import icon

  • Select the CSV file to be imported
For time records, you just need the two columns: "T&E Date", Hours

as User/Resource is derived if not part of the import file.

You can use the import also for updating (correcting) existing items as long as they are not approved.


To view your imported records, you may have to adjust the default T&E Date in the Timesheet or Expense tab.


Simple Example:

Resource, Date, Hours
Charlie Chatter, 10/12/2016, 3
Paul Platform, 2016-10-12, 3.1
Sue Standard, 12.10.2016, 3.2

Note that the sequence of the columns does not matter, the header is case insensitive and could also be the column names "resource__c, te_date__c, hours__c"


Preview

Before importing, you can select Import Preview to view and modify the records would be imported. Here, you can also select values for missing or incorrect file input. The Import Details section lists for every row how the values were derived.


Note that the preview is very resource intensive, and if errors occur, you should reduce the size of the import file to 100-200 records.  


Create T+E Reports

You can also create and submit T+E Report(s) automatically if the import is successful.

  • Select "Create T+E Report"
  • optionally select "by Project"
  • optionally select "Submit Report for approval"
  • choose to "Delete records on Report creation failure" - deletes the imported records to allow to fix the issue and re-import.

If successful, you can click on the T+E Report names to view the records.

Note that if you import T+E Items for others, you need to have the r/w sharing access for T+E Item and Report for that user or Modify All access.


Automatic Approval

When you submit the report, you can automatically approve the report, e.g. based on the following criteria:

  • T+E Report: Report Type = I (for import)
  • Match on parts of the file name (T+E Report field Import Source)
  • Some other criteria to prevent accidental approval when "normal" users use this functionality, e.g. Submitter


Hints and Tips

 All reference entities (e.g. project, tax, account) must already exist

  • Import creates new T&E items if the T&E Item column is empty (or not included)
  • If a T&E report is referenced it must have a status of 'New' or 'Not Approved'
  • Add attachments via email or in the T&E items related list after import
  • The file format must be .csv
  • If the record owner is not defined in the file, it will be the user who imported the records. If you import the OwnerId, the Resource is derived and vice versa. It is more efficient to import the Resource.
  • If using Field Labels, they must be enclosed in quotes if they contain spaces. (e.g. "Fin Account")
  • Do not import the x* and d* columns - they are used for timesheet display only
  • The Project column accepts the following:
    • Project Code or Project Name, e.g. myProject
    • Project Code/Name and Project Line Name/WBS separated by colon and a space ": ", e.g. myProject: myLine
  • For Projects, the line status can be Completed (as there may be delays).
  • If an external reference is used, it must be unique.  If a record with that external reference exists, that record is updated (unless approved)


The Record Owner is derived:

  • ownerid - if specified
  • resource__c - if there is a user defined on the record
  • the importing user


Check the Import Details section - it lists how the file was processed (e.g. all columns found/matched) and errors

Example:

File name: imp.csv
File size: 109
Using Handler: Base Parameter:
Parsed: lines=4 rows=3
Columns: accorto__resource__c, accorto__te_date__c, accorto__hours__c, 
Row #1: resource=Charlie Chatter date=10/12/2016 hours=3
...

Check the "Columns" line for missing, not mapped columns


Format

The csv file must have the label or name of the column in the first line; The columns (which represent the fields) and the order of the columns in the file are user defined (i.e. arbitrary).  For advanced use (overwriting header line, stop line) see the general Import description (use the Preview to validate the settings).


Column Name
Field Label
SynonymValue
account__c
Account

Customer Account Name
activitytype__cActivity TypeYesActivity Type Name or Payroll Code Code
contact__c
Contact

Last Name or Phone
description__c
Description
Yes- any text -
expendituretype__c
Expenditure Type

Expenditure Type Name
external_id__c
External Reference

- any text - unique!
financial_account__c
Fin Account

Financial Account Code
financial_organization__c
Fin Org

Financial Organization Name
hours__c
Hours
YesHours (decimal) number
minutes__c
Minutes

Alternative to importing Hours
isinvoiced__c
Billable

Y, N, true, false
iswaiting__c
Waiting for Details

Y, N, true, false
ownerid
Owner Id

Full username or email address
project__c
Project
Yesname or code or combined (see above)
project_phase__c
Project Phase

phase name or WBS
project_line__c
Project Line

line name or WBS
reason__c
Reason

- any text -
receipt_amount__c
Receipt Amount

(decimal) number
receipt_quantity__c
Receipt Quantity

(decimal) number (e.g. mileage)
reimbursable__c
Receipt Reimbursable

'Yes' (default) , 'No'
resource__c
Resource
YesResource Name or Code
tax_amount__c
Tax Amount

(decimal) number
tax__c
Tax

 Tax Name
te_date__c
T&E Date or Date
YesDate formatted: mm/dd/yyyy or dd.MM.yyyy
or yyyy-MM-dd HH:mm:ss in local time 

te_report__c
T+E Report

existing T+E Report Name
name
T+E Item

existing T+E Item Name for updates
--custom column----custom column name --
your custom column name

You can define synonyms for selected columns in the Custom Settings "Accorto Import Parameters"


Note, do not import the expense amount as that is the receipt amount converted to the currency of the organization and calculated automatically (multi currency). Similar the document amount is the receipt amount converted to the currency of reimbursement (dual currency).