Bulk import and update vendors and employees with bank file details

You can import vendor and employee information in bulk, and then update those vendor and employee records with bank file payment information.

If you have already imported your vendors and employees into Sage Intacct, along with their bank file details, you can update the details of your existing vendors and employees using the CSV file. Learn more.

The steps for importing and enabling vendors to receive bank file payments:

  1. Import your vendors into Sage Intacct.
  2. Export your vendors to a CSV file
  3. Update your vendors with bank file details

The steps for importing and enabling employees is the same, but with the employee import templates.

Enabling direct payment to a vendor using a bank file involves selecting the following in the vendor record:

  • ACH or bank file as your preferred payment method.
  • Enable bank file payments as the payment service.
  • The Payment country, currency, and relevant Bank file format.
  • Other information, such as the vendor  number, and additional required fields, depending on the file format you use.
Subscription

Accounts Payable

Cash Management

Sage Cloud Services

Expenses

Regional availability

Bank file payments are only available between banks that use the same currency in the following countries:

  • Australia
  • Canada
  • France
  • Ireland
  • South Africa
  • United Kingdom
  • United States
User type

Business user with admin privileges
Permissions

Cash Management

  • Bank files: List, View, Manage, Export, Confirm, Delete

Accounts Payable

  • Vendors: List, View, Add, Edit, Bank details, and Unmask bank details are required to access the vendor's information for bank file payments

Expenses

  • Employees: List, View, Add, Edit, Bank details, and Unmask bank details are required to access the employee's information for bank file payments
Restrictions

Bank file payments are only available between banks that use the same currency outside the US.

To view the Vendors list:

  • Go to Accounts Payable > All > Vendors.

Guidelines

The most important thing to remember when updating vendor records through CSV import is that changes you make in the spreadsheet will affect your vendor records.

  • If you update spreadsheet data for a vendor, the information you specify will overwrite the data in the existing vendor record.
  • If you remove data from the spreadsheet, the corresponding information will be removed from the vendor record.

Don't overwrite existing data with blank data, unless you intend to remove that data from your Vendors list. For example, if you export the Email Address field and then clear the data in the Email Address column of your spreadsheet, existing vendor email addresses will be deleted when you import the file.

  • Make a backup of your vendor information before you make changes. Save a copy of the "CSV for Import" spreadsheet so that you have a record of the Vendors list before the update process.
  • Before making large-scale changes, test the update process. Try creating a few test vendors and then update just those vendor records.
    • When you export the "CSV for Import" file, be sure to include the Vendor ID column. This identifies the vendor record that you’re updating.
    • Column order isn't important; for example, the Vendor ID doesn't need to be first. The header row identifies the contents of each column.
    • When you're updating the "CSV for Import" file, keep in mind that different types of fields require different information. For checkbox options, enter TRUE to select the option, and enter FALSE to deselect the option.

Prepare a CSV file for import

When you download a template, the correct headers are in the template already. All you have to do is fill in the columns with your data, save it as a CSV file, and upload the file.

The header titles in the template you download correlate to fields in Intacct. Incorrect header titles will cause an upload error.

For more information about the CSV upload process, including best practices, common errors, and how to upload your CSV file, see Prepare your file for CSV import.

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.

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.

Avoid common import errors

Intacct recommends following these guidelines to avoid import errors.

In Microsoft Excel:

  1. Select all cells.
  2. Right-click the cells and select Format Cells.
  3. On the Number tab, select Text and then select OK.
  4. Save the file.

The short date format is formatted like this: 3/15/2021

In Microsoft Excel:

  1. Select a column of dates.
  2. Right-click the column and select Format Cells.
  3. On the Number tab, select Date and select the short date format.
  4. 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:

  1. Select a range of cells.
  2. On the Home tab, in the Editing group, select Find & Select and select Replace.
  3. In the Find what box, enter one space. Leave the Replace with box blank.
  4. Select Replace All. A prompt opens telling you how many spaces have been removed.
  5. 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.

Import your vendors into Sage Intacct

If you have already imported vendors into Sage Intacct, skip this step, and proceed to Export your vendors to a CSV file.

If you want to add vendors in bulk to Sage Intacct, you'll want to export your vendors from the vendor management tool you use to manage them. Save the exported vendor details as a CSV file. Then, import the CSV file containing the exported vendors into Sage Intacct.

The maximum number of vendor records you can import in one CSV file is 25,000 records.

To import your vendors into Sage Intacct: 

  1. Export the following vendor information from your vendor management tool:

    • Vendor ID
    • Vendor name
    • Vendor country
    • Vendor contact details

  2. Enable vendor sequence auto-numbering, so that Sage Intacct automatically assigns a sequential ID number to each vendor record:

    1. Go to Company > Admin > Subscriptions.

    2. On the Subscriptions page, select Accounts Payable and then select Configure.
    3. Scroll down to Document sequencing and select a numbering sequence for Vendors. For details, see Document sequencing.

  3. Go to Company > Setup > Configuration > Import data.

  4. Download the Template for Vendors.
  5. You'll want to edit the CSV file of exported vendor details so that the file includes the corresponding Sage Intacct column names.

    When you're adding new vendors, include at least the following columns:

    • Vendor ID: Be sure to enter a unique vendor ID that hasn't already been used in Intacct.
    • Vendor Name: Enter the vendor name.
    • Print as: Enter the vendor name as it should appear on payments and reports.
    • Country: Enter the vendor country that corresponds to the location of the vendor .
    • Required custom fields: If you've created any custom fields that are required, include these columns.
  6. Follow the instructions in CSV import: Vendors

Export your vendors to a CSV file

You now have your vendors defined in Sage Intacct. However, your vendors lack bank file information. You'll want to export your vendor list before adding bank file information.

Exporting your vendors to a CSV file:

  • Creates a backup file of all your vendors in Sage Intacct
  • Records the vendor ID for each vendor record, enabling you to update the vendor records later with bank file details

To export your vendors from Sage Intacct:

Before starting this process, it's recommended that you create a custom view of your Vendors list that includes the vendors and columns you want to update. That way, when you export the Vendors list, you'll have a spreadsheet that's tailored to the information you want to change.

  1. If you haven't already done so, choose the list view that includes the data you want to export.

    • At the top of the Vendors list, select the All dropdown menu and choose the name of the view that you previously created.
    • If you don't select a view before exporting the Vendors list, your export file includes the list of vendors and columns that you currently see in the Vendors list.
  2. Export the Vendors list in CSV for Import format.

Update your vendors with bank file details

You'll now want to create a separate CSV file that includes your vendor IDs from Sage Intacct and the bank file details required for each vendor record.

Copy your vendor IDs from the exported CSV file into the country-specific CSV update file for updating your vendors with bank file details.

CSV import template Update vendors for bank file payments lists a field-by-field description of the bank file columns in the bank file CSV update template for updating your vendors.

To create a separate CSV file of bank file details per vendor:

  1. Download and open the CSV template for updating vendor records with bank file details:

    1. Go to Company > Setup > Configuration > Import data.

    2. Scroll down to Set Up AP Master List, Open Bills and Adjustments.
    3. Next to Update Vendors for Bank File Payment*, select Template.
    4. Open the downloaded CSV template file in a spreadsheet application.
  2. Open the CSV file of exported vendors you created in Export your vendors to a CSV file.

  3. In your master file of vendor records, select and copy all the values in the VENDOR_ID column.

    The VENDOR_ID column represents the vendor ID for your vendor.

  4. In the bank file update CSV template, paste the VENDOR_ID values into the VENDOR_ID column.

  5. Either manually enter, or copy and paste the bank file details for each vendor row into the bank file update CSV file.

    For example, for each vendor ID, enter values for the PAYMENT_METHOD, BANK_FILE_PAYMENT_ENABLE, and PYMTCOUNTRYCODE columns, and country-specific values.

  6. Save your bank file update CSV file.
  7. Next to Update Vendors for Bank File Payments, select Import, and select the bank file update CSV file you saved.

  8. In the Import Company Information dialog, set the following and select Import:

    Import 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 manually verify them.

Update bank file details for existing vendors with bank file info

You can use the CSV vendor update template to update only a specific vendor detail, such as the payment method or country code. Fill in the vendor IDs of the vendors you want to update, then add a column and values for the field you want to update.

You can do the following:

  • Update values for specific fields, such as PAYMENT_METHOD, or all fields.
  • Enable a vendor for bank file payments. When you change the value of BANK_FILE_PAYMENT_ENABLE from False to True, certain fields become required.

To update specific bank file details for your vendors:

  1. Download and open the CSV template for updating vendor records with bank file details:

    1. Go to Company > Setup > Configuration > Import data.

    2. Scroll down to Set Up AP Master List, Open Bills and Adjustments.
    3. Next to Update Vendors for Bank File Payment*, select Template.
    4. Open the downloaded CSV template file in a spreadsheet application.

  2. Ensure the import CSV file includes the following columns:

    • VENDOR_ID: The VENDOR_ID identifies the vendor and is therefore required. Fill in the IDs of the vendors whose details that you want to change.
    • COLUMN_NAME: Include any column whose details you want to change, for example, PYMTCOUNTRYCODE. Fill in the value that you want to add for each field in the column, corresponding to the relevant VENDOR_ID.

  3. Delete any columns that you don't need to update.

    You can delete any bank file details for vendors by deleting all values for that field. For example, if you want to delete the PYMTCOUNTRYCODE value for all vendors listed by vendor ID in your file, keep the PYMTCOUNTRYCODE column and delete all values in that column only. When you import the CSV file, Intacct overwrites the value of this field as empty (null).

    It's advisable to be absolutely certain about the fields you want to delete before you import your vendor update CSV file. As it represents the key to your vendors, you can't delete the values for VENDOR_ID.

  4. Save your bank file update CSV file.
  5. Next to Update Vendors for Bank File Payments, select Import, and select the bank file update CSV file you saved.

  6. In the Import Company Information dialog, set the following and select Import:

    Import 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 manually verify them.