Skip to main content

Business Value

When you need to create purchase documents from a file, you can use Excel Importer to speed up the process. Excel Importer allows you to use item references to handle scenarios where your item codes differ from your vendor's.

Possible Scenarios

Here are some possible scenarios where you might use this feature to import purchase documents:

  • Purchase orders from a replenishment system with no integration to Business Central.
  • Purchase invoices from your vendors. For example, you could import timesheets from your contractors. Look at our example below with our fictional vendor - Transportation Plus.
  • When you do not have an expense management solution to handle company credit cards, you can set up for Credit Card as a vendor and import expenses from the credit card statement.
  • Initial loading of open purchase orders for a new Business Central company.

Feature Details

The Import from Excel action can be found in the lists:

List
Purchase Quotes
Purchase Orders
Purchase Invoices
Purchase Credit Memos
Purchase Return Orders

Settings

Sales Document Settings

Document No. as Posting No.

This setting allows you to use the imported Document No. (if present) as the Posted Document No.

ValueDescription
NoBusiness Central will assign a number while posting the document. This is the default option.
YesUse the imported document as the posted document number. You use this setting if you want to keep the document number on the posted entries that will get created when you post the imported documents.

Replace with Document No. from Series

ValueDescription
YesThe imported document number gets replaced using the number series specified in the setup.
No (Default)The imported document number gets used for the imported documents.

Negative Document Totals

This setting allows you to specify how to handle documents when the total value is negative and will enable you to Ignore, Skip, Cancel and Change Document Type. You can use this setting when importing purchase documents from another system when the file includes orders and returns. It allows you to import the file with a single process - rather than split the file and process two separate imports.

ValueDescription
Do not ValidateThis is the default setting where the app imports the file without checking the totals. Use this setting when you are sure that the file only contains records for the same document type.
Skip during ImportUse this setting if you want the app to check the total and skip documents that have a negative total.
Cancel ImportUse this setting if you want the app to check the total and raise an error if the document total is negative.
Order/Invoice to CreditUse this setting if you want the app to change the document type of negative documents. For example, if you import a file containing orders and returns into either purchase orders or invoices, the app will create credit memos for the documents with negative totals.
Order/Invoice to Return/CreditUse this setting if you want the app to change the document type of negative documents. For example, if you import a file containing orders and returns into purchase orders, the app will create return orders for documents with negative totals. The app will create credit memos for negative documents when importing into purchase invoices.

Custom Values

You can use Custom Values to:

  • Create multiple purchase lines from one imported line. For example, the file may contain a charge for freight that you want to show as a separate line. You can read more about this here.
  • Create comment lines above or below the imported line using a column from the file. You can read more about this here.
  • Handle other custom logic, which you can add by extending Excel Importer.

Document Grouping

When you import a file, the app groups lines into documents differently depending on the following scenarios.

  • If one field you mapped is the document number, the app will group lines by Document No., Currency Code, Buy-from Vendor No. and Pay-to Vendor No..
  • If no document number is mapped, the app groups lines by Vendor Invoice No., Vendor Cr. Memo No., Currency Code, Buy-from Vendor No. and Pay-to Vendor No..

Examples

Import multiple purchase invoices from a vendor

In this example, we import a file that we received from our fictional vendor - Transportation Plus. This example includes using Account/Dimension Mapping to handle the mapping of the G/L Account, the GST/VAT and the Dimensions.

Excel file

Transportation Plus Template

tip

We will specified 5 for the First Data Row in the Excel Mapping Template because the first row of data we want to import is on row 5 of the Excel worksheet.

Excel Mapping Template

Transportation Plus Template

tip

We use the Vendor Invoice number from column B in the Excel file for both the Document No. and the Vendor Invoice No.. We have done this because we want to create one Purchase Invoice in Business Central for every Invoice we receive from our supplier. However, we also want to use the Business Central document number series. We specified Yes for the Replace with Document No. from Series to achieve this result. To avoid receiving an error, we needed to select Never for the Validate Field on the document number mapping line.

note

The file that we will import comes from our vendor's system, and values such as the accounts and dimensions need to get mapped to our Business Central equivalents. We have used the Account/Dimension Mapping to handle this.

Imported Invoices - List

Transportation Plus Template - Invoice List

note

Excel Importer created three invoices because the file we imported had lines spanning three vendor invoices.

Imported Invoice

Transportation Plus Template - Invoice

Step-by-Step Guides

Import a file containing Purchase Invoices

Setup an Excel Mapping Template that is used to import Purchase Invoices

Video

Importing purchase invoices

See Also