How to format your import file

Introduction

Learn: How the import file with expenses for TimeLog is configured in a financial system.

Description

In the financial system, this import file is created as an export file. Here we describe the requirements necessary for TimeLog to read the file and import its expense data.
The help text Purchases/expenses import describes how the file is managed and imported.

File format

The import file must be a .txt or .csv file And the columns must be separated by either semicolon (;), comma (,) or tabulator (TAB).

Import file fields

To read the import file, each row must include 11 fields in a specific order. Also, the fields require specific formatting. Please note that the field names in the financial system need not match the destination field names in TimeLog, and that they will be rearranged in TimeLog as well.

The data fields may not contain semicolons, commas or tabulator shifts, as they are used to separate the fields. In other words, each row must contain ten separators. The fields may not contain line breaks either, as they are used to end an expense. If each row does not contain 11 fields, the following message will be shown in TimeLog upon trying to import the file:

The following import rows do not contain the required number of columns (11):

This message is followed by the pertinent rows in the import file. This error also occurs if some of the fields contain a separator.

Field	No.	Formatting requirements	Required content
Date	1	Date of purchase. The format is dependent of the format selected when importing and an example is shown on the import page.	x
Expense No.	2	The financial system’s unique identifier upon import. Please note that this may not exceed 100 characters.	x
Account No.	3	The account number found in the financial system, used to identify the expense type in TimeLog.
Supplier No.	4	In TimeLog, the supplier number must be created as a customer number in the customer database, and the customer card must have the Supplier status. If there is no supplier number included in the import file, if the number is wrong or if the customer card does not have a supplier status, the expense will be imported without a supplier.	x
Amount incl. VAT	5	The number is given in the currency used for the purchase. The format is dependent of the format selected when importing and an example is shown on the import page.	x
VAT	6	In percent, but without percentage sign (e.g. 25). In TimeLog, VATs are converted into an amount in the same currency as the amount. If no VAT rate is given, the amount is simply “0”.
Comment	7	Comments for the expense. This text must not contain semicolons, commas, tabulator shifts or line breaks.
Currency code (¤)	8	The currency used for the payment. Format: Three-letter abbreviations (e.g. DKK for Danish kroner, EUR for euro, USD for American dollars etc.). A corresponding, active code must exist in the TimeLog currency settings.	x
Exchange rate	9	The exchange rate for the currency used for the payment. Format: xxx (e.g. 750, if 100 units in euro equal 750 Danish kroner).	x
Project No.	19	The project number found in the import file will be converted to a project name if it exists in TimeLog. If not, the user will be asked to select a project for the expense when importing.
Invoice No.	11	Invoice no. 11 Invoice number for the invoice with the expense, as found in the financial system. There are no requirements related to the format of the invoice number, however, it may not exceed 50 characters.