Ler arquivos Excel

Você pode ingerir, analisar e consultar arquivos Excel para cargas de trabalho em lote e fluxo contínuo usando o suporte interno para o formato de arquivo Excel. Ele infere automaticamente tipos de dados e esquema, eliminando a necessidade de bibliotecas externas ou conversões manuais de arquivo. Esse recurso fornece ingestão perfeita de uploads locais e armazenamento em nuvem.

Características principais

• Ler arquivos .xls e .xlsx diretamente usando APIs do Databricks SQL e do Spark.
• Carregue arquivos .xls e .xlsx diretamente usando a UI Adicionar Dados. Veja Criar ou modificar uma tabela usando o upload de arquivo.
• Leia qualquer planilha de um arquivo de várias planilhas.
• Especifique os limites ou intervalos exatos da célula.
• Inferir automaticamente esquemas, cabeçalhos e tipos de dados.
• Ingerir fórmulas avaliadas.
• Use o Carregador Automático para streaming estruturado de arquivos Excel.

Pré-requisitos

Databricks Runtime 17.1 ou superior.

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.

Consultar arquivos Excel

Você pode consultar seus arquivos de Excel usando as APIs em lote do Spark (spark.read) e as APIs de streaming do Spark (spark.readstream). Você pode optar por inferir automaticamente o esquema ou especificar seu próprio esquema para analisar os arquivos de Excel. Por padrão, o analisador lê todas as células que começam da célula superior esquerda para a célula não vazia inferior direita na primeira planilha. Para ler uma planilha ou intervalo de células diferente, use a opção dataAddress .

Você pode consultar a lista de planilhas em um arquivo Excel definindo a opção operation como listSheets.

Opções de análise do Excel

As seguintes opções estão disponíveis para analisar arquivos Excel:

Exemplos

Encontre exemplos de código para ler arquivos Excel usando o conector interno Lakeflow Connect.

Ler arquivos Excel usando uma leitura em lote do Spark

Você pode ler um arquivo Excel do armazenamento em nuvem (por exemplo, S3, ADLS) usando spark.read.excel. Por exemplo:

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

Ler arquivos Excel usando SQL

Você pode usar a função read_files com valor de tabela para ingerir arquivos Excel diretamente usando SQL. Por exemplo:

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

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

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. Por exemplo:

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

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

Limitações

• Não há suporte para gravar DataFrames no formato Excel. Você pode exportar dados para outros formatos, como CSV ou Parquet.
• 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?

Fórmulas são ingeridas como seus valores computados. Para células mescladas, somente o valor superior esquerdo é retido (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".

Posso gravar DataFrames no formato Excel?

Não. O conector Excel interno dá suporte apenas à leitura. Para exportar dados, use um formato de gravação com suporte, como CSV ou Parquet.

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.