For the migration of your data, ZEP provides Excel templates, one each for articles, receipts, absences, customers, employees, projects, project times and tickets. You enter your data into these templates, and the ZEP team then carries out the import.
Note: ZEP runs the import for you. You prepare the workbooks and send them to your contact at ZEP. There is no separate menu item for this import in the ZEP interface.
Product line availability
The data migration is available regardless of the product line.
Note: The import templates can be used in ZEP Clock, ZEP Compact and ZEP Professional. Individual sheets and fields do require add-on modules, for example Locations & Departments for the department assignment, Freelancers for daily rates, Export for Accounting for cost units and the Ticketing System for the ticket template. Without the respective module, leave the fields concerned empty.
Course of the data migration
The procedure is the same in every project and consists of four steps.
You receive from ZEP the templates you need for your scope.
You enter your data and follow the rules described in this article.
You send the completed workbooks to your contact at ZEP.
ZEP checks the data, imports it and comes back to you with any questions.
The templates can be used again later to add or correct data. In most projects, however, it stays with the one-off initial filling at the start.
Structure of the templates
Each template is an Excel workbook with one or several sheets. The first sheet carries the master data, the further sheets add details such as addresses, prices or additional attributes.
Mandatory fields are recognisable by a column heading set in bold.
Explanations sit in comments on individual column headings. A red corner at the top right of the header cell points to one.
Sheets you do not need may stay completely empty.
Partly filled sheets, in contrast, need all mandatory fields of that sheet in every filled row.
Unchanged structure
The importer expects the workbook exactly as you received it. It recognises the sheets and columns by their name and by their position.
Do not delete any rows, columns or sheets, not even when you do not need them. Leave them empty instead.
Do not rename sheet names or column headings.
Do not add columns or sheets of your own.
Do not change any cell formats, in particular not the date or number format.
Note: Changed cell formats are the most frequent reason for queries. A date that Excel stores as text is read differently by the importer than a real date. So simply enter values into the existing cells without adjusting their formatting.
System fields
Some columns belong to the system rather than to you. They stay empty when you create new records.
ID is filled by ZEP during the import. You enter an existing ID only when you want to change or delete an existing record.
Aktion stays empty. Only to delete an existing entry do you enter delete there.
created and modified appear in the export only. For the import they stay empty.
Completeness of the master data sheets
In the templates for projects, customers and employees, the first sheet with the master data has to be filled in completely for every import. The importer treats this sheet as the complete current state and not as a list of changes.
Warning: If an entry that was present in the last import is missing in a later one, it is overwritten or deleted. Always enter the complete record, therefore, not only the changed fields. This also applies to fields that are not marked as mandatory, such as the name, email address or address of an employee.
Order of the import
If you use several templates at the same time, the order matters. The later templates refer to data from the earlier ones, for example a project time to a project and an employee. ZEP therefore imports in this sequence.
Employees
Customers
Projects
Project times
Absences
Articles
Receipts
Tickets
Entries per template
The following sections name the mandatory fields per template and those fields that experience shows lead to queries. Column and sheet names are given exactly as they appear in the template, which is why they are in German.
Articles
Mandatory fields are Nummer, Bezeichnung, Beschreibung, Einheit and Einzelpreis.
Inaktiv is filled in only when the article is no longer to be used. If the field stays empty, the article counts as active.
Neue Nummer serves exclusively to change an existing article number, not to create a new one.
Receipts
The template has two sheets. In the Belege sheet, Nr, Userid, ProjektNr, Belegart, Zahlart, Datum and Waehrung are mandatory.
Netto states whether the amount entered is net or gross.
fakt. Netto states the same for the amount invoiced to the customer. The two entries may differ from each other.
Anhang and Dateiname are needed only when you supply a receipt document. Enter the name and the storage path of the file there.
In the Belegbetraege sheet, Nr, Steuersatz, Menge, Betrag, privater Anteil and fakt. Betrag are mandatory. Id and Aktion are needed only for changes to and deletions of existing items.
Absences
Mandatory fields are Userid, Startdatum, Endedatum, Fehlgrund and von.
halber Tag is entered with j or n. If the field stays empty, ZEP assumes a full day.
genehmigt is likewise entered with j or n. If it stays empty, the absence counts as approved.
von and bis expect times in the format hh:mm, for example 08:00.
Customers
The Kunden master data sheet requires only the KundenNr as a mandatory field but has to be complete for every import. Landkennung expects one of three entries: Inland, EU or Drittland.
The further sheets add to the customer.
Adressen with KundenNr and Standard as mandatory fields. Elektronische Rechnung expects a number: 0 for no electronic invoice, 1 for ZUGFeRD1, 2 for ZUGFeRD2, 50 for XRechnung and 100 for the Swiss QR code. As soon as you use an electronic invoice, the two-letter Ländercode is mandatory, for example DE, AT or CH.
Ansprechpartner with KundenNr and Name. As soon as you give an address, Namenzeile becomes mandatory as well. Separate several Kategorien with a comma.
Preisetabellen and Preise with KundenNr and Gueltig Ab, PreisFaktoren and Zusatzfelder with the KundenNr.
KundenVerantwortliche with KundenNr and Kundenverantwortlicher. The entries primär, darf ändern and Budgetverantwortung are given as yes-no values.
Zusatzattribute without mandatory fields.
Employees
In the Mitarbeiter master data sheet, Userid and Preisgruppe are mandatory. The completeness rule applies here as well for every repeated import.
Rechte expects a number: 0 for a normal user, 1 for an administrator, 2 for a controller, 3 for a user with additional rights and 4 for a project controller.
Freelancer expects 0 for permanently employed, 1 for a freelancer and 2 for a freelancer with credit note.
Abteilung is relevant only with the Locations & Departments module. The departments have to exist in ZEP beforehand. Kostentraeger requires the Export for Accounting module.
Sprache either stays empty or contains de, en or fr.
The further sheets map the history.
Beschaeftigungszeitraeume with Userid as the mandatory field. An empty Startdatum is set by ZEP to the import date, an empty Endedatum means an open-ended employment.
Regelarbeitszeiten with Userid and Startdatum. The columns Monday to Sunday carry the working hours per day, where -1 or an empty field stands for no working day. Set Dyn. Verteilung to true when ZEP is to distribute the monthly target hours itself.
Interne Stundensätze with Userid, Gültig ab, Satz and Typ. The Typ 1 stands for an hourly rate, 2 for a daily rate. Daily rates require the Freelancers add-on module.
Abgeglichene Zeiten with Userid, Monat and Jahr. The Monat is a number from 1 to 12.
Projects
In the Projekte master data sheet, ProjektNr, Start, Status and Abrechnungsart are mandatory. The completeness rule applies here too.
KundenNr is filled in only for a customer project. For internal projects the field stays empty. Ende may stay empty when the project has no end date yet.
Abrechnungsart expects 1 for effort by hours, 2 for effort by days and 3 for a flat rate.
DefaultFakt expects 1 for billable and changeable, 2 for billable and not changeable, 3 for not billable and changeable and 4 for not billable and not changeable.
Ueberbuchung expects 0 for do not prevent, 1 for billable times only and 2 for all times.
Belege - Fakturierung expects 0 when the amount is entered during capture, 1 for adopting the default and 2 for not adopting it. Anreisepauschale expects 0 per employee and day and 1 per employee and journey. Kilometergeld and Verpflegungsmehraufwendungen expect 0 for do not bill, 1 for billable times only and 2 for all times.
The further sheets describe the structure of the project.
Projekt-Mitarbeiter with ProjektNr and Userid. PL expects 0 for no project management, 1 for project management with budget responsibility and 2 for one without. von and bis may stay empty, in which case the project start and end apply. Verfügbarkeit is a value between 0 and 100 per cent.
Vorgaenge with ProjektNr, VorgangNr, Status and Abrechnungsart. The codes correspond to those of the project sheet, and 0 additionally stands for as project. Parent is filled in only for a subordinate task. Belegerfassung expects 0 for not possible and 1 for possible.
Projekt-Taetigkeiten, Projekt-Orte and Vorgangs-Taetigkeiten with the respective number and the designation.
Preisetabellen, Preise, PreisFaktoren, Zusatzfelder and Projekt-Tagessatzanteile with the ProjektNr.
Projekt-Schlagworte with ProjektNr and the German keyword. English and French are optional.
Project times
Mandatory fields are Userid, Datum, von, bis, ProjektNr, VorgangNr and Taetigkeit.
Dauer does not have to be filled in as long as von and bis are set. ZEP calculates it from them.
fakturierbar and privat are entered with j or n.
Reisetyp is filled in only for travel times, with hin, weiter or rueck.
Tickets
In the Tickets sheet, Ticket, Betreff, Status, ProjektNr, VorgangNr and Ersteller are mandatory. The ticket number has to be given in any case.
Status expects 1 for new, 2 for in progress, 3 for done, 4 for rejected and 5 for accepted.
Priorität is a number from 1 to 5.
Externe Ticketnummer is filled in only when the ticket is additionally kept in another system.
The further sheets map the history: Historie, TicketÄnderungen, TicketAnhänge and Teilaufgaben. All of them require the Ticket number plus the entries that make sense for the sheet, such as Bearbeiter, Datum or Betreff. The Status of a subtask uses the same codes as the ticket.
Questions about the data migration
If you are unsure whether a field is relevant for your case, leave it empty and point your contact at ZEP to it. An empty field can be supplied later, whereas a misunderstood entry often shows up only after the import.
Tip: After the import, check a sample in ZEP, in particular for projects with many tasks and for employees with several employment periods. That way deviations stand out while the connection to the template is still fresh.