Ler arquivos do Excel

importante

Este recurso está em versão Beta. Os administradores do espaço de trabalho podem controlar o acesso a este recurso na página de Pré-visualizações . Veja as prévias do Gerenciador Databricks.

Você pode importar, analisar e consultar arquivos Excel para cargas de trabalho de lotes e transmissões usando o suporte integrado ao formato de arquivo Excel . Ele infere automaticamente o esquema e os tipos de dados, eliminando a necessidade de bibliotecas externas ou conversões manuais de arquivos. Este recurso proporciona ingestão contínua tanto de uploads locais quanto de armazenamento cloud .

recurso principal

Leia diretamente arquivos .xls e .xlsx usando as APIs Databricks SQL e Spark.
Faça o upload direto dos arquivos .xls e .xlsx usando a interface de usuário Adicionar Dados. Veja como enviar arquivos para Databricks.
Leia qualquer folha de um arquivo com várias folhas.
Especifique os limites ou intervalos exatos das células.
Inferir automaticamente o esquema, os cabeçalhos e os tipos de dados.
Ingerir fórmulas avaliadas.
Utilize Auto Loader para transmissão estruturada de arquivos Excel .

Pré-requisitos

Databricks Runtime 17.1 ou acima.

Criar ou modificar uma tabela na interface do usuário.

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

Consultar arquivos do Excel

Você pode consultar seus arquivos Excel usando APIs Spark lotes (spark.read) e transmissão (spark.readstream). Você pode optar por inferir o esquema automaticamente ou especificar seu próprio esquema para analisar os arquivos do Excel. Por default, o analisador lê todas as células não vazias da primeira planilha, começando pela célula superior esquerda até a célula inferior direita. 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:

opção de fonte de dados	Descrição
`dataAddress`	O endereço do intervalo de células a ser lido na sintaxe do Excel. Caso não seja especificado, o analisador sintático lê todas as células válidas da primeira planilha. `""` ou omitido: Lê todos os dados da primeira planilha. `"MySheet!C5:H10"`: Lê o intervalo de `C5` a `H10` da planilha chamada `MySheet`. `"C5:H10"`: Lê o intervalo de `C5` a `H10` da primeira folha. `"Sheet1!A1:A1"`: Lê apenas a célula `A1` de `Sheet1`. `"Sheet1"`: Lê todos os dados de `Sheet1`. `"'My Sheet!?>'!D5:G10"`: Lê D5 a G10 de `My Sheet!?>`. Inclua o nome da folha com `'`.
`headerRows`	O número de linhas iniciais no arquivo Excel a serem tratadas como linhas de cabeçalho e lidas como nomes de coluna. Quando `dataAddress` é especificado, `headerRows` se aplica às linhas de cabeçalho dentro desse intervalo de células. Os valores suportados são `0` e `1`. O default é `0`, caso em que os nomes das colunas são gerados automaticamente anexando o número da coluna a `_c` (por exemplo: `_c1`, `_c2`, `_c3`, ...). Exemplos: `dataAddress: "A2:D5"`, `headerRows: "0"`: Infere nomes de coluna como `_c1`…`_c4`. Lê a primeira linha de dados da linha 2: `A2` a `D2`. `dataAddress: "A2:D5"`, `headerRows: "1"`: Define os nomes das colunas como valores das células na linha 2: `A2` a `D2`. Lê a primeira linha de dados da linha 3: `A3` a `D3`.
`operation`	Indica as operações a serem executadas na planilha Excel . O default é `readSheet`, que lê dados de uma planilha. A outra operação suportada é `listSheets`, que retorna a lista de planilhas na pasta de trabalho. Para as operações `listSheets` , o esquema retornado é um `struct` com os seguintes campos: `sheetIndex`: longo `sheetName`: strings
`timestampNTZFormat`	Cadeias de formato personalizadas para um valor de carimbo de data/hora (armazenado como uma cadeia de caracteres no Excel) sem fuso horário, que seguem o formato de padrão de data e hora. Isso se aplica a valores de strings lidos como `TimestampNTZType`. padrão: `yyyy-MM-dd'T'HH:mm:ss[.SSS]`.
`dateFormat`	Cadeias de formato de data personalizadas que seguem o padrão de data e hora. Isso se aplica a valores de strings lidos como `Date`. padrão: `yyyy-MM-dd`.

Exemplos

Encontre exemplos de código para leitura de arquivos Excel usando o conector integrado LakeFlow Connect.

Leia arquivos Excel usando um lote de leitura Spark .

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

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

Leia arquivos do Excel usando SQL

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

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

transmissão de arquivos Excel usando Auto Loader

Você pode transmitir arquivos Excel usando Auto Loader definindo cloudFiles.format para excel. Por exemplo:

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

SQL
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 Excel complexas e não estruturadas

Para planilhas do 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:

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

Folhas de lista

Você pode listar as planilhas em um arquivo Excel usando as operações listSheets . O esquema retornado é um struct com os seguintes campos:

sheetIndex: longo
sheetName: strings

Por exemplo:

Python
SQL

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

Arquivos protegidos por senha não são suportados.
Apenas uma linha de cabeçalho é suportada.
Os valores da célula mesclada preenchem apenas a célula superior esquerda. As células filhas restantes são definidas como NULL.
A transmissão de arquivos Excel usando Auto Loader é suportada, mas a evolução do esquema não. Você deve definir explicitamente schemaEvolutionMode="None".
"Planilha Open XML estrita (OOXML estrita)" não é suportada.
A execução de macros em arquivos .xlsm não é suportada.
A opção ignoreCorruptFiles não é suportada.

Perguntas frequentes

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

Posso ler todas as folhas de uma vez?

O analisador sintático lê apenas uma planilha de um arquivo Excel por vez. Por default, ele lê a primeira folha. 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, depois itere sobre os nomes das planilhas e leia cada uma fornecendo seu nome na opção dataAddress .

Posso importar arquivos Excel com complexyon ou várias tabelas por planilha?

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

Como são tratadas as fórmulas e a mesclagem de células?

As fórmulas são inseridas conforme seus valores de cálculo. Para células mescladas, apenas o valor superior esquerdo é mantido (células filhas são NULL).

Posso usar a ingestão de Excel no Auto Loader e no Transmitir Job?

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

O Excel protegido por senha é compatível?

Não. Se essa funcionalidade for essencial para o seu fluxo de trabalho, entre em contato com o seu representante account Databricks .

recurso principal​

Pré-requisitos​

Criar ou modificar uma tabela na interface do usuário.​

Consultar arquivos do Excel​

opções de análise do Excel​

Exemplos​

Leia arquivos Excel usando um lote de leitura Spark .​

Leia arquivos do Excel usando SQL​

transmissão de arquivos Excel usando Auto Loader​

Ingerir arquivos do Excel usando COPY INTO​

Analisar planilhas Excel complexas e não estruturadas​

Folhas de lista​

Limitações​

Perguntas frequentes​