Leia e transmita ficheiros Excel

O Azure Databricks inclui suporte integrado para ler ficheiros .xls e .xlsx, eliminando a necessidade de bibliotecas externas ou de conversões manuais de ficheiros. Pode ler qualquer folha de um ficheiro Excel com várias folhas, selecionar intervalos específicos de células, inferir automaticamente o esquema e os tipos de dados, e trabalhar com valores de fórmulas pelos respetivos resultados calculados. Os ficheiros Excel podem ser lidos a partir de armazenamento na nuvem ou carregados diretamente na interface de Adicionar Dados, e suportam cargas de trabalho em lote e streaming usando o Auto Loader.

Pré-requisitos

Ler e transmitir ficheiros Excel requer Databricks Runtime 17.1 ou superior e Auto Loader para carregar cargas de trabalho em streaming.

Opções

Utilize os métodos .option() e .options() de DataFrameReader para configurar fontes de dados do Excel. Para uma lista completa de opções suportadas, consulte DataFrameReader opções Excel e DataFrameWriter Excel.

Usage

Os exemplos seguintes demonstram a leitura de ficheiros Excel utilizando as APIs Spark em lote (spark.read) e de streaming. Por predefinição, o analisador lê todas as células desde o canto superior esquerdo até à última célula não vazia no canto inferior direito da primeira folha; use a opção dataAddress para especificar uma folha ou um intervalo de células específico. O esquema é inferido automaticamente, ou pode especificar o seu próprio.

Crie ou modifique uma tabela na interface

Pode usar a interface Create ou modificar tabelas para criar tabelas a partir de Excel ficheiros. Comece por carregar um ficheiro Excel ou selecionar um ficheiro Excel a partir de um volume ou de uma localização externa. Escolha a folha, ajuste o número de linhas de cabeçalho e, opcionalmente, especifique um intervalo de células. A interface suporta a criação de uma única tabela a partir do ficheiro e da folha selecionados.

Ler ficheiros Excel

Pode ler um ficheiro Excel a partir de armazenamento na cloud (por exemplo, S3, ADLS) usando spark.read.excel ou a função SQLread_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"
);

Transmita ficheiros Excel usando o Auto Loader

Pode transmitir Excel ficheiros usando o Auto Loader definindo cloudFiles.format para excel. Por exemplo:

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

Ingerir ficheiros Excel usando COPY INTO

Utilize COPY INTO para carregar ficheiros Excel do armazenamento na cloud para uma tabela Delta de forma 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');

Folhas de lista

Pode listar as folhas num ficheiro Excel usando a operação listSheets. O esquema devolvido é a struct com os seguintes campos:

  • sheetIndex:Longo
  • sheetName: cadeia de caracteres

Por exemplo:

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

Analise folhas Excel complexas não estruturadas

Para folhas de Excel complexas e não estruturadas (por exemplo, múltiplas tabelas por folha, ilhas de dados), o Databricks recomenda extrair os intervalos de células que precisa para criar os seus DataFrames Spark usando as opções dataAddress.

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

Limitações

  • Ficheiros protegidos por palavra-passe não são suportados.
  • Apenas uma linha de cabeçalhos é suportada.
  • Os valores das células fundidas apenas preenchem a célula no canto superior esquerdo. As células filhas restantes são definidas para NULL.
  • A transmissão de ficheiros Excel usando o Auto Loader é suportada, mas a evolução de esquemas não. Deve definir schemaEvolutionMode="None" explicitamente.
  • "Folha de Cálculo XML Aberta Estrita (Strict OOXML)" não é suportada.
  • A execução de macros em .xlsm ficheiros não é suportada.
  • A ignoreCorruptFiles opção não é suportada.

FAQ

Encontre respostas às perguntas frequentes sobre o conector Excel no Lakeflow Connect.

Posso ler todas as folhas de uma vez?

O parser lê apenas uma folha de um ficheiro Excel de cada vez. Por defeito, lê a primeira folha. Pode especificar uma folha diferente usando essa dataAddress opção. Para processar múltiplas folhas, primeiro recupere a lista de folhas definindo a operation opção para listSheets, depois itere sobre os nomes das folhas e leia cada uma fornecendo o seu nome na dataAddress opção.

Posso ingerir ficheiros Excel com layouts complexos ou múltiplas tabelas por folha?

Por defeito, o analisador lê todas as células do Excel desde a célula superior esquerda até à célula inferior direita não vazia. Pode especificar um intervalo de células diferente usando essa dataAddress opção.

Como são tratadas as fórmulas e as células fundidas?

As fórmulas são ingeridas como os seus valores calculados. Para células fundidas, apenas o valor no canto superior esquerdo é mantido (células filhas são NULL).

Posso usar a ingestão Excel no Auto Loader e nos trabalhos de streaming?

Sim, pode transmitir Excel ficheiros usando cloudFiles.format = "excel". No entanto, a evolução de esquemas não é suportada, por isso deve definir "schemaEvolutionMode" para "None".

É suportado Excel protegido por palavra-passe?

Não. Se esta funcionalidade for crítica para os seus fluxos de trabalho, contacte o seu representante de conta Databricks.

Recursos adicionais

  • Leia e escreva ficheiros CSV: Se a sua fonte de dados puder exportar para CSV, CSV é um formato mais simples, com suporte mais amplo para ferramentas e sem dependência de um parser dedicado.
  • Last updated on