Leggere file Excel

È possibile inserire, analizzare ed eseguire query sui file Excel per i carichi di lavoro batch e in streaming usando il supporto predefinito per il formato file Excel. Deduce automaticamente schemi e tipi di dati, eliminando la necessità di librerie esterne o conversioni manuali di file. Questa funzionalità offre un inserimento semplice sia dai caricamenti locali che dall'archiviazione cloud.

Funzionalità principali

• Leggere direttamente i file .xls e .xlsx utilizzando le API di Databricks SQL e Spark.
• Carica direttamente i file .xls e .xlsx utilizzando l'interfaccia Aggiungi dati. Consulta Creare o modificare una tabella tramite caricamento di file.
• Leggere qualsiasi foglio da un file con più fogli.
• Specificare limiti o intervalli di celle esatti.
• Dedurre automaticamente schema, intestazioni e tipi di dati.
• Inserire formule valutate.
• Usare Il caricatore automatico per lo streaming strutturato di file Excel.

Prerequisiti

Databricks Runtime 17.1 o versione successiva.

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.

Eseguire query Excel file

È possibile eseguire query sui file di Excel usando le API spark batch (spark.read) e di streaming (spark.readstream). È possibile scegliere di dedurre automaticamente lo schema o specificare il proprio schema per analizzare i file Excel. Per impostazione predefinita, il parser legge tutte le celle a partire dalla cella superiore sinistra alla cella inferiore destra non vuota nel primo foglio. Per leggere un foglio o un intervallo di celle diverso, usare l'opzione dataAddress .

È possibile eseguire una query sull'elenco dei fogli in un file di Excel impostando l'opzione operation su listSheets.

Excel opzioni di analisi

Sono disponibili le opzioni seguenti per analizzare Excel file. Per un elenco completo delle DataFrameReader opzioni, vedere Opzioni DataFrameReader.

Esempi

Trovare esempi di codice per leggere Excel file usando il connettore predefinito Lakeflow Connect.

Leggere file Excel usando una lettura batch di Spark

È possibile leggere un file Excel dall'archiviazione cloud ,ad esempio S3, ADLS, usando spark.read.excel. Per esempio:

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

Leggere Excel file con SQL

È possibile usare la funzione read_files con valori di tabella per inserire Excel file direttamente tramite SQL. Per esempio:

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

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

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. Per esempio:

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

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

Limitazioni

• La scrittura di dataframe nel formato Excel non è supportata. È possibile esportare dati in altri formati, ad esempio CSV o Parquet.
• 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".

È possibile scrivere dataframe nel formato Excel?

No Il connettore Excel predefinito supporta solo la lettura. Per esportare i dati, usare un formato di scrittura supportato, ad esempio CSV o Parquet.

Excel protetto da password è supportato?

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