PDF zu Excel – ohne „AI“ und kostenlos
Auf den ersten Blick hat dieser Artikel wenig Bezug zum übergeordneten Thema dieses Blogs. Dass es sich trotzdem lohnt, kleine Helferlein zu kennen, um repetitive Aufgaben zu automatisieren, möchte ich kurz anhand eines praktischen Beispiels illustrieren.
Konkreter Hintergrund ist bei mir, dass ich vor einigen Jahren das Ziel hatte, ein passives Einkommen mit Fotos zu erzielen, die im Rahmen meiner Reisen entstanden sind. Ich habe dafür 2018/2019 einige Dutzend Fotos bei einem Stock-Archiv EyeEm hochgeladen; sie wurden auch häufiger gekauft und haben auf diese Weise tatsächlich über die Jahre den einen oder anderen Urlaubsflug refinanziert.
Soweit so gut. Nicht so prickelnd waren allerdings die damit verbundenen buchhalterischen Aufgaben. Für jeden Kauf erstellt die Bilderbörse EyeEm nämlich eine eigene Gutschrift, deren Betrag trotz des Berliner Firmensitzes in USD (why?!) ausgewiesen wird. Für die Jahre 2021 und 2022 ergaben sich aufgrund vieler Kleinst-Lizenzen für TikTok im Cent-Bereich auf diese Weise rund 600 Gutschriften, die ich händisch in eine Excel-Liste hätte überführen und außerdem die Tageswechselkurs für jede Gutschrift ermitteln müssen.
No fucking way, dachte ich mir und habe zunächst nach Online-Tools geschaut, die das für mich erledigen können. Schließlich leben wir im Zeitalter von ChatGTP – da müsste so etwas doch eine Kleinigkeit sein. Und ja – es gibt diverse Anbieter (docparser, parsio.io, pdfTables, natif.ai, et cetera), die tatsächlich mit dem Einsatz von AI zur Umwandlung von PDFs in Excel-Tabellen werben. Für meine 600 Seiten hätte mich der Spaß aber so um die 100 EUR gekostet und die Währungsumrechnung hätte ich trotzdem selbst erledigen müssen.
Thanks, but no thanks. Dann machen wir das eben doch selbst.
PDF zu Excel bitte einmal kostenlos
Ich habe mir also ein Script geschrieben, dass die PDF-Daten parsed, die amtlichen Wechselkurse ermittelt und alles fein säuberlich in eine Liste schreibt. Und weil dieser Ansatz auch zur Stapelverarbeitung börsenbezogener Dokumente (z.B. Order-Belegen, 10k Filings, etc.) geeignet ist, habe ich ihn hier einmal etwas ausführlicher dokumentiert.
Jedes Dokument ist wie folgt strukturiert und mich interessierten die farbig hervorgehobenen Felder:
Ziel war eine Überführung dieser Daten in folgendes Format:
Für die technische Umsetzung hat sich nach kurzer Recherche ein Python-Script unter Einsatz von PDFQuery angeboten.
Zuerst musste ich mir dafür pip, einen Package Manager für Python-Bibliotheken, installieren:
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
python3 get-pip.py
Anschließend konnte ich dann mittels pip die erforderlichen Packages installieren – allem voran PDFQuery selbst, Pandas als universelle Bibliothek für Datenverarbeitungsaufgaben und den CurrencyConverter zur Währungsumrechnung.
pip install pdfquery
pip install pandas
pip install currencyconverter
PDFQuery benötigte nun jeweils die grafische Zone, in der eine gewünschte Information hinterlegt ist. Diese Zone wird durch Box festgelegt, die aus den Koordinaten X1,Y1 (unten links) sowie X2,Y2 (oben rechts) besteht:
pdf.pq('LTTextLineHorizontal:overlaps_bbox("x0, y0, x1, y1")').text()
Diese Koordinaten konnte ich ermitteln, in dem ich mir zuerst einmal eine XML-Repräsentation einer Seite speichern ließ:
pdf = pdfquery.PDFQuery(DATEI)
pdf.load()
pdf.tree.write('pdfXML.txt', pretty_print = True)
Im resultierenden XML konnte ich die Koordinaten der gesuchten Zeichenkette (hier die Credit Note C-A4583B061F86) nun exakt der Reihenfolge ablesen, wie sie von der oben genannten pdf.pq-Methode benötigt wird (hier 467.581, 696.635, 546.091, 706.635).
Aus:
<LTTextLineHorizontal y0="696.635" y1="706.635" x0="467.581" x1="546.091" width="78.51" height="10.0" bbox="[467.581, 696.635, 546.091, 706.635]" word_margin="0.1"><LTTextBoxHorizontal y0="696.635" y1="706.635" x0="467.581" x1="546.091" width="78.51" height="10.0" bbox="[467.581, 696.635, 546.091, 706.635]" index="9">C-A4583B061F86 </LTTextBoxHorizontal></LTTextLineHorizontal>
wird im Script also:
invoice_nr = pdf.pq('LTTextLineHorizontal:overlaps_bbox("467.581, 696.635, 546.091, 706.635")').text()
Nun fehlte nur noch die Währungsumrechnung und hier macht es einem das CurrencyConverter-Package wirklich denkbar einfach (Beispiel):
from datetime import date
from currency_converter import CurrencyConverter
c = CurrencyConverter()
c.convert(100, 'EUR', 'USD', date=date(2021, 3, 9))
Damit hatte ich nun alles Wesentliche zusammen. Noch etwas Ausgabeformatierung, ein paar (sehr grobe) Validierungen und der Loop, um multiple PDF-Seiten zu einer vielzeiligen CSV-Datei zusammenzufügen. Ich bin kein Python-Entwickler, manches ist vermutlich umständlich oder nicht so belastbar, aber: It gets the job done.
Hier also das vollständige Script:
import sys
import csv
import pdfquery
import pandas as pd
import locale
from currency_converter import CurrencyConverter
from datetime import datetime
from datetime import date
c = CurrencyConverter()
locale.setlocale(locale.LC_ALL, 'de_DE')
def pdfscrape(pdf):
# To make it easier to identify the coordinates in the calculation, it is recommended to first convert the entire PDF page into
# an XML structure using the tree method. There, the coordinates for each text can then be read quite conveniently.
#pdf = pdfquery.PDFQuery(pdf_path)
#pdf.load()
#pdf.tree.write('pdfXML.txt', pretty_print = True)
invoice_nr = pdf.pq('LTTextLineHorizontal:overlaps_bbox("467.581, 696.635, 546.091, 706.635")').text()
invoice_date = pdf.pq('LTTextLineHorizontal:overlaps_bbox("467.581, 728.304, 519.401, 738.304")').text()
image_id = pdf.pq('LTTextLineHorizontal:overlaps_bbox("115.803, 554.483, 174.823, 564.483")').text()
amount_net = pdf.pq('LTTextLineHorizontal:overlaps_bbox("517.491, 494.07, 546.091, 504.07")').text()
amount_vat = pdf.pq('LTTextLineHorizontal:overlaps_bbox("433.1, 465.07, 482.32, 475.07")').text()
amount_tax = pdf.pq('LTTextLineHorizontal:overlaps_bbox("517.491, 465.07, 546.091, 475.07")').text()
amount_gross = pdf.pq('LTTextLineHorizontal:overlaps_bbox("514.501, 436.07, 546.091, 446.07")').text()
if( (len(amount_net) > 0) and (len(amount_vat) > 0) and (len(amount_tax) > 0) and (len(amount_gross) > 0) and (len(invoice_date) > 0)):
invoice_date_obj = datetime.strptime(invoice_date, '%m/%d/%Y')
amount_usdeur = c.convert(1, 'USD', 'EUR', date=date(invoice_date_obj.year, invoice_date_obj.month, invoice_date_obj.day))
amount_eur = float(amount_gross.replace('$ ', '')) * amount_usdeur
page = pd.DataFrame({
'invoice_nr': invoice_nr,
'invoice_date': str(invoice_date_obj.day) + '.' + str(invoice_date_obj.month) + '.' + str(invoice_date_obj.year),
'image_id': image_id,
'amount_net': locale.currency(float(amount_net.replace('$ ', '')), symbol=False, grouping=True),
'amount_vat': amount_vat.replace('VAT ', '').replace('.00', ''),
'amount_tax': locale.currency(float(amount_tax.replace('$ ', '')), symbol=False, grouping=True),
'amount_gross': locale.currency(float(amount_gross.replace('$ ', '')), symbol=False, grouping=True),
'amount_usdeur': locale.format_string("%.4f",amount_usdeur),
'amount_eur': locale.currency(amount_eur, symbol=False, grouping=True),
}, index=[0])
print("Processing invoice " + str(invoice_nr))
return(page)
else:
print("An error occured on invoice " + str(invoice_nr))
def convert_to_csv(pdf_path, csv_path):
pdf = pdfquery.PDFQuery(pdf_path)
pagecount = pdf.doc.catalog['Pages'].resolve()['Count']
master = pd.DataFrame()
for p in range(pagecount):
pdf.load(p)
page = pdfscrape(pdf)
if(len(page) > 0):
master = pd.concat([master, page])
master.to_csv(csv_path, index = False)
if len(sys.argv) > 1:
pdf_path = sys.argv[1].lower()
csv_path = pdf_path.replace('.pdf', '.csv')
convert_to_csv(pdf_path, csv_path)
else:
print("Please provide a PDF file as argument.")
Nicht in allen Fällen weisen PDFs ein so gleichförmiges Layout auf, wie meine Belege. Um etwas mehr Flexibilität in die Verarbeitung zu bringen, bietet PDFQuery eine schöne Methode, um den Scope einzugrenzen, aus dem Daten extrahiert werden sollen.
Angenommen, die EyeEm-Belege hätten eine variable Anzahl von verkauften Lizenzen pro Seite, sodass wir die Y-Position der Summen nicht fix festlegen können. Hier würden wir zunächst ein Schlüsselwort suchen, dass wir als verlässlichen Ausgangspunkt nutzen können und die Extraktions-Koordinaten relativ zum Schlüsselwort platzieren:
reference = pdf.pq('LTTextLineHorizontal:contains("{}")'.format("Total Credit Amount"))[0]
left = float(reference.get('x0',0)) + 100 # Verschiebe 100 Punkte nach rechts
bottom = float(reference.get('y0',0)) # gleiche Höhe
right = float(reference.get('x1',0)) + 200 # Verschiebe 200 Punkte nach rechts
top = float(reference.get('y1',0)) # gleiche Höhe
total = pdf.pq('LTTextLineHorizontal:overlaps_bbox("%s, %s, %s, %s")' % (left, bottom, top, right)).text()
Bildnachweis: iStockPhoto / AzmanJaka