Leggere e trasmettere file Excel

Azure Databricks include il supporto integrato per la lettura di file .xls e .xlsx, eliminando la necessità di librerie esterne o di conversioni manuali dei file. È possibile leggere qualsiasi foglio da una cartella di lavoro su più fogli, specificare intervalli di celle specifici, dedurre automaticamente schema e tipi di dati e usare i valori della formula come risultati calcolati. Excel i file possono essere letti dall'archiviazione cloud o caricati direttamente nell'interfaccia utente Aggiungi dati e supportano sia carichi di lavoro batch che di streaming usando il caricatore automatico.

Prerequisiti

La lettura e lo streaming dei file Excel richiedono Databricks Runtime 17.1 o versione successiva e il caricatore automatico per i carichi di lavoro di streaming.

Options

Usa i metodi .option() e .options() di DataFrameReader per configurare le origini dati di Excel. Per un elenco completo delle opzioni supportate, vedere DataFrameReader opzioni Excel e DataFrameWriter opzioni di Excel.

Usage

Gli esempi seguenti illustrano la lettura di Excel file usando il batch Spark (spark.read) e le API di streaming. Per impostazione predefinita, il parser legge tutte le celle dall'alto a sinistra alla cella non vuota in basso a destra nel primo foglio; utilizzare l'opzione dataAddress per specificare un foglio o un intervallo di celle specifico. Lo schema viene dedotto automaticamente oppure è possibile specificare il proprio.

Creare o modificare una tabella nell'interfaccia utente

È possibile usare l'interfaccia utente Crea o modifica tabella per creare tabelle da file di Excel. Inizia caricando un file Excel o selezionando un file Excel da un disco o da un percorso esterno. Selezionare il foglio, regolare il numero di righe di intestazione e, facoltativamente, specificare un intervallo di celle. L'interfaccia utente supporta la creazione di una singola tabella dal file e dal foglio selezionati.

Leggere file Excel

È possibile leggere un file Excel dall'archiviazione cloud ,ad esempio S3, ADLS, usando spark.read.excel o la funzione di read_files SQL.

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

Trasmettere Excel file con il caricatore automatico

È possibile trasmettere i file di Excel usando il caricatore automatico impostando cloudFiles.format su excel. Per esempio:

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

Inserire file Excel usando COPY INTO

Usare COPY INTO per caricare file Excel dall'archiviazione nel cloud in una tabella Delta in modo 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');

Elenco dei fogli

È possibile elencare i fogli in un file di Excel usando l'operazione listSheets. Lo schema restituito è un oggetto struct con i campi seguenti:

• sheetIndex:lungo
• sheetName: stringa

Per esempio:

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

Analizzare fogli di Excel complessi non strutturati

Per fogli di Excel complessi e non strutturati (ad esempio, più tabelle per foglio, isole dati), Databricks consiglia di estrarre gli intervalli di celle necessari per creare i dataframe Spark usando le opzioni dataAddress.

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

Limitazioni

• I file protetti da password non sono supportati.
• È supportata una sola riga di intestazione.
• I valori delle celle unite popolano solo la cella in alto a sinistra. Le celle figlio rimanenti sono impostate su NULL.
• Lo streaming di file Excel tramite Auto Loader è supportato, ma l'evoluzione dello schema non è. È necessario impostare schemaEvolutionMode="None"in modo esplicito .
• "Strict Open XML Spreadsheet (Strict OOXML)" non è supportato.
• L'esecuzione di macro nei .xlsm file non è supportata.
• L'opzione ignoreCorruptFiles non è supportata.

Domande frequenti

Trovare le risposte alle domande frequenti sul connettore Excel in Lakeflow Connect.

Posso leggere tutti i fogli contemporaneamente?

Il parser legge un solo foglio da un file Excel alla volta. Per impostazione predefinita, legge il primo foglio. È possibile specificare un foglio diverso usando l'opzione dataAddress . Per elaborare più fogli, recuperare prima di tutto l'elenco dei fogli impostando l'opzione operation su listSheets, quindi scorrere i nomi dei fogli e leggerne ognuno specificando il nome nell'opzione dataAddress .

È possibile inserire file di Excel con layout complessi o più tabelle per foglio?

Per impostazione predefinita, il parser legge tutte le celle Excel dalla cella superiore sinistra alla cella inferiore destra non vuota. È possibile specificare un intervallo di celle diverso usando l'opzione dataAddress .

Come vengono gestite le formule e le celle unite?

Le formule vengono inserite come valori calcolati. Per le celle unite, viene mantenuto solo il valore in alto a sinistra (le celle subordinate sono NULL).

È possibile usare l'inserimento Excel nei processi di caricamento automatico e di streaming?

Sì, è possibile trasmettere Excel file usando cloudFiles.format = "excel". Tuttavia, poiché l'evoluzione dello schema non è supportata, è necessario impostare "schemaEvolutionMode" su "None".

Excel protetto da password è supportato?

No Se questa funzionalità è fondamentale per i flussi di lavoro, contattare il rappresentante dell'account Databricks.

Risorse aggiuntive

• Leggere e scrivere file CSV: se l'origine dati può esportare in CSV, csv è un formato più semplice con supporto più ampio degli strumenti e nessuna dipendenza da un parser dedicato.