Ler arquivos do Excel

Você pode ingerir, analisar e consultar arquivos do Excel para cargas de trabalho em lote e streaming usando o suporte interno ao formato de arquivo do 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. Consulte Carregar arquivos no Azure Databricks.
• 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 do 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 do Excel. Comece carregando um arquivo do Excel ou selecionando um arquivo do Excel em um volume ou em 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 do Excel

Você pode consultar seus arquivos do Excel usando APIs de lote (spark.read) e streaming (spark.readstream) do Spark. Você pode optar por inferir automaticamente o esquema ou especificar seu próprio esquema para analisar os arquivos do 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 do 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 do Excel:

Exemplos

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

Ler arquivos do Excel usando uma leitura em lote do Spark

Você pode ler um arquivo do 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 do Excel usando SQL

Você pode usar a read_files função com valor de tabela para ingerir arquivos do 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 do Excel usando o Carregador Automático

Você pode transmitir arquivos do 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 do 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 do Excel não estruturadas

Para planilhas complexas e não estruturadas do Excel (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 dataAddress opções. 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 do Excel usando a listSheets operação. 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 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 do Excel usando o Auto Loader, mas a evolução de 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 do Excel no Lakeflow Connect.

Posso ler todas as planilhas de uma vez?

O analisador lê apenas uma planilha de um arquivo do Excel por 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 do Excel com layouts complexos ou várias tabelas por planilha?

Por padrão, o analisador lê todas as células do 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 do Excel em trabalhos de streaming e carregador automático?

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

Há suporte para o Excel protegido por senha?

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