Open a DATEV export (EXTF CSV) in Excel without breaking it
The German subsidiary closes the month, and the group gets a file called something like EXTF_Buchungsstapel_2024.csv. It is the export format of DATEV, the software almost every German tax adviser (Steuerberater) uses. On a double-click Excel turns it into a mess: one wide row of codes at the top, umlauts as symbols, dates like 302, amounts that will not add up. The data is fine — Excel just does not know the format.
Free to use. The file is read in your browser and never uploaded. Windows version — 4.9 MB, no installation: details. Pro — $29 once: price.
What the first line is
A DATEV file (DATEV-Format) is a semicolon-separated CSV with one extra line on top: the header record. It describes the whole batch, and the column names only come on the second line. A typical header looks like this:
"EXTF";700;21;"Buchungsstapel";13;20240130140440439;;"RE";"Admin";"";29098;55003;20240101;4;20240101;20240831;…;"EUR";…;"03";…
| Field | Example | Meaning |
|---|---|---|
| 1 | EXTF | file from or for a non-DATEV program; DTVF is DATEV’s own export |
| 2 | 700 | version of the header format |
| 3–4 | 21, Buchungsstapel | data category: 21 is a batch of postings (Buchungsstapel), 16 is customer and supplier master data (Debitoren/Kreditoren), 20 is the account names (Kontenbeschriftungen) |
| 11 | 29098 | tax adviser number (Berater) |
| 12 | 55003 | client number at that adviser (Mandant) — in practice, which company this is |
| 13 | 20240101 | start of the fiscal year (Wirtschaftsjahr-Beginn) |
| 14 | 4 | length of general ledger account numbers (Sachkontenlänge) |
| 15–16 | 20240101, 20240831 | period the batch covers (Datum vom / Datum bis) |
| 22 | EUR | currency |
| 27 | 03 | chart of accounts: SKR03 or SKR04 |
Below the header come the column names — Umsatz (ohne Soll/Haben-Kz), Soll/Haben-Kennzeichen, Konto, Gegenkonto (ohne BU-Schlüssel), BU-Schlüssel, Belegdatum, Belegfeld 1, Buchungstext and a hundred more, most of them empty — and then one posting per line.
Why Excel breaks it
- The header line becomes the header. Excel takes line 1 as the column names, so filters and tables are built on “EXTF”, “700”, “21”, while the real names sit in row 2 as data.
- Umlauts turn into symbols. DATEV writes Windows-1252 (ANSI). Excel on a Mac, or any import set to UTF-8, shows “Müller” or “M�ller” instead of “Müller”. See CSV encoding.
- Everything lands in column A. Outside the German-speaking countries Excel expects a comma between fields, not a semicolon. See CSV opens in one column.
- Amounts stop being numbers. “1190,00” uses a decimal comma. With English regional settings Excel either keeps it as text, so SUM ignores it, or reads the comma as a thousands separator and you get 119000.
- Belegdatum loses its zero and has no year. The document date is stored as day and month only — “0302” is 3 February. Excel turns it into the number 302, and nothing in the row says which year it belongs to: the year lives in the header line.
- No signs on amounts. Every amount is positive; whether it is a debit or a credit is in the separate column Soll/Haben-Kennzeichen — S (Soll, debit) or H (Haben, credit). A plain sum of the amount column means nothing.
And never save the file back from Excel and send it on: quotes, dates and encoding all change, and DATEV rejects the import.
What happens when you open it here
The app recognizes the EXTF or DTVF signature at the start of the file and reads it as DATEV:
- the header line is skipped, and the column names are taken from the second line;
- the encoding is Windows-1252, or UTF-8 if the file has a BOM; umlauts and ß come out right;
- the delimiter is the semicolon, and amounts with a decimal comma become numbers;
- Belegdatum gets its year from the header: the year the period starts, or the next one if the day would fall before the start — so a fiscal year from July to June comes out right. A date Excel already damaged to “302” is read as 03.02 too. Other date columns in the DATEV form DDMMYYYY (for example Leistungsdatum) become dates as well;
- a column Signed amount (S+ / H−) is added at the end: the amount with a plus for Soll and a minus for Haben;
- the header details — data category, Berater, Mandant, period, fiscal year start, currency — are shown in the notes of the File and fields panel.
Customer and supplier lists (Debitoren/Kreditoren) and account names open the same way; they simply have no amounts, so no signed column. Postcodes such as 01067 keep their leading zero.
Checking the postings
Each line is one posting: the amount goes to Konto (account) on the side given by S or H, and to Gegenkonto (contra account) on the other side. The signed column is from the point of view of Konto.
- Everything on one account. In the filter bar:
Konto = 1200 OR [Gegenkonto (ohne BU-Schlüssel)] = 1200— 1200 is the bank account in SKR03. - Movement per account. “Grouping and totals…” by Konto with the sum of the signed column. Remember the contra side: for a full account balance the same postings count with the opposite sign where the account appears as Gegenkonto.
- Tax codes. BU-Schlüssel (posting key) mostly carries the VAT treatment — in SKR03, 9 is 19% input VAT and 3 is 19% output VAT. Group by BU-Schlüssel to see which codes are used and how much goes through each; an empty key on a revenue account is worth a question.
- Dates outside the period. Sort by Belegdatum: the first and last rows should lie between Datum vom and Datum bis from the header.
- Document references. Belegfeld 1 is usually the invoice or receipt number; “Duplicates” on it finds documents posted twice.
Getting it into Excel
“Export…” → Excel writes a workbook in which Belegdatum is a real date, amounts and the signed column are numbers, and the German text is intact. Filter first if you only need one account or one month: the export takes the rows on screen. A pivot table by account and month can be built and exported the same way.
The DATEV file itself is opened read-only. The app does not write the DATEV format back — corrections go through the program that produced the export, or through the tax adviser.
FAQ
Does the file go to a server?
No. It is read in your browser tab, and the page technically cannot send the file’s contents to any other address. For ledger data of a subsidiary that is usually the point.
What if the header has no period?
Then the year is taken from the fiscal year start. If neither is there, Belegdatum is left as it is in the file, DDMM without a year, and a note says so — the app does not invent a year.
Does it work with DTVF files and master data?
Yes. DTVF and EXTF headers are read the same way; the data category and its name are taken from the file itself, so Debitoren/Kreditoren, Kontenbeschriftungen and other categories open with their column names from line two.
The amounts look wrong in Excel even after import. Why?
Most likely the decimal comma: “1190,00” read with English settings. Either import with German locale settings in Excel’s text import, or export from here, where the numbers are already numbers.
Free to use. Your file is not uploaded to a server. Windows version — 4.9 MB, no installation: details. Pro — $29 once: price.