CSV import: Employee expenses
Related information
Related topicsYou can import an employee expense report list into Sage Intacct by uploading a CSV file.
| Subscription | Time and Expenses |
|---|---|
| Regional availability | All regions |
| User type | Business |
| Permissions | Expenses Expense summaries: List expenses, View expense, Add expense |
Before you import
Before importing an employee expense report, you should do the following:
Go to Expenses > Setup > Configuration.
While configuring Expenses, be sure to establish journals specifically for Expenses.
Also before importing, you should set up the expense approval process:
Most companies set up an employee manager who should be able to approve expense reports that employees submit.
Go to Expenses > All > Employees.
- Select Edit next to each employee to display the Employee Information page, and use the Manager box to select a manager for each employee.
Go to Expenses > Setup > Configuration.
- When the Configure Expenses page displays, locate the Expense approval settings section and create an approval process.
About imported Expense reports
When importing Expense reports, you can import the information as Historical or General ledger.
Historical imports
Historical transactions are transactions that took place in the past before your Intacct start date. Because they occurred before your start date, these transactions don’t post to the General Ledger. Instead, the impact of these transactions are assumed to already be included in the opening balances for your accounts. As such, these historical transactions can't be edited.
Since the expense was incurred in your previous accounting system, the expense doesn’t post to the GL. As with all historical transactions, these expenses are assumed to be included in opening balances. However, when the expense payments are paid in Intacct, the payments will be posted to the GL. You can use the non-reimbursable flag if you do not want the expense to be paid in Intacct.
If you submit expense reports as historical transactions with an approval process in place, the expense report can be approved by the appropriate person, and the transactions do not post to the GL.
If you submit expense reports as historical transactions with approvals off, or with an automatic approval process, the expense report is approved automatically, and the transactions do not post to the GL.
General Ledger imports
When you import expense reports as General Ledger transactions, these transactions must be approved before they impact the GL.
If you submit expense reports as General Ledger, with an approval process in place, the expense report is submitted, but must be approved before it posts to the GL.
If you submit expense reports as General Ledger with approvals off, or with an automated approval process, the expense report is approved automatically and posts to the GL immediately.
Approve imported expense reports
Intacct does not send email notification to managers and supervisors regarding approval for expense reports that are imported, rather than created directly in Intacct. These reports still need to be approved, however, before they can be paid through Intacct.
Download a template
| Subscription | Company |
|---|---|
| Regional availability | All regions |
| User type | Business |
| Permissions | Company Setup Checklist: Run |
Download a new blank template from the Company Setup Checklist whenever you import information into Sage Intacct. Using a downloaded template is important because:
- Templates in the Company Setup Checklist are automatically customized based on your company configuration, including any dimensions and custom fields you created.
- Intacct can update templates at any time, especially when a new version of Intacct is released. If Intacct updates a template, an older template might not work during import.
To download a template from the Company Setup Checklist:
Go to Company > Setup > Configuration > Import data.
The Company Setup Checklist opens. You’ll find a list of areas that you can import information into Intacct.
- Decide on a type of information to upload, and select the Template link associated with that information.
Best practices
The following best practices will help you prepare your CSV file for upload.
Download a template from the Company setup checklist or an object list page.
- Use the latest template, especially following a product release.
- Use the correct template for the area in which you want to upload information. Different templates organize different types of information. Use a template specifically for the information that you need to upload.
- Don’t change the titles in the header row. The header titles correlate to fields in Intacct. Incorrect header titles will cause an upload error. When you download a template from the Company setup checklist, the correct headers are in the template.
- Read the header descriptions in the template. The header description is the cell under the header row that explains what type of information is accepted for each column. If you enter information in the wrong format, or with an incorrect accepted value, it will cause an error in your upload. The header descriptions in the template show you how the information should look. For example, the header in the GL Journal Entries template for JOURNAL shows that allows up to four characters, is required, and has no default values. The header in the Reporting Periods template shows that the STATUS only allows the values 1 or 2, which correlates to True and False, and is not a required field.
- Enter required information. Some data is required, while other data is not. Read the description field to determine which information is required. Required information is necessary for your upload to succeed.
Save the file as a comma-separated value, or CSV, file. If saving from Excel, you could choose the comma-delimited file type.
The file will have the file extension .csv after you save it. Leave the file open until your import is successful. Excel can sometimes reformat the file if you close it before the import is successful.
Avoid common import errors
Intacct recommends following these guidelines to avoid import errors.
In Microsoft Excel:
- Select all cells.
- Right-click the cells and select Format Cells.
- On the Number tab, select Text and then select OK.
- Save the file.
The short date format is formatted like this: 3/15/2021
In Microsoft Excel:
- Select a column of dates.
- Right-click the column and select Format Cells.
- On the Number tab, select Date and select the short date format.
- Save the file.
While spaces within memo fields are allowed, make sure that there are no leading or trailing spaces in any field (not just memos). Leading and trailing spaces might cause import errors. For all other fields, remove unnecessary spaces at the start or end of the data.
In Microsoft Excel:
- Select a range of cells.
- On the Home tab, in the Editing group, select Find & Select and select Replace.
- In the Find what box, enter one space. Leave the Replace with box blank.
- Select Replace All. A prompt opens telling you how many spaces have been removed.
- Save the file.
Commas are used to separate fields in CSV files.
To prevent data from shifting into the wrong columns, avoid using commas in numeric and text fields.
If you must include a comma in a text field, always surround the field with double quotes, for example "Smith, John".
Headers in your CSV file must match the template exactly, including spelling, capitalization, and spacing. Even small changes (such as adding a space or changing a letter’s case) will cause the import to fail. Always use the official template provided by Sage Intacct.
ID fields must contain exact references to customers or vendors.
ID fields must match exactly, including capitalization. For example, ven100 and VEN100 do not match.
Enter the ID exactly as it appears in Intacct or you run the risk of matching transactions to the wrong vendor or customer, or having the upload fail.
If you reference a vendor, customer, term, budget, etc., in a CSV file, whatever you’re referencing must already exist in Intacct.
For example, if you’re uploading transactions for a vendor, that vendor must already exist in Intacct before you start the upload. If the vendor does not exist, the upload will fail.
This does not apply when creating new customers or vendors using the import process.
For more information about to correctly number line items, see CSV import: Line number examples.
For fields with predefined valid entries (such as True/False), use initial capitalization. For example True, not TRUE or true. IDs require exact case matches, while predefined entries require initial capitalization.
If your import template contains a country field, for example for an address, make sure that this field has an entry.
If your company has enabled ISO country codes, you must specify the country code in the country field for each record. If you’re unsure whether ISO codes are enabled, check with your administrator or test a sample import to confirm the required format.
Format dates
When uploading your CSV file, you can select a Date Format for the dates in your file.
| Date sequence | Date format |
| Day, month, year |
|
| Month, day, year |
|
| Year, month, day |
|
| Day, month, year |
|
| Month, day, year |
|
| Year, month, day |
|
When you select a date format here, Intacct attempts to import your file using the selected date format. If Intacct can’t import your file with the selected date format, it attempts to discover the date format used in your file and import the data using that date format.
Import a CSV file
Now that you've entered your data and looked through it for any of the common issues imports can have, you’re ready to import your file to Intacct.
To import your CSV file:
Go to Company > Setup > Configuration > Import data.
- Find the information type that you want to import, and select the corresponding Import link.
In the Import Company Information dialog, set the following and select Import:
Import Company Information: optionsImport file option descriptions Option Description Browse
Use the Browse button to locate and select the CSV file you want to import.
Date format
When you select a date format here, Sage Intacct attempts to import your file using the selected date format. If Intacct can’t import your file with the selected date format, it attempts to discover the date format used in your file and import the data using that date format.
File encoding
Leave this option set to "auto-detect" unless you're unable to import your file. Depending on your operating system, you may need to select a different type of file encoding.
Process offline
Some imports can take considerable time, depending on their size and how much processing the system must do on the uploaded data. If you're not sure how much time your upload will take, select Process offline. When the system completes the process, it sends an email to the address you provide in the Email results to this address field. The system also sends you any errors that may have occurred in the process. If you prefer to wait until the upload process is complete, don’t select the Process offline checkbox.
Transactions and other records that are processed offline show a source of System in the audit trail, instead of CSV import.
Email results to this address
Email address to which import results and errors should be sent. You can enter multiple email addresses, separated by a semicolon (;) or comma (,).
Sage Intacct highly recommends entering an email address.
After an import, Sage Intacct informs you if the import was successful and how many records were imported.
In a completely successful import, the confirmation page lets you verify all the records in your original file were, in fact, successfully imported without having to verify them manually.
Offline CSV imports
Some imports can take considerable time, depending on their size and how much processing Intacct must do to the data. When in doubt, select Process offline on the import utility page.
When the offline process is complete, Intacct sends an email to the address you entered in the Import Company Information dialog (where you imported your file).
Intacct recommends that you select Process offline when importing CSV files, and provide your email address. If your import fails, you’ll always be notified, and the failed records are sent to your email.
Handle errors
If you experience import errors, review the importing tips and common errors.
Import errors occur when you try to import information that doesn’t match the requirements of the template. Each template has a set of required information you must enter to import the file successfully, and formats to follow for certain kinds of information. Read the descriptions in the template to determine the most accurate way to enter your data.
If individual transactions are in error, the rows in the transaction that are in error are saved in a CSV file for you along with an error code for each row. Check your email for information about specific errors. See Find and fix CSV import errors for more information.
For more information about the CSV upload process, read Prepare your file for CSV import.
Field descriptions
The field definitions in your template explain what type of information is required for each field, and how to enter valid information. It's always a good idea to read the field definitions before making entries.
You can reorder the columns of your CSV file for ease of use. However, the column header must contain the field name exactly as it appears in the original template.
If you downloaded a template from the Company Setup Checklist or an object list page, your template contains the headers and dimensions that are specific to your company. The headers in a standard template might not contain field definitions specific to your company.
To import dimension values, enter information in the appropriate dimension column of the import spreadsheet. Otherwise, no information appears for that dimension.
If you relabeled any dimensions (see Terminology for more information), your dimension name does not appear in the CSV template, but the original Intacct dimension name does.
The Supporting Document ID field is not used in the template for import. If your template contains this headers, do not enter information for these fields. You cannot add supporting documents during an import. Adding this information to the template will cause your upload to fail.
The CSV template for Employee Expenses supports all currency fields, and all new fields in the UI (such as quantity).
If you are an OpenAir.com customer, expense report transactions are transmitted to OpenAir.com, along with other data such as customers and outstanding bills and invoices, as the two applications synchronize automatically. As you may not want to synchronize, before you import your employees you should decide what effect you want relative to OpenAir. If you want the convenience of pushing all of this data to OpenAir.com at once, you must install OpenAir.com before you begin the import process. Otherwise, do not install OpenAir, and instead use Account Labels ("On").
| Field Name: | DONOTIMPORT |
|---|---|
# | Any row that starts with # is ignored during import. |
| Field Name: | REPORT_NO |
UI Field Name: | Expense Report Number |
Type: | Character |
Length: | 30 |
Default Value: | None |
Valid Values: | Any |
Dependencies: | No uniqueness required, but encouraged |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | EMPLOYEE_ID |
UI Field Name: | Employee |
Type: | Character |
Length: | 20 |
Default Value: | None |
Valid Values: | Alphanumeric and underscore |
Dependencies: | Must refer to a valid employee |
Required: | Yes |
Editable: | No |
| Field Name: | DATE_FILED |
UI Field Name: | Date Filed |
Type: | Date |
Length: | 10 |
Default Value: | None |
Valid Values: | Any valid date format |
Dependencies: | None |
Required: | Yes |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | DESCRIPTION |
UI Field Name: | Reason For Expense |
Type: | Character |
Length: | 80 |
Default Value: | None |
Valid Values: | Any |
Dependencies: | None |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | REPORT_MEMO |
UI Field Name: | Memo |
Type: | Character |
Length: | 20 |
Default Value: | None |
Valid Values: | Any |
Dependencies: | None |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | BASECURR |
UI Field Name: | Base Currency |
Type: | Character |
Length: | 3 |
Default Value: | None |
Valid Values: | Any defined ISO three-letter ISO-4127 currency code such as USD or EUR |
Dependencies: | Must reference a previously defined currency; locations in line items must use the same base currency. |
Required: | No, except for shared multi-entity companies with multiple base currencies. |
Editable: | No |
| Notes: | Applies only to shared multi-entity companies with multiple base currencies. This field defines the transaction base currency. Verify that locations in line items use the same base currency. |
| Field Name: | CURRENCY |
UI Field Name: | Reimbursement Currency |
Type: | Character |
Length: | 3 |
Default Value: | None |
Valid Values: | Any defined ISO three-letter ISO-4127 currency code such as USD or EUR |
Dependencies: | Must reference a previously defined currency; locations in line items must use the same currency. |
Required: | No |
Editable: | Yes |
| Notes: | This field defines the expense reimbursement currency. |
| Field Name: | STATE |
UI Field Name: | State |
Type: | Character |
Length: | - |
Default Value: | Submitted |
Valid Values: | Draft, Submitted |
Dependencies: | None |
Required: | No |
Editable: | Yes |
| Field Name: | LINE_NO |
UI Field Name: | No field name as such. These are the incrementing numbers on the left side of the transaction area in the Create Adjustment page. |
Type: | Number |
Length: | 4 |
Default Value: | None |
Valid Values: | Positive integer |
Dependencies: | Start with 1 for the first line of a transaction, and increment by one for each subsequent line. |
Required: | Yes |
Editable: | No |
| Field Name: | PAID_TO |
UI Field Name: | Paid To |
Type: | Character |
Length: | 1000 |
Default Value: | None |
Valid Values: | Any |
Dependencies: | None |
Required: | Configurable |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | ACCT_NO |
UI Field Name: | Account |
Type: | Character |
Length: | 24 |
Default Value: | None |
Valid Values: | Must conform to the primary/sub accounts. |
Dependencies: | Must refer to a valid account number |
Required: | Yes |
Editable: | No |
| Field Name: | EXPENSE_TYPE |
UI Field Name: | Expense Type |
Type: | Character |
Length: | 80 |
Default Value: | None |
Valid Values: | Any |
Dependencies: | None |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | ENTRY_DATE |
UI Field Name: | Date |
Type: | Date |
Length: | 10 |
Default Value: | None |
Valid Values: | Any valid date format |
Dependencies: | None |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | LOCATION_ID |
UI Field Name: | Location |
Type: | Character |
Length: | 20 |
Default Value: | None |
Valid Values: | Must refer to an existing valid location |
Dependencies: | None, except in shared multi-entity companies with multiple base currencies, the LOCATION_ID must use the same currency as the BASECURR field. |
Required: | No |
Editable: | Yes, if the bill is not partially paid or paid |
| Notes | In shared multi-entity companies with multiple base currencies, LOCATION_ID must be in an entity that uses the same base currency as BASECURR. |
| Field Name: | DEPT_ID |
UI Field Name: | Department |
Type: | Character |
Length: | 20 |
Default Value: | None |
Valid Values: | Must refer to a valid department |
Dependencies: | None |
Required: | No |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | AMOUNT |
UI Field Name: | Amount |
Type: | Number |
Length: | 38,2 |
Default Value: | None |
Valid Values: | Should be non-zero numbers with negatives designated by a leading dash. |
Dependencies: | None |
Required: | Yes, unless using TRX_AMOUNT for foreign currency expenses. |
Editable: | Yes, if the expense is draft or unapproved |
| Field Name: | PAID_FOR |
UI Field Name: | Paid For |
Type: | Character |
Length: | 1000 |
Valid Values: | Memo |
Dependencies: | None |
Required: | No |
| Field Name: | BILLABLE |
UI Field Name: | Billable |
Type: | Character |
Default Value: | F |
Valid Values: | T (True), F (False) |
Required: | No |
| Field Name: | BILLED |
UI Field Name: | Billed |
Type: | Character |
Default Value: | F |
Valid Values: | T (True), F (False) |
Required: | No |
| Field Name: | PAYMENT_TYPE |
UI Field Name: | Payment Type |
Type: | Character |
Length: | 40 |
Valid Values: | A valid Expense Payment Type |
Required: | No |
| Field Name: | FORM_1099 |
UI Field Name: | Form 1099 |
Type: | Boolean |
Default Value: | false |
Valid Values: | true, false |
Dependencies: | |
Required: | No |
Notes: | Set this field to ‘true’ to load default 1099 information from the employee master into the expense line items. |
| Field Name: | TRX_EXCHRATE_TYPE |
UI Field Name: | Exchange Rate Type |
Type: | Character |
Length: | 40 |
Default Value: | Intacct Daily Rate, unless a custom exchange rate is defined as the default. |
Valid Values: | Alphanumeric and underscore |
Dependencies: | Must reference a previously defined exchange rate type. |
Required: | No |
Editable: | Yes |
Notes: | Use only in multi-currency companies. Defines an optional custom exchange rate type. The Intacct Daily Rate is used if a rate is not defined here. |
| Field Name: | TRX_EXCHRATE_DATE |
UI Field Name: | Exchange Rate Date |
Type: | Date |
Length: | 10 |
Default Value: | Date Filed |
Valid Values: | Any valid date format defining a date prior to or including today |
Dependencies: | None |
Required: | No |
Editable: | Yes |
Notes: | Use only in multi-currency companies. This field determines the exchange rate as of this date. |
| Field Name: | TRX_EXCHANGE_RATE |
UI Field Name: | Exchange Rate |
Type: | Number |
Length: | 17, not including decimal point |
Default Value: | Defaults to the exchange rate of the TRX_CURRENCY on the TRX_EXCHRATE_DATE for the TRX_EXCHRATE_TYPE |
Valid Values: | Positive numbers |
Dependencies: | None |
Required: | No |
Editable: | Yes |
Notes: | Use only in multi-currency companies. Overrides the default exchange rate, which is the product of the TRX_CURRENCY, TRX_EXCHRATE_DATE and TRX_EXCHRATE_TYPE. |
| Field Name: | USER_EXCHRATE |
UI Field Name: | Override Exchange Rate |
Type: | Number |
Length: | 17, not including decimal point |
Default Value: | None |
Valid Values: | Positive numbers |
Dependencies: | None |
Required: | No |
Editable: | Yes |
Notes: | Use only in multi-currency companies. Overrides the TRX_EXCHANGE_RATE, whether explicitly defined or calculated. |
| Field Name: | TRX_AMOUNT |
UI Field Name: | Transaction Amount |
Type: | Number |
Length: | None |
Default Value: | None |
Valid Values: | Should be a non-zero number with negatives designated by a leading dash. |
Dependencies: | None |
Required: | Yes, for foreign-currency expenses (unless using quantity-based expense type with quantity specified). |
Editable: | Yes, if the expense is draft or unapproved. |
| Field Name: | TRX_CURRENCY |
UI Field Name: | Transaction Currency |
Type: | Character |
Length: | 3 |
Default Value: | None |
Valid Values: | Any defined ISO three-letter ISO-4127 currency code such as USD or EUR. |
Dependencies: | Must reference a previously defined currency; locations in line items must use the same currency. |
Required: | No |
Editable: | Yes |
Notes: | This field defines the currency in which the expense was incurred. |
| Field Name: | QUANTITY |
UI Field Name: | Quantity |
Type: | Number |
Default Value: | None |
Valid Values: | Should be a non-zero number, up to 2 decimals. |
Dependencies: | None |
Required: | Yes but only for quantity-based expense type |
Editable: | Yes |
| Field Name: | UNITRATE |
UI Field Name: | Unit Rate |
Type: | Number |
Default Value: | None |
Valid Values: | Should be greater than zero, up to 4 decimals |
Dependencies: | None |
Required: | No; should not be specified for quantity-based expense type. |
Editable: | Yes |
| Field Name: | TAXSOLUTIONID - This field only applies to companies subscribed to the Taxes application. |
| UI Field Name: | Tax solution |
| Type: | Character |
| Length: | 100 |
| Default Value: | None |
| Valid Values: | Alphanumeric and underscore |
| Dependencies: | Must refer to a valid VAT or GST tax solution |
| Required: | Yes, if your organization is configured for multiple tax jurisdictions and you are importing at the top level |
| Editable: | No |
| Field Name: | INCLUSIVETAX - This field only applies to companies subscribed to the Taxes application. |
UI Field Name: | Inclusive taxes |
Type: | Character |
Length: |
|
Default Value: |
|
Valid Values: | true, false |
Dependencies: | The Taxes application is enabled for a VAT or GST tax solution. |
Required: | No |
Editable: | Yes |
Notes: | This field is only applicable for VAT or GST tax solutions. If you set this field to T, enter a value in TOTALTRXAMOUNT and leave TRX_AMOUNT blank. |
| Field Name: | TAX_LINE_NO - This field only applies to companies subscribed to the Taxes application. |
UI Field Name: | No field name as such. These are the incrementing numbers for the tax lines specific to line items. |
Type: | Number |
Length: | 4 |
Default Value: | None |
Valid Values: | Positive integer |
Dependencies: | The Taxes subscription is enabled for a VAT or GST tax solution. Start with 1 for the first tax line of a line item, and increment by one for each subsequent line. |
Required: | Yes, only if the transaction has tax implications |
Editable: | Yes |
Notes: | This field is only applicable for VAT or GST tax solutions. |
| Field Name: | TAX_AMOUNT - This field only applies to companies subscribed to the Taxes application. |
UI Field Name: | Transaction tax |
Type: | Number |
Length: | 38,2 |
Default Value: | None |
Valid Values: | Any, with negatives values designated by a leading dash |
Dependencies: | None |
Required: | No |
Editable: | Yes |
| Notes: | This field is only applicable for VAT or GST tax solutions. The amount entered will override the tax calculated by the tax detail. |
| Field Name: | TAX_DETAILID - This field only applies to companies subscribed to the Taxes application. |
UI Field Name: | Tax detail |
Type: | Character |
Length: | 30 |
Default Value: | None |
Valid Values: | Refers to a valid tax detail ID |
Dependencies: | None |
Required: | Yes, only if the transaction has tax implications |
Editable: | Yes |
| Notes: | This field is only applicable for VAT or GST tax solutions. |
| Field Name: | TOTALTRXAMOUNT - This field only applies to companies subscribed to the Taxes application. |
UI Field Name: | Transaction total |
Type: | Number |
Length: | 38,2 |
Default Value: | None |
Valid Values: | Any, with negative values designated by a leading dash |
Dependencies: | None |
Required: | Yes, only if INCLUSIVETAX is set to T |
Editable: | Yes |
| Notes: | This field is only applicable for VAT or GST tax solutions. |
| Field Name: | GLDIMPRODUCT |
UI Field Name: | Product |
Required: | No |
