How travel expense reports get to DATEV with booking data and receipt images: CSV, XML or data service, with an example trip and file structure. After the travel expense report, the costs have to go into financial accounting and the reimbursement to the employees. If your tax advisor works with DATEV, booking data and receipt images should arrive there without retyping. This article shows the four ways to DATEV using an example trip and explains the structure of the files for those who are interested. Which way suits you is best decided together with your tax firm: if it does the bookkeeping itself and only needs the data, the CSV file is enough. If the receipts should also arrive digitally, XML, CSV with XML or a data service are suitable. The data service also saves passing files on. For payroll there are separate exports for DATEV LODAS and DATEV Lohn und Gehalt. Many people look for an Excel or PDF template or a form for travel expense reports that fits DATEV. Such a template helps with filling in. However, accounting then has to transfer the figures into DATEV by hand, because DATEV only reads booking data in DATEV format or via the data services. Travel expense software creates both directly from the report, with the current allowances and the accounts from your chart of accounts. Figure 2 below shows what a finished travel expense report looks like as a PDF. How to create a report step by step is explained in the guide Create travel expense report. If you have used DATEV Reisekosten classic so far, you will find information in the article on the discontinuation of DATEV Reisekosten classic.DATEV export of travel expenses
The four ways to DATEV at a glance
Way
What is transferred
Receipt images
Where it arrives
CSV file (DATEV format)
Booking records
no
Imported by the tax firm into DATEV Rechnungswesen
XML file
Receipt data with receipt images
yes
DATEV Unternehmen online via DATEV Belegtransfer
CSV and XML (ZIP file)
Booking records, linked receipt images
yes
DATEV Rechnungswesen and Unternehmen online
DATEV data services
Booking records or receipt data with receipt images
yes
automatically online to DATEV
Is there a DATEV template for travel expense reports?
An overview of the different file formats based on a travel expense report
In Germany, certain tax regulations apply to the settlement of travel expenses. There are some software solutions for travel expense reporting on the market that help to apply these regulations correctly. After that, the travel costs and expenses must be transferred to the financial accounting system and paid to the employees. However, if the data obtained has to be manually transferred to financial accounting after the payroll has been prepared, the fun stops.
To avoid these so-called “media breaks”, there are interfaces from the travel expense software solutions to various accounting systems. DATEV can still be described as the “number one” among tax advisors. Therefore, this article explains the different possibilities how to transfer the data from a software for travel expenses, using the example of Spesenfuchs, to DATEV.
A concrete example is used to run through all three possibilities. The structure of the files is explained in detail.
Products
First, we will very briefly introduce the products that we will be talking about below:
Kanzlei-Rechnungswesen is a software from DATEV for tax advisors, with which they can handle all activities from financial accounting to annual financial statements.
DATEV document transfer is a free tool from DATEV for sending (by the tax advisor) document data from the client to the DATEV Data centre.
DATEV Unternehmen Online, called DUO, replaces the “commuting folder”. Companies can send their receipts to the tax advisor via this online application. Here it is possible to send also accounting data such as amounts, descriptions and accounts.
Spesenfuchs is an online software solution for travel expense reporting.
Overview
By way of introduction it can be said that there are 3 (or 4) ways to bring the data to DATEV:
- CSV file that is imported into Kanzlei-Rechnungswesen
- XML file that is imported into DATEV Unternehmen online
- CSV file in combination with an XML file (mixture of 1. and 2.)
- Transfer of data from 2. through an online API (automated).
Example situation
The following data is taken as a basis for the examples: the self-employed pool builder Sepp Elmann goes on a one-day business trip from Karlsruhe to Freiburg. Departure 6:00, return the same day at 23:00. He submits a receipt for entertainment.
His statement of account is as follows:
While the statement is clear for the business traveler, it does not include the accounts for the bookings. For this there is a booking list for this trip, which contains all bookings.
This posting list lists all the necessary posting records to correctly post the travel expenses, including VAT specials, etc. While this list makes it easy to transfer the entries to accounting, it is of course tedious. Wouldn’t it be nice to be able to transfer the whole thing directly to the accounting software? Here we are at the actual topic of this article, the interfaces.
The CSV interface
The CSV interface is the oldest of the DATEV formats. With the CSV interface no document images can be transferred to Kanzlei Rechnungswesen. This format is also supported by many other programs and can therefore be considered almost a de facto standard. It is only one file that starts with EXTF_ and ends with .csv.
First line: The first line always has the same structure and contains information for the entire file, such as the DATEV consultant number and the DATEV client number.
Second line: The second line of the file also always has the same structure and provides the headings for the following data lines.
N data lines: One line per posting record. More than 120 columns, not all of which need to be filled in our case.
The following image shows the CSV file. In the second line are the headings for the posting lines.
The way of the data is simple: the user sends the file to the tax advisor and the latter reads it directly into Kanzlei Rechnungswesen. As described above, the disadvantage is that no document images can be transferred this way.
The XML interface
The DATEV XML interface is an XML-based file interface for importing data to DATEV Unternehmen online. With this file format, the entries can be transferred with both records and the corresponding document images. These files are imported via the desktop application DATEV Belegtransfer.
A set of files for the bookings from our example contains the following files for the XML interface:
These files are discussed in detail below.
Adocument.xmlfile
Further, the set of files contains N files with the extension .xml. The number of files depends on how the manufacturer of the software that generates them divides the data. In the case of Spesenfuchs, one file is created per receipt and per trip, and another file is created for the trip and its per diems and, if applicable, the deductions for meals. So, in our example, there are 3 files, one for the trip with its additional meal expenses, one for the trip, and one for the receipt. These files are structured as follows:
Other files are all receipt images and other files, in our example the travel expense report of the trip (already shown above, Figure 2) and the receipt for the hospitality.
If you now take a closer look at the files, you will notice that the document.xml is something like a table of contents for the entire package. The document.xml establishes the connection between the individual .xml files and the document images. We will look at this in more detail using the example of the receipt for hospitality.
In the document.xml there is this section:
By this the picture for the hospitality with the file name 202009071523-316261422-1.png can be assigned to the booking data in the file SPF_1_559_002.xml during the import.
The file SPF_1_559_002.xml contains the posting data for this document (upper area omitted for clarity):
...2020–08–31 19.70 6640 8 EUR Bewirtungskosten, Mahlzeit, Einladung Kunde nach erfolgreich Bewirtungskosten 70001 2020–08–31 2020–08–31 12.60 6640 9 EUR Bewirtungskosten, Getränke, Einladung Kunde nach erfolgreich Bewirtungskosten 70001 2020–08–31
The same applies to the other sections or files.
If negative amounts were necessary, such as a meal reduction, they would receive a minus.
If now a document without image would be entered, one would have to transfer a dummy image, otherwise a transfer of the booking data with this interface format would not be possible.
The data is sent from the user to the tax advisor, from there via the DATEV document transfer app to DATEV Unternehmen online and from there to Kanzlei Rechnungswesen.
The XML+ CSV interface
This interface is a mixture of the two XML and CSV described above. However, both XML and CSV contain other data in certain places.
A set of files for the postings from our example contains the following files for the XML + CSV interface:
The zip file shown in Figure 11 is sent to the tax consultant without unpacking it. Their content is similar to the XML interface but not the same:
Since the booking data is taken from the CSV file, there is no need for the dummy image. Only the document images that are actually required are sent via the XML interface. This also eliminates all data about the documents in the XML file. The XML file is therefore much smaller. There are no XML files other than the document.xml file.
Also with this interface format the document.xml file represents a kind of table of contents.
But first we have to look at the CSV file: In the CSV file there is also a difference to the pure CSV interface: in the column “document link” the UUID or also GUID, i.e. a unique identifier is listed, which is also used in the document.xml. This allows to establish the connection between the images and the bookings in the CSV file.
To explain the hospitality receipt in this example as well, let’s look at this area in the XML file:
If the guid’s of the two lines in the CSV file for the hospitality are compared with these two entries, one recognizes the connection that is made here and links the document screens with the bookings when reading in via DUO or Kanzlei-Rechnungswesen.
The path of the data is as follows:
The CSV file is read by the tax advisor directly into Kanzlei Rechnungswesen. The XML file is imported by the tax advisor via the app DATEV Belegtransfer into DATEV Unternehmen online and from there into Kanzlei Rechnungswesen. The UUID creates a link between the data.
DATEV data services (online)
Instead of passing files on by hand, software can also send the data online directly to DATEV. Manual handling is no longer necessary. Today these ways are called DATEV data services:
- DATEV Rechnungsdatenservice 1.0: transfers receipt images and structured receipt data to DATEV Unternehmen online, similar to the XML interface described above. Booking then takes place in DATEV Rechnungswesen.
- DATEV Buchungsdatenservice: transfers finished booking records together with the receipt images. The receipt images end up in DATEV Unternehmen online, the booking data in the DATEV data centre.
Once a data service is set up, Spesenfuchs transfers approved expenses automatically every night. Requirements and setup are described on the page DATEV Data Service.
Summary
Basically, the facts are not complicated. But even we at Spesenfuchs had to understand the differences first when we implemented the interfaces. Therefore this contribution.
Further, we would like to give our users a more detailed overview of the export options to better decide which format to use. You can create all three file formats in the Spesenfuchs export.
Links to the topic
https://www.datev-community.de moderated forum from DATEV
https://developer.datev.de/portal/ Developer Portal from DATEV (free login required)
DATEV export with Spesenfuchs
For travel expense and expense reports in Spesenfuchs, you choose your chart of accounts (SKR03, SKR04, SKR42 or your own chart of accounts); the accounts for the receipt types are already preset. After approval, you export the booking data as a DATEV file, with receipt images if required, or have it transferred automatically every night via the DATEV data service. In the Spesenfuchs export you choose between booking data only (CSV), booking data with receipt images (CSV with receipt images) and receipt data with receipt images (XML with receipt images). More on the page DATEV export.
More guides: Create travel expense report, Post entertainment receipts correctly.
Related features for the DATEV export
All features at a glanceCompare PRO and TEAM in detailIntegrations
