Supervisor utilities > Book > Import administration

With this function, it is possible, in a Pro or Trust version of Mill7, to import a bookkeeping from another system. The source can be a XAF Auditfile or an Excel file. Both options are described below. The function is only available to the user SUPERV and can be found via Supervisor > Book > Import administration. The function can only be applied in the last book year of a client bookkeeping.

Please note!

Any processing starts with the deletion of all data present in the currently opened bookkeeping. Preferably make sure to have a good backup first to prevent from accidentally losing valuable data.

Process

In the main window, a source file can be selected to begin with. Depending on the file type, XAF Auditfile or Excel file, there are different options available. In both cases, the data can be exported to an Excel file. This gives a good picture of the result. The data in the Excel file reflects the data from the source file, but translated into the way Mill7 stores the data. If errors have been found, they are listed in the last columns of the various sheets under the heading Notes. The data in the Excel file can be supplemented and improved for the next step in the import process. In this way, a XAF Auditfile can be converted to Excel and the Excel file can also be imported again after the necessary adjustments.

An Excel file is presented as a temporary file. If the file is to serve as an import file, it is recommended to save it elsewhere with an appropriate name.

Settings

When data is removed from the bookkeeping during processing, this also happens with some settings, but not with all settings. That does not mean that the settings that remain are correct. After processing a XAF Auditfile or an Excel file, another window appears in which the following settings, which are important for accounting, are shown.

General ledger no. first P/L account

General ledger no. result P/L

General ledger no. payment discount creditors

General ledger no. daybook differences

General ledger no. payment differences

General ledger no. automatic batch payments

Balance code table

If necessary, these settings can be adjusted immediately, but this can also be done later via Maintenance > Settings. There are also many other settings that may need to be checked.

XAF Auditfile

Most accounting packages can export bookkeepings to audit files. Over the years, several versions of XAF Auditfile have been published. The most used and also the most extensive is version 3.2. This is the version that can be imported into Mill7. The more data an audit file contains, the more complete a bookkeeping is after import. Completeness is determined not only by the version, but also by the system used to create the file.

The following files/tables are built on the basis of a XAF Auditfile.

•001. Bookkeeping (general data of the bookkeeping)

•011. Accounts

•010. Daybooks

•013. Daybook totals

•012. Journal entries

•092. Companies

•002. Debtors

•004. Creditors

•003. VAT codes

•037. Cost centres

•038. Cost types

•098. Diagrams

When importing an Auditfile, there are three additional options available: Allow foreign currency journals, Allow applying VAT codes in journals, and Apply mapping file.

Allow foreign currency in journals

If journal entries are offered in an Auditfile in which data relating to foreign currencies are also specified (currency code and amount), the journal entry can be booked directly in that currency. If the option is not activated, each entry is booked in the accounting currency. If there is no booking directly in foreign currency, than the information regarding the foreign currency is included in the journal entry using the Ctrl+K method. Booking directly in foreign currencies is only possible in journals for daybooks for banks, debtors creditors.

Allow applying VAT codes in journals

When booking invoices, in journal entries for debtors and creditors, it is possible to specify the VAT via a VAT code. This is only done when importing if this option is enabled. If this is not the case, the VAT is booked directly to the account of the VAT code with an additional journal entry. Booking with VAT codes is not possible in every diary and with every type of journal entry. It is therefore possible that journal entries are rejected because of the application of VAT codes.

Apply mapping file

A special Excel file can be used to convert G/L accounts during import. The option can be activated as soon as a conversion file has been designated. This is done via the Mapping file button.

Mapping file

When importing an Auditfile, a mapping file can be used. A mapping file is an Excel file with a sheet in which a translation of original numbers or codes of ledger accounts into desired account numbers is made in two columns. The reason for this could be organizational, but often there is a technical necessity to convert. That's because Mill7 only allows numbers of a certain length for G/L accounts. In other cases, for example in the case of cost centres and cost types, the translation of codes is made automatically, but for account numbers that need to be converted, a conversion table must be used.

The Mapping file button brings up the functions for the conversion. Here, an Excel file can be opened and a sheet selected. The Edit button allows the file to be opened in Excel to view or modify it. The intention is that each account in the first column is linked to an account number in the second column. The second column contains the account numbers for the bookkeeping in Mill7. It is possible (with the Fill button) to have the file filled with numbers or codes of ledger accounts in the Auditfile. The file will then be replaced. The new file will then have one sheet, where the first column contains the account numbers from the Auditfile and the second column is empty. If no file has been selected yet, the Create button will create a file in the Data folder called GL-mapping.xlsx. This file will then also be selected automatically.

If the selected mapping file is to be applied when importing, please activate the Apply mapping file option. Close the screen to return to the main screen.

If more administrations have to be imported, the same conversion file could always be used. That depends on the extent to which these administrations correspond to each other.

Excel file

An Excel file offers the opportunity to import an administration even more accurately. The file must be set up in a way that reflects the structures of the files in Mill7. The file has sheets for various files in Mill7. The names of these start with a 3-digit number that is followed by a dot. To give some examples: "001." for general data of the administration, "011." for ledger accounts, "010." for daybooks, "092." for companies, "002." for debtors, "004." for creditors, "012." for journal entries, etc. The number is important for identifying the data, but what follows the dot in the name of a sheet is not. For example, a sheet name could be "001. Bookkeeping". In Mill7, each file has an identification number. Please refer to the file list for this.

The order of the columns of data in each sheet is also important. The column titles are not used to identify the data fields. For this, only the position of the column is considered.

To see an example, such a file could be created using an Auditfile, where the option to create an Excel file is activated. Do this, for example, in a test bookkeeping, from which the existing data may be deleted. A XAF Auditfile can be created with Mill7 via Bookkeeping > Financial > Create XML audit file.

Conversion file

When importing an Excel file, a conversion file cannot be used. We assume that this is not necessary, in an Excel file everything must be arranged in advance.

Example

A procedure for importing a XAF Auditfile could be as follows.

In the import function, select Auditfile as the source.

If necessary, use a mapping file with a conversion table for G/L accounts.

Activate the option to export to Excel.

Click the Start button. Please note: existing data in the open administration will be deleted!

The Excel file will open with a temporary name.

Correct or complete the data in the various sheets where necessary.

Save the modified Excel file, preferably with an appropriate name in a different location.

In the import function, select the modified Excel file as the new source.

Click the Start button.

When a mapping file is used, then there will be some more steps, off course.

Concluding remarks

A problem with Auditfiles is that the way in which the data should be interpreted varies depending on the accounting package with which it is created. Audit files largely reflect the design of data and procedures in an accounting package, and these can differ from each other in important parts. There are also many optional fields, including relation codes and daybook types, that accounting packages do not always include. Also, the fact that Auditfiles are not required by law can lead to differences in availability and quality.

Despite its limitations, the XAF Auditfile is an important standard for exchanging financial data with the tax authorities and accountants. Most accounting packages are therefore able to produce Auditfiles. This could also make it a good tool for importing administrations into Mill7.

This is a new feature that is still in an experimental phase. Many controls that are needed to manage the processing of data are still missing. We recommend that you contact our helpdesk if you would like to import administrations into Mill7.