Master data import

A master data import is a recurring Hangfire job. It reads the master data from a source (CSV file, ODBC or SQL query, ERP interface), turns each row into a record with a mapping script and reconciles the result with the existing master data.

Which sources are available depends on the ERP plugin. The generic plugins read CSV files, ODBC data sources and SQL Server databases. This page mainly describes the import from CSV files.

The import job

Each import is an entry in the table conf.IrRecurringJobs. It is set up during commissioning by enio AG or a partner.

Field	Meaning
`HangfireId`	Unique ID of the job, for example `ch.invoicer.mstr.suppliers`
`Description`	Description shown under Administration > Job-Ausführung
`Enabled`	Whether the job is active
`CronExpression`	When the job runs, for example `0 6 * * *` for daily at 6 am
`TimeZoneId`	Time zone for the cron expression
`ClassIdentifier`	Class that runs the import, for example `enio.erp.Plugin.FromCsv.Import.Import_Supplier_From_File`
`MethodName`	Method of the class, for imports usually `DoImport`
`MethodArgumentIdentifier`	Type of the configuration, for example `enio.erp.Plugin.FromCsv.Import.ImportFromFileBaseArgument`
`Configuration`	Import configuration as JSON (see below)
`ObjectMappingCode`	The mapping script (see below)

Configuring a CSV import

{
  "SourceFilePath": "\\\\<server>\\export\\suppliers.csv",
  "SourceFileEncoding": "utf-8",
  "Separator": ";",
  "HasHeaderRow": true,
  "OuColumnIndex": 0,
  "Source": "ERP",
  "MappingKeys": ["IrOrganizationUnitId", "Number"],
  "DeleteNotDeliveredRecords": true,
  "TraceLevel": "Default"
}

Setting	Default	Meaning
`SourceFilePath`		Path to the file. Possible are a file path, a network share or an address with `http`.
`Host`, `Port`, `Username`, `Password`		For files on an SFTP server: `Host` starts with `sftp://`.
`SourceFileEncoding`	Server default	Character set, for example `utf-8`, `utf-8bom` or `windows-1252`
`Separator`	`;`	Separator between columns
`HasHeaderRow`	`false`	Whether the first row contains column headings. It is then skipped.
`CsvHelperMode`	`RFC4180`	Handling of quotes: `RFC4180`, `Escape` or `NoEscape`
`IgnoreEmptyFile`	`false`	Skip an empty file without error
`IgnoreBadData`	`false`	Tolerate badly formatted rows
`OnlyTakeNewestFileOfFolder`	`false`	`SourceFilePath` is a folder. The import takes the newest file in it.
`FileNameIncludesTimeStamp`	`false`	The file name contains a date (format in `FileNameIncludesTimeStampFormat`, default `yyyyMMdd`).
`OuColumnIndex`	`0`	Column (counted from 0) with the external ID of the organization unit
`UseOUId`		Fixed external ID of the organization unit for all rows. Replaces `OuColumnIndex`.
`Source`	`External`	Source of the records, see Master data
`MappingKeys`		Fields by which the import recognizes existing records, see Master data
`IgnoreProperties`		Fields the import never overwrites in existing records, for example because they are maintained in invoiceR
`DeleteNotDeliveredRecords`	`true`	Mark records that are no longer delivered as deleted
`ReactivateDeletedRecords`	`false`	Reactivate deleted records when they are delivered again
`MappingFilePath`, `MappingFileEncoding`		Load the mapping script from a file or address instead of `ObjectMappingCode`
`BatchSize`	`999999`	After how many rows intermediate saves happen
`TraceLevel`	`Default`	With `Verbose` the job writes every row to the log.

The mapping script

The mapping script is C# code. It runs for every row and fills the new record. The following is available in the script:

Variable	Content
`words`	The columns of the current row as text array, counted from 0
`entityFromImport`	The new record that the script fills. Organization unit and tenant are already set.
`cultureInfo`	Swiss regional settings, for example to convert numbers and dates
`importDynamic`	For imports from databases, the row as an object with the column names

The script must return the record with return. If it returns null, the import skips the row.

Example for suppliers from a CSV file in the format below:

entityFromImport.Number = words[1];
entityFromImport.Name = words[2];
entityFromImport.Address1 = words[3];
entityFromImport.Zip = words[6];
entityFromImport.City = words[7];
entityFromImport.UID = words[8];
entityFromImport.Country = words[11];
entityFromImport.DefaultTermOfPayment = words[12];
entityFromImport.DefaultVatCode = words[13];

if (string.IsNullOrWhiteSpace(entityFromImport.Number))
    return null;

return entityFromImport;

Example for payment methods from a database query, where the script derives the type from the IBAN:

using System;
using System.Linq;
using web_manager.Enio;

entityFromImport.Number = importDynamic.LaufNummer.ToString();
entityFromImport.SupplierNumber = importDynamic.KreditorNummer.ToString();
entityFromImport.IBAN = importDynamic.IBAN.ToString().Replace(" ", "");
entityFromImport.Account = entityFromImport.IBAN;
entityFromImport.Country = importDynamic.LandCode.ToString();
entityFromImport.Type = string.IsNullOrWhiteSpace(entityFromImport.IBAN)
    ? PaymentMethodType.BankAccount
    : PaymentMethodType.BankIBAN;

return entityFromImport;

CSV formats

If your ERP delivers the master data as CSV files, we recommend the following formats. The column order is free; it is assigned in the mapping script. The first column always contains the external ID of the organization unit (company in the ERP).

Suppliers

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
Number	Text	Unique supplier number	yes
Name	Text		yes
Address1	Text	Address line 1	no
Address2	Text	Address line 2	no
Address3	Text	Address line 3	no
Zip	Text	Postal code	no
City	Text	City	no
UID	Text	Business identification number	no
Email	Text		no
Phone	Text	Phone number	no
Country	Text	ISO country code (CH, DE, LI, …)	no
DefaultTermOfPayment	Text	Default terms of payment (number from the terms of payment)	no
DefaultVatCode	Text	Default VAT code (code from the VAT codes)	no

Payment methods

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
Number	Text	Unique number of the payment method	yes
SupplierNumber	Text	Supplier number from the suppliers	yes
Name	Text	Name of the payment method, for example of the bank	yes
Address	Text	Address	no
Zip	Text	Postal code	no
City	Text	City	no
Country	Text	ISO country code	no
Account	Text	Account number for postal connections	for postal connections
IBAN	Text	IBAN	for bank connections with IBAN
BICCode	Text	BIC according to ISO 9362	no
SwiftNumber	Text	SWIFT	no

Accounts

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
Number	Text	Unique account number	yes
Description	Text	Description	no
VATCode	Text	Default VAT code for the account	no
IsBalanceSheetAccount	Yes/No	`1` = balance sheet account	no

Cost centers

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
Number	Text	Unique cost center number	yes
Description	Text	Description	no

Account/cost center

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
AccountNumber	Text	Account number from the accounts	yes
CostCenterNumber	Text	Cost center number from the cost centers	yes

Terms of payment

Column	Type	Format	Remark	Required
Mandant	Text		External ID of the organization unit	yes
Number	Text		Unique number of the terms of payment	yes
Description	Text		Description	no
NetPeriod	Number	`0`	Net payment period in days	yes
DiscountPeriod1	Number	`0`	Period for discount 1 in days	no
DiscountPercent1	Number	`0.00`	Discount 1 in percent	no
DiscountPeriod2	Number	`0`	Period for discount 2 in days	no
DiscountPercent2	Number	`0.00`	Discount 2 in percent	no

VAT codes

Column	Type	Format	Remark	Required
Mandant	Text		External ID of the organization unit	yes
Code	Text		VAT code	yes
Rate	Number	`0.00`	VAT rate	yes
Description	Text		Description	no
CountryCode	Text		ISO country code	no

Projects

Column	Type	Remark	Required
Mandant	Text	External ID of the organization unit	yes
Number	Text	Unique project number	yes
Name	Text	Name	yes