Importing data from Excel
Key-Business provides you with the possibility to import master data on products, partners, and charts of accounts from Excel tables. This feature can speed up your administrative tasks related to start using your software, and managing your data.
Important! In order to be able to use the Excel import feature, you need to have a spreadsheet program meeting minimum system requirements (Microsoft Office 2003+), or Access Database Engine 2010 Redistributable (available as a free download from the website of Microsoft).
Prior to initiating data import, perform a BACKUP on your current data. Data import, as specified by data import settings, will overwrite your current data.
Prepare for data import properly, bearing the following topics in mind:
 |
 |
Preparing masters of data to import
As the first step of importing products, partners and ledger numbers, prepare related masters of the program.
- As part of the preparation of the table containing partners to import, you need to check the following masters: Public premise type, Country, Payment method, Bank, Ledger numbers, and Partner category. These data can only be saved to partner data during data import, if the given master contains those data, that are present in the table to import.
-
As part of the preparation of the table containing products to import, you need to check the following masters: Unit of quantity, Warehouse, Currency, Ledger numbers, Language, Price list, and Product category. These data can only be saved to product data during data import, if the given master contains those data, that are present in the table to import.
In the table of data to import, at a minimum, those columns need to be present, that are flagged as mandatory. When checking master data, this needs to be taken into consideration too. If you do not wish to import foreign language product names, checking of the Language master can be skipped.
For checking related masters, please refer to the detailed structure descriptions of various Excel tables (chart of accounts, partner, product).
 |
|
General information about setting up the table to import
In the table of data to import, at a minimum, those columns need to be present, that are flagged as mandatory. Additionally, the tables may contain any coloumn in any order. While import, the system checks if column names in the table match column names indicated in the structure description, and in the sample table. Based on column names, the importer utility will be able to associate column contents with data fields.
If you use a sample table as a basis for your import, it is advised to delete those columns from the table, that contain data not to import. If you import for updating and modifying existing product and partner masters, (i.e.: you import to a non-empty database), pay special attention to non-filled colums of the Excel table to import. Blank fields to import may cause existing data to be overwritten (be deleted).
Green data blocks of the table structure description will only be imported, if they are written to the table in the specified order. These blocks may appear in the table multiple times. Such multiple appearances can include entering multiple category values (for product and partner import), price list unit prices, or foreign language names (for product import).
For those columns, where you need to indicate numeric data (for example: Minimal quantity), be careful not to leave unfilled fields. Enter zeros (0) to those fields to import, that do not require values.
 |
|
Use Maintenance / 
Import data to specify the type of data you wish to import.
In the presenting window, use the File to import field to browse for the prepared import file. The format of the file to import can be xls, xlsx, or csv.
Warning! For 64 bit Microsoft Office, only csv import files can be processed.
When opening the table to import, the following processes and checks are performed:
The program reads the selectable name from the ID field of the open file, and imports it to the ID column. For chart of accounts import, it can be Ledger number and Name. For product import, it can be Product name and Product code. For partner import, it can be Partner name and Partner code.
The value of the ID column is going to determine how the pre-data import database state is going to be updated.
This means that the program, during import, will check the value of the ID column, and if the value is found in the database, the program will update its value in accordance with data of the table to import.
Format verification of the table. Should mandatory data be missing, or certain fields contain invalid data, an informational message is displayed in the lower part of the screen. If you click on the message, the cursor will jump to the red cell of the table in question. Now, you can correct the invalid data.
After implementing your corrections, click on Recheck data. If all data are importable, you can click on the Start import button. A confirmation message appears, containing the number of modifications, and the number of new items being inserted. If you choose Yes, the process can begin.
A message is displayed on the screen about successful import.
 |
|
The structure of the Partners Excel file to import (including a sample table)
See detailed description at Partner import >>
See detailed description at Additional partner addresses import >>
 |
|
The structure of the Products Excel file to import (including a sample table)
