Skip to main content

Import from Excel

Here we show you how simple it is to import a file from Excel. This process applies to all pages where we have made the Import from Excel action available. In this example, we will import customer transactions into a sales journal. This example assumes you have already created an Excel Mapping Template using the General Journal import type.

Importing a Sales Journal

Open the Import from Excel Page

In the sales journal page, select the Import from Excel action. Sales Journal

Select the Excel Mapping Template

Enter the Template Code or choose the Template Code by using the lookup. You could alternatively click Select a template to use for the import, which will open the Excel Importer Mapping List where you can choose a template. Sales Journal Select Template

Choose the Excel file and Excel Worksheet

Browse to the Excel file by selecting the Assist Edit button next to the Excel file name. Sales Journal Select Template Completed

note

If your Excel file only has one worksheet, the Worksheet Name will default. If the file has multiple sheets, you get prompted to select the sheet from a list showing all sheets in the Excel workbook. If you mistake and choose the incorrect worksheet, you can use the assist edit next to the Worksheet Name to select a different worksheet.

tip

You will notice that the text highlighted by the blue rectangle has changed and now reads Click here to view or update template - CUSTOMER-OPENING. If you click this, the Excel Mapping Template page will open.

Click OK to import the file

Click OK to import the file. After importing the file, Excel Importer displays a message confirming that the import is complete. After clicking OK, Excel Importer will return you to the page where you started. Sales Journal Select Template Completed

Importing a variable-length text file for the first time

Here we show you how to configure a template and them proceed to import it.

Data Validation on Imports

Excel Importer validates the template setup and the most common errors before importing the data. If errors occur at this level, the import gets cancelled, and a page showing the user the errors is displayed. Suppose there are no errors in the data. In that case, Excel Importer will start importing the data and perform the default validation on the data you import as if you were entering it manually.

Excel Mapping Template Setup Errors

If the template setup is incorrect or incomplete, it is impossible to process the file. The app checks all the mapping lines to ensure the mapping setup is complete. Any errors get collected and displayed once all mapping lines have been checked. The example below shows the behaviour if you select a template that is not correctly configured.

Excel Mapping Template Errors On Import

tip

If you encounter this page, select Open Related Record to open the Excel Mapping Template so that you can correct the setup. You can find more information on the Excel Mapping Template here.

Data Errors

Data errors occur when the file does not contain valid data or when the data in the file contains references to entities that do not exist or entities that cannot be used (e.g. blocked customers). By default, Excel Importer will validate the most common issues before importing the data. This approach lets the app provide you with a list of errors rather than stopping on the first error. The validation done before importing will check the following:

  • The data in the file can be imported into the fields defined in the template. For example, you cannot import text into a decimal field.
  • The minimum information is specified - for example, a Sales Invoice must have a customer number, or a general journal line must have an account number specified.
  • The entities (e.g. G/L Accounts) referenced in the file exist and are not blocked.

The app processes all rows in the file while performing this validation. If there are errors after the initial validation, processing gets cancelled, and the app displays a list of errors. The example below shows a combination of data issues in the file (red rectangle) and configuration issues (orange rectangle).

Data Errors On Import

tip

You will notice that the Source field is filled in for the errors related to G/L Account 1240 in the example above. You can select Open Related Record to open the G/L Account Card if you want to edit the account.

See Also