You will find the program item "Excel interface" in the left quick start bar under "Accessories".
This data exchange works according to the remote control principle via OLE automation. Excel is the server and EULANDA® is the client. The Microsoft® Excel (Server) application is remotely controlled by the EULANDA® (Client) application. For this reason, the Excel application must be installed on the workstation that is to execute this program item.
Data exchange allows data to be imported and exported. The Excel format XLS and the dBase format DBF are supported as file formats.
When exporting, you have the option of selecting fields and data. In addition, EULANDA® can evaluate the fields of an existing Excel file and only export the fields listed there.
During the import only those fields are imported which are also used in EULANDA®; all other fields are ignored.
Selection of the process
First select in the wizard whether you want to import or export data.
Preparation and Rules
In order to import articles or addresses via the Excel interface, certain preparations are necessary:
- The field names used in the Excel file must match the field names used in EULANDA®.
- The possible field names are obtained using the following methods:
- By exporting an article or an address with all fields to Excel and opening the file in Excel. This then automatically contains the required field names.
- Alternatively, a field list can be displayed via the SQL Designer, which you can access via the Quick Launch toolbar and the "System Administration" item. This displays the address fields (dbo.address) when called. You can set the selection box to dbo.article to see the possible article fields.
- You can also output a field list. For this purpose, there is a macro "Field description for all tables" that can be executed with CTRL+E. The result can be saved as text with the right mouse button
- The field names must be in the first line of the Excel file.
- Field names that are not known in EULANDA® are ignored during import. In this way you can also exclude fields (Excel columns) for certain imports and then activate them again or exclude them from the import in general.
- Internal fields must not be overwritten; these are the fields: "ID", "CreateDate", "ChangeDate", "Locked", "Stamp".
- Calculated fields may not be used during import. These are write-protected, so that an error message would be generated during the import. This applies to the fields "VkNetto", "VkBrutto", "SNPflichtFlg", "ChargenPflichtFlg", "Rabatt" and "Multi" for articles.
- When importing article sales units, only the "Vk" field may be imported. By default, this is the net price. If the field "Vk" is a gross price, the field "BruttoFlg" must also be transferred with the value TRUE. If the field "GrossFlg" has the value FALSE, "Vk" is also a net price. This rule makes it possible to manage gross or net prices in a mixed way. Normally, only the field "Vk" is transferred (without the field "BruttoFlg").
- If the data record is missing in the root, it is created by the import. If the data record already exists, the data record is updated. Only the fields that are transferred in the Excel file are updated.
- Fields that refer to constants must contain a valid value. For example, if an article merchandise category is imported, these values must already exist in the"Settings" under"Accessories" on the"Product group" tab. Inconsistencies are not allowed during import. This affects the fields "ManufacturerID", "ErloesGr", "MengenEh", "MwStGr", "RabattGr", "Währhrung", "WarenGr", "Lagertyp", and "VertreterID" for articles and the fields "AdresseGr", "Waehrung", "ZielID", "Lagerkonto", "Preisliste" and "VertreterID" for addresses. If in doubt, these fields should not be used during import.
- When importing or exporting via the Excel interface, no Excel must be installed on the executing work center. The data exchange uses the remote control function via OLE automation.
- Fields that refer to constants or fields that are calculated in the server can change from version to version in EULANDA®. The field names listed here are therefore only a guide. If you are importing and receive an error message indicating a consistency conflict or a read-only field, remove this field from the import list.
Example of an Excel file
Determination of tables
First, it is decided whether addresses or articles are to be exported via Excel.
Determination of the supplier
If you have selected "Items" in the table selection, you can determine the supplier from whom the data to be imported is to be determined in the following dialog. EULANDA® supports any number of suppliers for each article. In addition to the article in the article master, supplier-specific data is also imported for each imported article. These include vendor discount groups, vendor merchandise groups, and vendor prices.
This procedure allows you to change the article number in the master article after importing a vendor master without losing the option of maintaining prices through data updates.
If you want to treat the data to be imported as "Own articles", select this option in the supplier selection.
Then the file name is specified. You can also use the file via the file dialog with the button"..." at the end of the file field.
Starting the import
You have now made all entries and can start the data import.
Existing articles are automatically updated and missing articles are created.
If you have selected a vendor, article numbers that have never been imported for this vendor but may already be used by other vendors are automatically increased in the article number.
If you have imported an article from vendor"X" with article number"4711" and now import vendor Y, which also has an article"4711" in its assortment, the new article is saved as"4711 (2)".
Exporting of data
Determining the table
First, it is decided whether addresses or articles are to be exported via Excel.
Selecting the table
You can type the file name, including the path, into the field provided.
Alternatively, a file dialog is also available via the"...." button at the end of the file field. This dialog also allows you to specify whether you want to create a dBase file instead of an Excel file.
Determination of options
If the file exists, the fields defined there are read. In Excel, these are the field names in the first line of the spreadsheet. If the field names match the field names used internally, you have three options for further processing. If these do not match, only the last option is available.
Option: Overwrite file, keep field names
This option removes all data from the file and replaces it with the exported data. Only the first line of the file, the line with the field names, is retained. EULANDA® exports only the fields that existed in the old file. Here you can easily determine which fields EULANDA® should export. Field names that are not known in EULANDA® are left blank.
Option: Append data to structure
The new data is appended to the output file. Field names that are not known in EULANDA® are left blank.
Option: Replace file, like in a new creation
The existing file is completely replaced, regardless of its content. The next dialog page allows you to specify the fields to be exported.
Selection of field names
The field names are only displayed if the output file does not exist or the last option in the previous dialog was selected.
Select the fields you want to export by clicking on them. The"Default" button selects all fields except the system fields. System fields are the fields "ID", "CreateDate", "ChangeDate", "Locked". The"No long texts" button selects all fields except system fields and long texts. The"Importable fields only" button selects all fields that can be re-imported. The"Nothing" button removes all selected fields.
You use the data selection to determine the number of data records. You can either export all data records or only data records that have certain characteristics by
clicking on the"Data selection" button. If you double-click on the top term, all data records are selected. Otherwise you can select or exclude any combination of characteristics.
After you have started the export, a message appears that the export was completed successfully and without errors. You can now open, view and edit the Excel file.
Created Excel file