MENU
  1. Startseite
  2. SQL
  3. So importieren Sie Daten aus Excel-Dateien in eine SQL-Datenbank

So importieren Sie Daten aus Excel-Dateien in eine SQL-Datenbank

SQL

Im Bereich Business und Datenanalyse wird häufig verlangt, dass Daten aus Excel-Dateien in eine SQL-Datenbank importiert werden. Durch die Migration von Daten in eine SQL-Datenbank kann die Verwaltung, Abfrage und Analyse von Daten erheblich verbessert werden. In diesem Artikel stellen wir Ihnen die konkreten Schritte und Tools vor, die erforderlich sind, um Excel-Daten in eine SQL-Datenbank zu importieren.

Inhaltsverzeichnis

Vorbereitung der benötigten Tools und Umgebung

Um Daten aus einer Excel-Datei in eine SQL-Datenbank zu importieren, benötigen Sie die folgenden Tools und eine entsprechende Umgebung.

Erforderliche Tools

  1. Microsoft Excel: Erforderlich, um die Excel-Datei mit den zu importierenden Daten zu öffnen.
  2. SQL Server: Die SQL-Datenbank, in die die Daten importiert werden sollen. Wir verwenden hier SQL Server als Beispiel.
  3. SQL Server Management Studio (SSMS): Ein Tool zur Verwaltung von SQL Server und zum Importieren von Daten.
  4. Python: Wird verwendet, wenn der Import durch Automatisierung durchgeführt werden soll.

Umgebungsvorbereitung

  1. Installation von SQL Server: Laden Sie SQL Server von der offiziellen Website herunter und installieren Sie es.
  2. Installation von SQL Server Management Studio: Laden Sie SSMS von der offiziellen Website herunter und installieren Sie es.
  3. Vorbereitung der Python-Umgebung: Installieren Sie Anaconda oder die offizielle Python-Distribution und installieren Sie die erforderlichen Bibliotheken (z. B. pandas, sqlalchemy).

Vorbereitung der Excel-Daten

Bevor Sie die Daten aus einer Excel-Datei in eine SQL-Datenbank importieren, müssen Sie die Daten aufbereiten und vorbereiten.

Datenaufbereitung

  1. Überprüfung der Datenformate: Stellen Sie sicher, dass die Datenformate in jeder Spalte korrekt sind. Zum Beispiel sollten Datumsangaben als Datumsformat und Zahlen als Zahlenformat vorliegen.
  2. Entfernen unnötiger Daten: Entfernen Sie leere Zeilen und unnötige Spalten, sodass nur die benötigten Daten zum Import übrig bleiben.
  3. Datenbereinigung: Überprüfen Sie auf doppelte Daten oder fehlende Werte und korrigieren oder entfernen Sie diese bei Bedarf.

Formatierung der Excel-Datei

  1. Überprüfung des Blattnamens: Überprüfen Sie den Namen des Blattes, das importiert werden soll, und ändern Sie ihn bei Bedarf in einen verständlicheren Namen.
  2. Überprüfung des Datenbereichs: Stellen Sie sicher, welcher Bereich der Daten importiert werden soll. Wenn der Bereich groß ist, wählen Sie nur den benötigten Bereich aus.

Speicherformat der Excel-Datei

  1. Auswahl des Dateiformats: Speichern Sie die Excel-Datei im .xlsx-Format. Es können auch andere Formate (z. B. .xls, .csv) verwendet werden, wählen Sie jedoch das für das Importtool geeignete Format.

Verwendung des SQL Server Import and Export Wizard

In diesem Abschnitt wird erläutert, wie Sie den SQL Server Import and Export Wizard verwenden, um Daten aus einer Excel-Datei in eine SQL-Datenbank zu importieren.

Starten des Wizards

  1. Öffnen von SSMS: Starten Sie SQL Server Management Studio und verbinden Sie sich mit der Zieldatenbank für den Import.
  2. Starten des Wizards: Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Datenbanknamen und wählen Sie „Tasks“ > „Daten importieren“. Dadurch wird der SQL Server Import and Export Wizard gestartet.

Einstellung der Datenquelle

  1. Auswahl der Datenquelle: Wählen Sie im Bildschirm „Datenquelle auswählen“ „Microsoft Excel“ aus.
  2. Angabe des Dateipfads: Geben Sie den Pfad zur zu importierenden Excel-Datei an. Wählen Sie bei Bedarf die Excel-Version aus.
  3. Auswahl des Blattes: Wählen Sie das zu importierende Blatt aus.

Ziel-Einstellungen

  1. Auswahl des Ziels: Wählen Sie im Bildschirm „Ziel auswählen“ „SQL Server Native Client“ aus.
  2. Angabe des Servernamens und der Datenbank: Geben Sie den Servernamen ein und wählen Sie die Zieldatenbank für den Import aus.

Überprüfung und Ausführung der Importeinstellungen

  1. Kopieren der Daten: Wählen Sie die Option „Daten kopieren“ und klicken Sie auf „Weiter“.
  2. Überprüfung der Zuordnung: Überprüfen Sie im Bildschirm „Spaltenzuordnung“ die Zuordnung der Excel-Spalten zu den SQL-Tabellenspalten und passen Sie diese bei Bedarf an.
  3. Ausführung des Imports: Klicken Sie auf „Weiter“, überprüfen Sie die Importeinstellungen und klicken Sie dann auf „Fertig stellen“, um den Import auszuführen.

Überprüfung der Ergebnisse

Nach Abschluss des Imports werden die Ergebnisse angezeigt. Überprüfen Sie, ob Fehlermeldungen oder Warnungen vorhanden sind, und stellen Sie sicher, dass der Import erfolgreich war.

Verwendung von SQL Server Management Studio (SSMS) für den Import

In diesem Abschnitt wird ausführlich erklärt, wie Sie mithilfe von SQL Server Management Studio (SSMS) Daten aus einer Excel-Datei in eine SQL-Datenbank importieren können.

Erstellen einer Tabelle

  1. Starten Sie SSMS: Öffnen Sie SQL Server Management Studio und verbinden Sie sich mit der Zieldatenbank für den Import.
  2. Erstellen Sie eine neue Tabelle: Erstellen Sie eine neue Tabelle in der Datenbank. Die Tabellenstruktur sollte den Daten in der zu importierenden Excel-Datei entsprechen. Verwenden Sie beispielsweise die folgende SQL-Abfrage, um eine Tabelle zu erstellen.
   CREATE TABLE SampleTable (
       ID INT PRIMARY KEY,
       Name NVARCHAR(50),
       Age INT,
       Email NVARCHAR(50)
   );

Laden der Excel-Daten

  1. Verbindung mit der Datenbank-Engine: Stellen Sie in SSMS eine Verbindung zur Datenbank-Engine her.
  2. Einstellung eines Linked Servers: Stellen Sie die Excel-Datei als Linked Server ein. Verwenden Sie die folgende SQL-Abfrage, um den Linked Server zu konfigurieren.
   EXEC sp_addlinkedserver 
       @server = 'EXCEL_LINK', 
       @srvproduct = 'Excel', 
       @provider = 'Microsoft.ACE.OLEDB.12.0', 
       @datasrc = 'C:\path\to\your\file.xlsx', 
       @provstr = 'Excel 12.0;HDR=YES';
  1. Überprüfung des Linked Servers: Führen Sie die folgende SQL-Abfrage aus, um zu überprüfen, ob der Linked Server korrekt konfiguriert ist.
   SELECT * FROM OPENQUERY(EXCEL_LINK, 'SELECT * FROM [Sheet1$]');

Importieren der Daten

  1. Verwendung der INSERT INTO-Anweisung: Importieren Sie die Excel-Daten in die neu erstellte Tabelle. Führen Sie die folgende SQL-Abfrage aus, um die Daten zu importieren.
   INSERT INTO SampleTable (ID, Name, Age, Email)
   SELECT * FROM OPENQUERY(EXCEL_LINK, 'SELECT * FROM [Sheet1$]');
  1. Überprüfung der Importergebnisse: Überprüfen Sie den Inhalt der Tabelle, um sicherzustellen, dass die Daten korrekt importiert wurden.
   SELECT * FROM SampleTable;

Löschen des Linked Servers

Nach Abschluss des Imports sollten Sie den Linked Server löschen. Führen Sie die folgende SQL-Abfrage aus.

   EXEC sp_dropserver 'EXCEL_LINK', 'droplogins';

Verwendung von Python für den Import

In diesem Abschnitt wird erklärt, wie Sie mit Python Daten aus einer Excel-Datei in eine SQL-Datenbank importieren können. Python ist aufgrund seiner Automatisierungs- und Anpassungsfähigkeit ein äußerst nützliches Werkzeug für die Datenverarbeitung.

Installation der erforderlichen Bibliotheken

Zuerst müssen Sie die erforderlichen Python-Bibliotheken installieren. Wir verwenden pandas und SQLAlchemy.

pip install pandas sqlalchemy openpyxl

Lesen der Excel-Datei

Verwenden Sie pandas, um die Excel-Datei zu lesen.

import pandas as pd

# Pfad zur Excel-Datei
file_path = 'path/to/your/file.xlsx'

# Lesen der Excel-Datei
df = pd.read_excel(file_path, sheet_name='Sheet1')

Einstellungen für die Datenbankverbindung

Verwenden Sie SQLAlchemy, um eine Verbindung zur SQL-Datenbank herzustellen.

from sqlalchemy import create_engine

# Verbindungs-URL der Datenbank
db_url = 'mssql+pyodbc://username:password@server/database?driver=SQL+Server'

# Erstellen der SQLAlchemy-Engine
engine = create_engine(db_url)

Importieren der Daten

Importieren Sie das pandas DataFrame in die SQL-Datenbank.

# Tabellenname
table_name = 'SampleTable'

# Importieren des DataFrame in die SQL-Tabelle
df.to_sql(table_name, con=engine, if_exists='append', index=False)

Fehlerbehandlung und Datenvalidierung

Fangen Sie mögliche Fehler beim Import der Daten ab und überprüfen Sie, ob die Daten korrekt importiert wurden.

try:
    df.to_sql(table_name, con=engine, if_exists='append', index=False)
    print("Data imported successfully")
except Exception as e:
    print(f"An error occurred: {e}")

Überprüfung der Importergebnisse

Führen Sie eine SQL-Abfrage aus, um sicherzustellen, dass die Daten korrekt importiert wurden.

import sqlalchemy as sa

# Verbindung zur Datenbank herstellen
with engine.connect() as connection:
    result = connection.execute(sa.text(f"SELECT * FROM {table_name}"))
    for row in result:
        print(row)

Wichtige Punkte bei der Fehlerbehandlung und Datenvalidierung

Beim Import von Daten in eine SQL-Datenbank sind Fehlerbehandlung und Datenvalidierung entscheidend. Dies gewährleistet die Datenintegrität und ermöglicht die effektive Verwaltung von Problemen, die während des Importprozesses auftreten können.

Grundlagen der Fehlerbehandlung

  1. Verwendung von Transaktionen: Verwenden Sie Transaktionen, um eine Reihe von Datenoperationen zusammenzufassen. Im Fehlerfall wird die Transaktion zurückgesetzt, um die Datenintegrität zu wahren.
   BEGIN TRANSACTION;
   -- Datenimportoperationen
   IF @@ERROR <> 0
       ROLLBACK TRANSACTION;
   ELSE
       COMMIT TRANSACTION;
  1. Exception Handling: Führen Sie eine Fehlerbehandlung im Programm durch, fangen Sie Fehlermeldungen ab und protokollieren Sie diese. In Python verwenden Sie dazu den try-except-Block.
   try:
       # Datenimportoperationen
   except Exception as e:
       print(f"An error occurred: {e}")
       # Rollback-Operationen falls erforderlich

Methoden der Datenvalidierung

  1. Überprüfung der Datentypen: Stellen Sie vor dem Import sicher, dass die Datentypen in jeder Spalte mit der Definition der SQL-Tabelle übereinstimmen. In pandas können Sie die Datentypen wie folgt überprüfen.
   assert df['column_name'].dtype == 'expected_dtype'
  1. Überprüfung der Eindeutigkeit: Stellen Sie sicher, dass keine doppelten Daten in den Spalten vorhanden sind, die als Primärschlüssel oder eindeutiger Index definiert sind.
   SELECT column_name, COUNT(*)
   FROM table_name
   GROUP BY column_name
   HAVING COUNT(*) > 1;

Datenvalidierung nach dem Import

  1. Überprüfung der Datenmenge: Vergleichen Sie die Datenmenge vor und nach dem Import, um sicherzustellen, dass alle Datensätze korrekt importiert wurden.
   SELECT COUNT(*) FROM table_name;
  1. Überprüfung von Beispieldaten: Extrahieren Sie Beispieldaten, um zu überprüfen, ob der Inhalt der Daten korrekt übernommen wurde.
   SELECT TOP 10 * FROM table_name;

Protokollierung und Überwachung

  1. Protokollierungseinstellungen: Protokollieren Sie Fehler und Warnungen, die während des Importprozesses auftreten. In SQL Server können Sie SQL Server-Agent verwenden, um die Protokolle von Jobs zu verwalten.
  2. Verwendung von Überwachungstools: Verwenden Sie Überwachungstools, um die Leistung und Fehlerprotokolle der Datenbank zu überwachen.

Fazit

In diesem Artikel haben wir detailliert erklärt, wie Sie Daten aus einer Excel-Datei in eine SQL-Datenbank importieren können. Im Folgenden fassen wir die wichtigsten Punkte zusammen.

Vorbereitung der Tools und Umgebung

Wir haben die erforderlichen Tools (Microsoft Excel, SQL Server, SQL Server Management Studio, Python usw.) und deren Installationsverfahren erklärt.

Vorbereitung der Excel-Daten

Wir haben die Vorverarbeitung der Excel-Datei erläutert, einschließlich der Formatierung der Daten und des Entfernens unnötiger Daten, um die Datei für den Import vorzubereiten.

Verwendung des SQL Server Import and Export Wizard

Wir haben detailliert beschrieben, wie Sie mit dem SSMS-Wizard Excel-Daten einfach in eine SQL-Datenbank importieren können.

Verwendung von SSMS für den Import

Wir haben erklärt, wie Sie einen Linked Server konfigurieren und Excel-Daten in eine SQL-Datenbank importieren können, einschließlich der Erstellung von Tabellen und der Zuordnung von Daten.

Verwendung von Python für den Import

Wir haben detailliert beschrieben, wie Sie mit pandas und SQLAlchemy in Python Excel-Daten in eine SQL-Datenbank importieren können.

Fehlerbehandlung und Datenvalidierung

Wir haben Methoden zur Fehlerbehandlung und Sicherstellung der Datenintegrität während des Importprozesses erläutert.

Der Prozess des Datenimports kann kompliziert erscheinen, aber mit den richtigen Tools und Schritten lässt er sich effizient durchführen. Nutzen Sie diese Methoden, um Excel-Daten reibungslos in eine SQL-Datenbank zu importieren.

SQL
Datenanalyse Datenbank Datenimport Excel Python SQL SQL Server SSMS
Inhaltsverzeichnis