Ler e transmitir arquivos Excel

O Azure Databricks inclui suporte integrado para a leitura de arquivos .xls e .xlsx, eliminando a necessidade de bibliotecas externas ou de conversões manuais de arquivos. Você pode ler qualquer planilha de uma pasta de trabalho com várias planilhas, selecionar intervalos específicos de células, inferir automaticamente o esquema e os tipos de dados e trabalhar com valores de fórmulas como resultados calculados. Arquivos do Excel podem ser lidos do armazenamento em nuvem ou carregados diretamente na interface do usuário Adicionar dados e oferecem suporte a cargas de trabalho em lote e de streaming usando o Auto Loader.

Pré-requisitos

Ler e transmitir arquivos Excel requer o Databricks Runtime 17.1 ou superior e o Carregador Automático para cargas de trabalho de streaming.

Opções

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

Usage

Os exemplos a seguir demonstram a leitura de arquivos do Excel usando as APIs em lote do Spark (spark.read) e de streaming. Por padrão, o analisador lê todas as células da parte superior esquerda para a célula não vazia inferior direita na primeira planilha; use a opção dataAddress para direcionar uma planilha ou intervalo de células específico. O esquema é inferido automaticamente ou você pode especificar o seu próprio.

Criar ou modificar uma tabela na interface do usuário

Você pode usar a interface do usuário Criar ou modificar a tabela para criar tabelas de arquivos Excel. Comece fazendo upload de um arquivo Excel ou selecionando um arquivo Excel de um volume ou de um local externo. Escolha a planilha, ajuste o número de linhas de cabeçalho e, opcionalmente, especifique um intervalo de células. A interface do usuário dá suporte à criação de uma única tabela a partir do arquivo e da planilha selecionados.

Ler arquivos Excel

Você pode ler um arquivo Excel do armazenamento em nuvem (por exemplo, S3, ADLS) usando spark.read.excel ou a função do 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"
);

Transmitir arquivos Excel usando o Carregador Automático

Você pode transmitir arquivos Excel usando o Carregador Automático definindo cloudFiles.format como 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 arquivos Excel usando COPY INTO

Use COPY INTO para carregar arquivos do Excel do armazenamento em nuvem 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');

Listar planilhas

Você pode listar as planilhas em um arquivo Excel usando a operação listSheets. O esquema retornado é um 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"
)

Analisar planilhas complexas não estruturadas Excel

Para planilhas de Excel complexas e não estruturadas (por exemplo, várias tabelas por planilha, ilhas de dados), o Databricks recomenda extrair os intervalos de células necessários para criar seus DataFrames do 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

  • Não há suporte para arquivos protegidos por senha.
  • Há suporte apenas para uma linha de cabeçalho.
  • Os valores das células mescladas são preenchidos apenas na célula superior esquerda. As células-filhas restantes são configuradas para NULL.
  • Há suporte para streaming de arquivos Excel usando o Auto Loader, mas a evolução do esquema não é suportada. Você deve definir schemaEvolutionMode="None"explicitamente .
  • Não há suporte para "Planilha Open XML Estrita (Strict OOXML)".
  • Não há suporte para a execução de macro em .xlsm arquivos.
  • Não há suporte para a opção ignoreCorruptFiles.

perguntas frequentes

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

Posso ler todas as planilhas de uma vez?

O analisador lê apenas uma planilha de um arquivo Excel de cada vez. Por padrão, ele lê a primeira planilha. Você pode especificar uma planilha diferente usando a opção dataAddress . Para processar várias planilhas, primeiro recupere a lista de planilhas definindo a opção operation como listSheets e, em seguida, itere sobre os nomes das planilhas, lendo cada um ao fornecer seu nome na opção dataAddress.

Posso ingerir arquivos Excel com layouts complexos ou várias tabelas em uma planilha?

Por padrão, o analisador lê todas as células Excel da célula superior esquerda para a célula não vazia inferior direita. Você pode especificar um intervalo de células diferente usando a opção dataAddress .

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

As fórmulas são ingeridas como seus valores calculados. Para células mescladas, somente o valor superior esquerdo é retido (as células filho são NULL).

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

Sim, você pode transmitir arquivos Excel usando cloudFiles.format = "excel". No entanto, não há suporte para a evolução do esquema, portanto, você deve definir "schemaEvolutionMode" como "None".

O Excel protegido por senha é compatível?

Não. Se essa funcionalidade for essencial para seus fluxos de trabalho, entre em contato com o representante da sua conta do Databricks.

Recursos adicionais

  • Ler e gravar arquivos CSV: se a fonte de dados puder exportar para CSV, o CSV será um formato mais simples com suporte de ferramentas mais amplo e nenhuma dependência em um analisador dedicado.
  • Last updated on