Importing master data for Microsoft Excel
The Kofax AP Essentials Data Import app for Microsoft Excel lets you easily import master data from Microsoft Excel to AP Essentials.
To import master data from Microsoft Excel to AP Essentials:
- Install and activate the app from the Microsoft Office Store.
-
Open an Excel document that contains the master data that you want to upload to
AP Essentials.
Make a separate Excel sheet for each master-data type. For example, you can have one sheet for suppliers and one sheet for currencies. Each sheet must be named according to the master data type: Suppliers, Currencies, GLAccounts, etc.
PurchaseOrders and PurchaseOrderLines also need one sheet each. OrderNumber is used to connect line items (PurchaseOrderLines) to corresponding purchase orders (PurchaseOrders), so lines matching an order number are imported as a part of the purchase order.
Make sure that the column names in Excel conform to the names specified in the tables below. The column names are not case sensitive, and spaces are ignored.
-
Open the
AP Essentials
Data Import app and specify:
- The Login URL that you use to log in to AP Essentials.
- The Administrator user name and password of a customer administrator. Single sign-on credentials are not supported.
- If two-factor authentication is enabled, you must specify a Two-factor token. The token is automatically saved in the current workbook for 30 days. If you create a new workbook, you must provide a new token.
- Click Synchronize to start the master-data upload. The app searches the current Excel document for spreadsheets that contain master data and uploads the data to AP Essentials.
Note that when you save the Excel document, the login address and user name are saved with the Excel document. The password is not saved.
Suppliers
Supplier upload is only available for accounts that utilize accounts payable document processes.
| Column name | Alternative names | Sample data |
|---|---|---|
| Name * | SupplierName | Polychemtex Inc. |
| Number * | SupplierNumber | 00001 |
| Street | StreetAddress, Address | 1 Main Street |
| Description | Desc | Supplier of quality goods |
| Buyer | Organization, LegalEntity | My Buyer
You can specify buyer names or external IDs. |
| TaxCode | VATCode | 123456789 |
| TaxRegistrationNumber | VatRegistrationNumber, TaxRegNo | GB999999973 |
| OrganizationNumber | OrganisationNumber, OrgNo | 5561947986 |
| PostalCode | ZipCode, Zip | DH9OTB |
| City | Town | Townsville |
| Location | - | North |
| State | - | Texas |
| Country | CountryName | US |
| PhoneNumber | TelephoneNumber, PhoneNo, Phone | (312) 555-1234 |
| FaxNumber | FaxNo, Fax | (312) 555-2234 |
| PaymentTerm | PaymentTerms | 30 Days |
| PaymentMethod | - | Direct Deposit |
| CurrencyCode | Currency, Cur | GBP |
| Blocked
(Active) |
Disabled
(Enabled) |
1/true/X
(0/false) |
* Required
Customers
Customer upload is only available for accounts that utilize accounts receivable document processes.
| Column name | Alternative names | Sample data |
|---|---|---|
| Name * | CustomerName | Polychemtex Inc. |
| Number * | CustomerNumber | 00001 |
| Description | Desc | North branch |
| Seller | Organization, LegalEntity |
My Seller You can specify seller names or external IDs |
| TaxRegistrationNumber | VatRegistrationNumber, TaxRegNo | GB999999976 |
| OrganizationNumber | OrganisationNumber, OrgNo | K28TF62YM3 |
| Street | StreetAddress, Address | 1 Main Street |
| PostalCode | ZipCode, Zip | DH9OTB |
| City | Town | Townsville |
| State | - | Texas |
| Country | CountryName | US |
| PhoneNumber | TelephoneNumber, PhoneNo, Phone | (312) 555-1234 |
| FaxNumber | FaxNo, Fax | (312) 555-2234 |
| Blocked
(Active) |
Disabled
(Enabled) |
1/true/X
(0/false) |
* Required
Currencies
| Column name | Alternative names | Sample data |
|---|---|---|
| Code * | CurrencyCode, Number | SEK |
| Name | CurrencyName, Description | Swedish Krona |
| Rate | ExchangeRate | 1.0 |
| Active | Enabled | 1/true/X
(0/false) |
| Buyer | Organization, LegalEntity | My Buyer
You can specify buyer names or external IDs. |
* Required.
GLAccounts (requires Workflow services)
| Column name | Alternative names | Sample data |
|---|---|---|
| Code * | AccountCode, AccountNumber, Number | 1000 |
| Name | AccountName | Telephone costs |
| Group | AccountGroup | Main accounts |
| Description | Desc | Supplier of quality goods |
| Active | Enabled | 1/true/X
(0/false) |
| Buyer | Organization, LegalEntity | My Buyer
You can specify buyer names or external IDs. |
* Required.
Other master data (must be named according to the master data type)
In addition to GL accounts, you can also upload financial dimensions that are setup as coding fields in the Workflow service, such as Project, CostCenter, Employee, etc.
| Column name | Alternative names | Sample data |
|---|---|---|
| Value * | Code, Number, Type + Number (for example: DepartmentNumber) | 200010 |
| Name | Type + Name (for example: DepartmentName) | Marketing |
| Description | Desc | The marketing department |
| Active | Enabled |
1/true/X (0/false) |
| Buyer | Organization, LegalEntity |
My Buyer You can specify buyer names or external IDs. |
* Required.
PurchaseOrders
| Column name | Alternative names | Sample data |
|---|---|---|
| OrderNumber * | OrderNo, Number | CE123456789 |
| SupplierNumber * | SupplierNo, VendorNumber, VendorNo | 00001 |
| CurrencyCode | Currency, Cur | USD |
| DateCreated | Orderdate, Created, Date | 2023-10-29T11:05:01 |
| ContactName | Contact | Ben Martin |
| Description | OrderDescription | Anything can go here. |
| StatusText | Status, OrderStatus | Open order |
| SupplierName | VendorName | Polychemtex |
| Buyer | Organization, LegalEntity |
My Buyer You can specify buyer names or external IDs. |
| RequisitionedBy | Jennifer Smith | |
| CreatedBy | Ben Martin |
* Required.
PurchaseOrderLines
If you upload a PurchaseOrderLines sheet, you must also upload a PurchaseOrders sheet. You cannot upload a PurchaseOrderLines sheet by itself.
| Column name | Alternative names | Sample data |
|---|---|---|
| OrderNumber * | OrderNo, Number | CE123456789 |
| OrderLineNumber * | LineNumber, RowID | 1 |
| ArticleNumber | ArticleNo | 234233 |
| SupplierArticleNumber | SupplierArticleNo, SupplierArticle | 123456 |
| ArticleDescription | Description, Desc, ArticleName | Stereo headphones |
| CategoryNumber | CategoryNo | 123 |
| CategoryDescription | CategoryDesc, CategoryName | Electronics |
| Quantity | Qty, OrderedQuantity | 7 |
| Unit | UOM, UnitOfMeasure | kg |
| UnitPrice | PricePerUnit, Price | 2.00 |
| RowTotal | RowTotalAmount, RowTotalAmountVATExcluded, LineTotal | 25.00 |
| StatusText | Status, Linestatus | Received |
| InvoicedQuantity | InvoicedQty, Invoiced | 10 |
| DeliveredQuantity | DeliveredQty, Delivered | 10 |
| IsDeliveryRequired | DeliveryRequired, ReceiptRequired |
1/true/X (0/false) |
| PriceUnit | PriceFactor, UnitPriceFactor | 1
(Default: 1) |
| Buyer | Organization, LegalEntity |
My Buyer You can specify buyer names or external IDs. |
| VatAmount | 25.00 | |
| DeliveryNoteNumber | ES-123456 | |
| DeliveryDate1 | 20230125 The data format must be yyyyMMdd, and the data type for the cell in Excel must be Text or General. |
|
| DeliveryDate2 | 20230125 The data format must be yyyyMMdd, and the data type for the cell in Excel must be Text or General. |
|
| UniqueArticleNumber | SE12345678 |
* Required.
Troubleshooting
If you use the Apple Safari browser, you must to deselect the Prevent cross-site tracking setting (Preferences > Privacy).