excel-to-sql

Excel-Dateien in SQL-Datenbank übertragen mit Python und Raspberry Pi

Im Büroalltag ist Excel seit Jahrzehnten das bevorzugte Programm, um Daten zu speichern und zu verwalten. Irgendwann hat jede Abteilung ihr eigenes System und unzählige Listen auf verschiedenen Datenträger liegen. Möchte man jetzt im Zuge der Digitalisierung die gesammelten Daten einheitlich auf einen SQL-Server bringen, kann das sehr mühsam werden. Werkzeuge wie “phpMyAdmin” erlauben zwar den Upload von CSV-Dateien, aber auch das funktioniert nicht immer fehlerfrei. Sollen außerdem noch zusätzliche Werte hinzufügt werden, wie z.B. eine Firmen-ID, so wird das Projekt schnell zur Mammutaufgabe. Mit Python ist das alles sehr einfach und kostengünstig machbar und darum soll es in dieser Anleitung gehen.

Natürlich gibt es auch Onlinetools, die Excel-Dateien in eine SQL-Datei umwandeln, aber wer möchte schon Firmendaten auf einen fremden Server laden?! Lieber etwas mehr Zeit in das Projekt investieren und dafür eine hohe Datenqualität erhalten.

In dieser Anleitung werde ich nicht darauf eingehen, wie Raspberry Pi OS installiert wird. Ich arbeite mit einem Windows 10 PC und einem Raspberry Pi 3 auf dem Raspberry Pi OS Lite läuft.

Wir benötigen:

  • Raspberry Pi egal welches Modell
  • Python 2 oder 3
  • Texteditor wie Notepad++
  • SSH Client wie Putty
  • FTP Client wie Filezilla
  • SQL Server

01. SQL Tabelle mit Umlauten

In dieser Anleitung möchte ich auf die Einrichtung eines SQL-Servers gar nicht so genau eingehen. Aber eine Frage kommt immer wieder auf: wie können Umlaute problemlos in einer SQL Tabelle verarbeitet werden?

Die meisten Excel-Listen werden wahrscheinlich auch Umlaute enthalten, daher sollten die Spalten mit Text auch richtig konfiguriert sein. Ich empfehle hier immer die “Kollation” der Spalten auf “utf8_general_ci” einzustellen, damit hab ich noch nie Probleme gehabt.

02. Bibliotheken installieren

2.1 pandas

Zum Lesen der Excel-Dateien nutzen wir das bekannte und umfangreiche Projekt “pandas”. Wir können “pandas” entweder mit “pip” installieren oder klassisch mit “apt-get install”. Leider hat bei mir die “pip” Installation immer wieder nicht funktioniert, daher nutze ich die klassische Variante. Dennoch gebe ich hier beide Befehle an, testet am besten selbst was bei euch am besten funktioniert.

pip install pandas
sudo apt-get install python-pandas

2.2 mysql-connector

Zum Übertragen der Werte in die SQL Datenbank nutzen wir die Bibliothek von mysql-connector. Hier hatte ich noch nie Probleme mit der “pip” Installation, daher gebe ich hier nur diesen Befehl an.

pip install mysql-connector

03. Das Skript

Sind alle Bibliotheken installiert, können wir auch schon mit dem eigentlichen Skript loslegen. In unserem Beispiel wollen wir Daten einer Immobilienfirma in eine SQL-Datenbank übertragen. Die Wohnungen haben noch keine eindeutige Identifikationsnummer bei der Firma, das wollen wir ändern. Wir lesen die Werte der Excel-Liste mit “pandas”, mithilfe einer “for” Schleife und mysql-connector übertragen wir die Werte in unsere SQL-Datenbank. Damit wir zusätzlich eine “buildingid” generieren können, fügen wir noch eine kleine Rechnung hinzu.

Die “buildingid” errechnen wir aus den Daten der Liste, dazu packen wir noch einen Zeitwert in Millisekunden und die Nummer der Schleife dazu, damit die Identifikationsnummern auch wirklich immer einzigartig sind.

#!/usr/bin/env python
# coding: utf8

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import mysql.connector as mariadb
from mysql.connector import Error
from mysql.connector import errorcode
import datetime


# Pfad zur Excel-Datei und Angabe der Tabelle
df = pd.read_excel('/home/pi/excel-to-sql/file.xlsx', sheet_name='Tabelle1', encoding='utf-8')


# Wert zum Erstellen der Gebäude ID
count = 1


# Schleife die alle Daten der Tabelle in die SQL Datenbank übertägt
for i in df.index:



    # Variablen der Excelliste
    ort = df['ort'][i]
    strasse = df['strasse'][i]
    etage = float(df['etage'][i])
    baujahr = float(df['baujahr'][i])
    zimmer = float(df['zimmer'][i])

    # Berechnung der neuen ID
    count += 1
    time = datetime.datetime.now()
    milliseconds  = time.strftime("%f")    
    buildingid = etage + baujahr + zimmer + float(milliseconds) + float(count)
    buildingid = buildingid / 2
    buildingid = str(buildingid).replace(".", "")
    buildingid = "ID" + str(buildingid).replace("-", "")
  

    # Upload der Daten zum SQL-Server  
    mariadb_connection = mariadb.connect (host = 'localhost', database = 'excel', user = 'mein-nutzername', password = 'mein-passwort')
    cursor = mariadb_connection.cursor()
    	 
	 
    try: 
        cursor.execute("INSERT INTO `exceltosql` (buildingid,ort,strasse,etage,baujahr,zimmer) VALUES (%s,%s,%s,%s,%s,%s)", (buildingid,ort,strasse,etage,baujahr,zimmer))
        Id = cursor.lastrowid
        mariadb_connection.commit()
        
	
    except mariadb.Error as error:
        mariadb_connection.rollback()
        
	
    finally:
        if(mariadb_connection.is_connected()):
            cursor.close()
            mariadb_connection.close()

04. Download

Ich habe hier nochmal alle Files zusammengestellt, damit das Einrichten nochmal etwas schneller von der Hand geht. Ich hoffe, ihr habt genauso viel Spaß mit dem Projekt wie ich und jetzt wünsche ich euch gutes Gelingen.

zip-file-icon

artsblog-excel-to-sql-master.zip

Filesize: 0,1 mb
Related Posts