Lire et diffuser en continu des fichiers Excel

Azure Databricks inclut la prise en charge intégrée de la lecture .xls et .xlsx des fichiers, ce qui élimine le besoin de bibliothèques externes ou de conversions manuelles de fichiers. Vous pouvez lire n’importe quelle feuille à partir d’un classeur à plusieurs feuilles, cibler des plages de cellules spécifiques, déduire automatiquement le schéma et les types de données, et utiliser des valeurs de formule en tant que résultats calculés. Les fichiers Excel peuvent être lus à partir du stockage dans le cloud ou chargés directement dans l’interface utilisateur Add Data, et prennent en charge les charges de travail par lots et en streaming à l’aide d’Auto Loader.

Prerequisites

La lecture et la diffusion en continu de fichiers Excel nécessitent Databricks Runtime 17.1 ou version ultérieure et le chargeur automatique pour les charges de travail de diffusion en continu.

Options

Utilisez les méthodes .option() et .options() de DataFrameReader pour configurer les sources de données Excel. Pour obtenir la liste complète des options prises en charge, consultez DataFrameReader Excel options et DataFrameWriter Excel options.

Usage

Les exemples suivants illustrent la lecture de fichiers Excel à l’aide des API de traitement par lots Spark (spark.read) et de diffusion en continu. Par défaut, l’analyseur lit toutes les cellules du haut à gauche vers la cellule non vide du bas à droite dans la première feuille ; utilisez l’option dataAddress pour cibler une feuille ou une plage de cellules spécifique. Le schéma est déduit automatiquement, ou vous pouvez spécifier votre propre schéma.

Créer ou modifier une table dans l’interface utilisateur

Vous pouvez utiliser l’interface utilisateur Create ou modifier une table pour créer des tables à partir de fichiers Excel. Commencez par charger un fichier Excel ou élection d’un fichier Excel à partir d’un volume ou d’un emplacement externe. Choisissez la feuille, ajustez le nombre de lignes d’en-tête et spécifiez éventuellement une plage de cellules. L’interface utilisateur prend en charge la création d’une table unique à partir du fichier et de la feuille sélectionnés.

Lire les fichiers Excel

Vous pouvez lire un fichier Excel à partir d’un stockage cloud (par exemple, S3, ADLS) à l’aide de spark.read.excel ou de la fonction SQL read_files.

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"
);

Diffuser en continu des fichiers Excel à l’aide du chargeur automatique

Vous pouvez diffuser en continu des fichiers Excel à l’aide du chargeur automatique en définissant cloudFiles.format sur excel. Par exemple:

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>)

Ingérer des fichiers Excel à l’aide de COPY INTO

Utilisez COPY INTO pour charger des fichiers Excel depuis un stockage cloud dans une table Delta de manière idempotente.

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');

Feuilles de liste

Vous pouvez répertorier les feuilles d’un fichier Excel à l’aide de l’opération listSheets. Le schéma retourné est un struct avec les champs suivants :

• sheetIndex : long
• sheetName : Chaîne

Par exemple:

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"
)

Analyser des feuilles de Excel complexes non structurées

Pour les feuilles de Excel complexes et non structurées (par exemple, plusieurs tables par feuille, îles de données), Databricks recommande d’extraire les plages de cellules dont vous avez besoin pour créer vos DataFrames Spark à l’aide des options dataAddress.

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

Limites

• Les fichiers protégés par mot de passe ne sont pas pris en charge.
• Une seule ligne d’en-tête est prise en charge.
• Les valeurs de cellule fusionnées remplissent uniquement la cellule en haut à gauche. Les cellules enfants restantes sont définies sur NULL.
• La diffusion en continu de fichiers Excel à l’aide d’Auto Loader est prise en charge, mais l’évolution du schéma n’est pas prise en charge. Vous devez définir schemaEvolutionMode="None"explicitement .
• « Strict Open XML Spreadsheet (Strict OOXML) » n’est pas pris en charge.
• L’exécution des macros dans les .xlsm fichiers n’est pas prise en charge.
• L'option ignoreCorruptFiles n'est pas prise en charge.

Questions fréquentes (FAQ)

Trouvez des réponses aux questions fréquemment posées sur le connecteur Excel dans Lakeflow Connect.

Puis-je lire toutes les feuilles en même temps ?

L’analyseur lit une seule feuille d’un fichier Excel à la fois. Par défaut, il lit la première feuille. Vous pouvez spécifier une autre feuille à l’aide de l’option dataAddress . Pour traiter plusieurs feuilles, commencez par récupérer la liste des feuilles en définissant l’option operation à listSheets, puis itérez sur les noms des feuilles et lisez chacune en fournissant son nom dans l’option dataAddress.

Puis-je ingérer des fichiers Excel avec des mises en page complexes ou plusieurs tables par feuille ?

Par défaut, l’analyseur lit toutes les cellules Excel de la cellule supérieure gauche vers la cellule en bas à droite non vide. Vous pouvez spécifier une plage de cellules différente à l’aide de l’option dataAddress .

Comment les formules et les cellules fusionnées sont-elles gérées ?

Les formules sont ingérées en tant que valeurs calculées. Pour les cellules fusionnées, seule la valeur en haut à gauche est conservée (les cellules enfants sont NULL).

Puis-je utiliser l’ingestion Excel dans Auto Loader et les travaux de streaming ?

Oui, vous pouvez diffuser en continu des fichiers Excel à l’aide de cloudFiles.format = "excel". Toutefois, l’évolution du schéma n’est pas prise en charge, vous devez donc définir "schemaEvolutionMode" sur "None".

Excel protégé par un mot de passe est-il pris en charge ?

Non. Si cette fonctionnalité est essentielle pour vos flux de travail, contactez votre représentant de compte Databricks.

Ressources additionnelles

• Lire et écrire des fichiers CSV : si votre source de données peut exporter au format CSV, csv est un format plus simple avec une prise en charge plus large des outils et aucune dépendance sur un analyseur dédié.