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:

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.