Excel-Dateien lesen und streamen

Azure Databricks umfasst integrierte Unterstützung für das Lesen .xls und .xlsx Dateien, ohne dass externe Bibliotheken oder manuelle Dateikonvertierungen erforderlich sind. Sie können jedes Blatt aus einer Mehrblattarbeitsmappe lesen, bestimmte Zellbereiche als Ziel festlegen, Schema- und Datentypen automatisch ableiten und mit Formelwerten als berechnete Ergebnisse arbeiten. Excel Dateien können aus dem Cloudspeicher gelesen oder direkt in die Benutzeroberfläche zum Hinzufügen von Daten hochgeladen werden und sowohl Batch- als auch Streaming-Workloads mithilfe des automatischen Ladens unterstützen.

Voraussetzungen

Das Lesen und Streamen Excel Dateien erfordert Databricks Runtime 17.1 oder höher und auto Loader für Streaming-Workloads.

Options

Verwenden Sie die Methoden .option() und .options() von DataFrameReader, um Excel-Datenquellen zu konfigurieren. Eine vollständige Liste der unterstützten Optionen finden Sie unter DataFrameReader Excel Optionen und DataFrameWriter Excel Optionen.

Usage

Die folgenden Beispiele veranschaulichen das Lesen Excel Dateien mit dem Spark-Batch (spark.read) und streaming-APIs. Standardmäßig liest der Parser alle Zellen von oben links in die nicht leere Zelle unten rechts im ersten Blatt. verwenden Sie die dataAddress Option, um auf ein bestimmtes Blatt oder einen bestimmten Zellbereich zu abzielen. Das Schema wird automatisch abgeleitet, oder Sie können eigene angeben.

Erstellen oder Ändern einer Tabelle in der Benutzeroberfläche

Sie können die Benutzeroberfläche Create or modify table verwenden, um Tabellen aus Excel Dateien zu erstellen. Beginnen Sie mit Uploading einer Excel Datei oder Auswahl einer Excel Datei von einem Volume oder einem externen Speicherort. Wählen Sie das Blatt aus, passen Sie die Anzahl der Kopfzeilen an, und geben Sie optional einen Zellbereich an. Die Benutzeroberfläche unterstützt das Erstellen einer einzelnen Tabelle aus der ausgewählten Datei und dem ausgewählten Blatt.

Lesen Excel Dateien

Sie können eine Excel Datei aus dem Cloudspeicher (z. B. S3, ADLS) mit spark.read.excel oder der SQL-Funktion read_files lesen.

Python

# Read the first sheet from a single Excel file or from multiple Excel files in a directory
df = (spark.read.excel(<path to excel directory or file>))

# Infer schema field name from the header row
df = (spark.read
       .option("headerRows", 1)
       .excel(<path to excel directory or file>))

# Read a specific sheet and range
df = (spark.read
       .option("headerRows", 1)
       .option("dataAddress", "Sheet1!A1:E10")
       .excel(<path to excel directory or file>))

SQL

-- Read an entire Excel file
CREATE TABLE my_table AS
SELECT * FROM read_files(
  "<path to excel directory or file>",
  schemaEvolutionMode => "none"
);

-- Read a specific sheet and range
CREATE TABLE my_sheet_table AS
SELECT * FROM read_files(
  "<path to excel directory or file>",
  format => "excel",
  headerRows => 1,
  dataAddress => "Sheet1!A2:D10",
  schemaEvolutionMode => "none"
);

Streamen von Excel-Dateien mithilfe des Auto-Loaders

Sie können Excel Dateien mithilfe des automatischen Ladeprogramms streamen, indem Sie cloudFiles.format auf excel festlegen. Beispiel:

df = (
  spark
    .readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "excel")
    .option("cloudFiles.inferColumnTypes", True)
    .option("headerRows", 1)
    .option("cloudFiles.schemaLocation", "<path to schema location dir>")
    .option("cloudFiles.schemaEvolutionMode", "none")
    .load(<path to excel directory or file>)
)
df.writeStream
  .format("delta")
  .option("mergeSchema", "true")
  .option("checkpointLocation", "<path to checkpoint location dir>")
  .table(<table name>)

Excel-Dateien importieren mithilfe von COPY INTO

Verwenden Sie COPY INTO, um Excel-Dateien aus dem Cloud-Speicher idempotent in eine Delta-Tabelle zu laden.

CREATE TABLE IF NOT EXISTS excel_demo_table;

COPY INTO excel_demo_table
FROM "<path to excel directory or file>"
FILEFORMAT = EXCEL
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

Blätter auflisten

Sie können die Blätter in einer Excel Datei mithilfe des Vorgangs listSheets auflisten. Das zurückgegebene Schema ist ein struct Mit den folgenden Feldern:

  • sheetIndex: lang
  • sheetName: Zeichenfolge

Beispiel:

Python

# List the name of the Sheets in an Excel file
df = (spark.read.format("excel")
       .option("operation", "listSheets")
       .load(<path to excel directory or file>))

SQL

SELECT * FROM read_files("<path to excel directory or file>",
  schemaEvolutionMode => "none",
  operation => "listSheets"
)

Analysieren komplexer nicht strukturierter Excel Blätter

Für komplexe, nicht strukturierte Excel Blätter (z. B. mehrere Tabellen pro Blatt, Dateninseln) empfiehlt Databricks, die Zellbereiche zu extrahieren, die Sie zum Erstellen Von Spark DataFrames mit den Optionen dataAddress benötigen.

df = (spark.read.format("excel")
       .option("headerRows", 1)
       .option("dataAddress", "Sheet1!A1:E10")
       .load(<path to excel directory or file>))

Einschränkungen

  • Kennwortgeschützte Dateien werden nicht unterstützt.
  • Es wird nur eine Kopfzeile unterstützt.
  • Zusammengeführte Zellwerte füllen nur die obere linke Zelle auf. Verbleibende untergeordnete Zellen werden auf NULL gesetzt.
  • Das Streamen von Excel-Dateien mit Auto Loader wird unterstützt, jedoch nicht die Schemaanpassung. Sie müssen schemaEvolutionMode="None" explizit festlegen.
  • "Strict Open XML Spreadsheet (Strict OOXML)" wird nicht unterstützt.
  • Die Makroausführung in .xlsm Dateien wird nicht unterstützt.
  • Die Option ignoreCorruptFiles wird nicht unterstützt.

Häufig gestellte Fragen

Hier finden Sie Antworten auf häufig gestellte Fragen zum Excel Connector in Lakeflow Connect.

Kann ich alle Blätter gleichzeitig lesen?

Der Parser liest jeweils nur ein Blatt aus einer Excel Datei. Standardmäßig liest es das erste Blatt. Sie können ein anderes Blatt mithilfe der dataAddress Option angeben. Um mehrere Blätter zu verarbeiten, rufen Sie zuerst die Liste der Blätter ab, indem Sie die operation Option auf listSheetsfestlegen, dann die Blattnamen durchlaufen und die einzelnen Blätter lesen, indem Sie ihren Namen in der dataAddress Option angeben.

Kann ich Excel Dateien mit komplexen Layouts oder mehreren Tabellen pro Blatt aufnehmen?

Standardmäßig liest der Parser alle Excel Zellen aus der oberen linken Zelle bis zur nicht leeren Zelle unten rechts. Sie können einen anderen Zellbereich mithilfe der dataAddress Option angeben.

Wie werden Formeln und verbundene Zellen behandelt?

Formeln werden als berechnete Werte aufgenommen. Bei zusammengeführten Zellen wird nur der obere linke Wert beibehalten (untergeordnete Zellen sind NULL).

Kann ich die Excel-Datenaufnahme in Auto Loader- und Streaming-Aufträgen verwenden?

Ja, Sie können Excel Dateien mithilfe von cloudFiles.format = "excel" streamen. Die Schemaentwicklung wird jedoch nicht unterstützt, daher müssen Sie "schemaEvolutionMode" auf "None" festlegen.

Wird kennwortgeschütztes Excel unterstützt?

Nein. Wenn diese Funktionalität für Ihre Workflows wichtig ist, wenden Sie sich an Ihren Databricks-Kontovertreter.

Weitere Ressourcen

  • Lesen und Schreiben von CSV-Dateien: Wenn Ihre Datenquelle in CSV exportieren kann, ist CSV ein einfacheres Format mit umfassenderer Toolunterstützung und keine Abhängigkeit von einem dedizierten Parser.
  • Last updated on