Ler ficheiros Excel

Pode ingerir, analisar e consultar ficheiros Excel para cargas de trabalho em lote e streaming usando o suporte integrado para formatos de ficheiros Excel. Infere automaticamente o esquema e os tipos de dados, eliminando a necessidade de bibliotecas externas ou conversões manuais de ficheiros. Esta funcionalidade permite uma ingestão fluida tanto a partir de uploads locais como de armazenamento na cloud.

Principais características

• Ler diretamente ficheiros .xls e .xlsx usando APIs SQL e Spark do Databricks.
• Carregue diretamente os ficheiros .xls e .xlsx usando a interface de utilizador Adicionar Dados. Consulte Criar ou modificar uma tabela usando o upload de arquivos.
• Leia qualquer folha de um ficheiro multi-folha.
• Especifique limites ou intervalos exatos das células.
• Inferir automaticamente o esquema, cabeçalhos e tipos de dados.
• Ingerir fórmulas avaliadas.
• Use o Auto Loader para streaming estruturado de ficheiros Excel.

Pré-requisitos

Databricks Runtime 17.1 ou superior.

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.

Consultar ficheiros Excel

Pode consultar os seus ficheiros Excel usando as APIs Spark batch (spark.read) e streaming (spark.readstream). Podes escolher inferir automaticamente o esquema ou especificar o teu próprio esquema para analisar os ficheiros Excel. Por defeito, o analisador lê todas as células a partir da célula superior esquerda até à célula inferior direita não vazia na primeira folha. Para ler uma folha ou intervalo de células diferente, use a opção dataAddress.

Pode consultar a lista de folhas num ficheiro Excel definindo a opção operation para listSheets.

Opções de análise Excel

As seguintes opções estão disponíveis para analisar ficheiros Excel. Para uma lista completa de opções de DataFrameReader, consulte as opções do DataFrameReader.

Examples

Encontre exemplos de código para ler ficheiros Excel usando o conector incorporado Lakeflow Connect.

Leia ficheiros Excel usando uma leitura em lote do Spark

Pode ler um ficheiro Excel a partir de armazenamento na cloud (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>))

Leia ficheiros Excel usando SQL

Podes usar a função de valores de tabela read_files para ingerir ficheiros de 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"
);

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

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

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

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

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

Limitações

• Não é suportado escrever DataFrames para formato Excel. Pode exportar dados para outros formatos, como CSV ou Parquet.
• 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".

Posso escrever DataFrames em formato Excel?

Não. O conector Excel incorporado suporta apenas leitura. Para exportar dados, utilize um formato de escrita suportado, como CSV ou Parquet.

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