Leer archivos de Excel

Puede ingerir, analizar y consultar archivos Excel para cargas de trabajo por lotes y streaming mediante compatibilidad integrada con formato de archivo Excel. Deduce automáticamente los tipos de datos y esquema, lo que elimina la necesidad de bibliotecas externas o conversiones manuales de archivos. Esta característica proporciona ingesta sin problemas tanto de cargas locales como de almacenamiento en la nube.

Características clave

• Leer .xls y .xlsx archivos directamente mediante las API de Sql y Spark de Databricks.
• Cargue los archivos .xls y .xlsx directamente mediante la interfaz de usuario Agregar datos. Consulte Crear o modificar una tabla mediante la carga de archivos.
• Lee cualquier hoja de un archivo de varias hojas.
• Especifique límites o intervalos exactos de celdas.
• Inferir automáticamente el esquema, los encabezados y los tipos de datos.
• Ingestión de fórmulas evaluadas.
• Use Auto Loader para la transmisión estructurada de archivos Excel.

Prerrequisitos

Databricks Runtime 17.1 o superior.

Crear o modificar una tabla en la interfaz de usuario

Puede usar la interfaz de usuario Create o modify table para crear tablas a partir de archivos Excel. Comience por cargar un archivo Excel o seleccionar un archivo de Excel desde un volumen o una ubicación externa. Elija la hoja, ajuste el número de filas de encabezado y, opcionalmente, especifique un intervalo de celdas. La interfaz de usuario admite la creación de una sola tabla a partir del archivo y la hoja seleccionados.

Consulta de archivos Excel

Puede consultar los archivos de Excel mediante las API de procesamiento por lotes de Spark (spark.read) y las API de transmisión (spark.readstream). Puede optar por deducir automáticamente el esquema o especificar su propio esquema para analizar los archivos de Excel. De forma predeterminada, el analizador lee todas las celdas a partir de la celda superior izquierda a la celda inferior derecha no vacía de la primera hoja. Para leer otra hoja o rango de celdas, use la dataAddress opción .

Puede consultar la lista de hojas en un archivo de Excel estableciendo la opción operation en listSheets.

Opciones de análisis de Excel

Las siguientes opciones están disponibles para analizar archivos Excel:

Examples

Busque ejemplos de código para leer Excel archivos mediante el conector integrado Lakeflow Connect.

Leer Excel archivos mediante una lectura por lotes de Spark

Puede leer un archivo Excel desde el almacenamiento en la nube (por ejemplo, S3, ADLS) mediante spark.read.excel. Por ejemplo:

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

Leer archivos de Excel mediante SQL

Puede usar la función con valores de tabla de read_files para ingerir archivos Excel directamente mediante SQL. Por ejemplo:

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

Transmisión de archivos Excel mediante el cargador automático

Puede transmitir archivos de Excel mediante el cargador automático estableciendo cloudFiles.format en excel. Por ejemplo:

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

Ingesta de archivos Excel mediante 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');

Análisis de hojas de Excel complejas no estructuradas

En el caso de hojas de Excel complejas y no estructuradas (por ejemplo, varias tablas por hoja, islas de datos), Databricks recomienda extraer los intervalos de celdas que necesita para crear los dataframes de Spark mediante las opciones de dataAddress. Por ejemplo:

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

Hojas de lista

Puede enumerar las hojas de un archivo de Excel mediante la operación listSheets. El esquema devuelto es un struct con los siguientes campos:

• sheetIndex:largo
• sheetName: Cadena

Por ejemplo:

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

Limitaciones

• No se admite escribir DataFrames en formato Excel. Puede exportar datos a otros formatos, como CSV o Parquet.
• No se admiten archivos protegidos con contraseña.
• Solo se admite una fila de encabezado.
• Los valores de celda combinados solo rellenan la celda superior izquierda. Las celdas secundarias restantes se configuran en NULL.
• Se admite el streaming de archivos Excel mediante Auto Loader, pero no se admite la evolución del esquema. Debe establecer explícitamente schemaEvolutionMode="None".
• No se admite "Strict Open XML Spreadsheet (Strict OOXML)".
• No se admite la ejecución de macros en .xlsm archivos.
• La opción ignoreCorruptFiles no es compatible.

Preguntas más frecuentes

Encuentre respuestas a las preguntas más frecuentes sobre el conector de Excel en Lakeflow Connect.

¿Puedo leer todas las hojas a la vez?

El analizador lee solo una hoja de un archivo Excel a la vez. De forma predeterminada, lee la primera hoja. Puede especificar otra hoja con la dataAddress opción . Para procesar varias hojas, primero recupere la lista de hojas estableciendo la operation opción listSheets en, y a continuación, itere sobre los nombres de las hojas y lea cada una especificando su nombre en la dataAddress opción.

¿Puedo ingerir archivos Excel con diseños complejos o varias tablas por hoja?

De forma predeterminada, el analizador lee todas las celdas Excel de la celda superior izquierda a la celda inferior derecha no vacía. Puede especificar un intervalo de celdas diferente mediante la dataAddress opción .

¿Cómo se controlan las fórmulas y las celdas combinadas?

Las fórmulas se ingieren como sus valores calculados. Para las celdas combinadas, solo se conserva el valor superior izquierdo (las celdas secundarias son NULL).

¿Puedo utilizar la ingestión de Excel en Auto Loader y trabajos de transmisión?

Sí, puede transmitir archivos Excel mediante cloudFiles.format = "excel". Sin embargo, no se admite la evolución del esquema, por lo que debe establecer "schemaEvolutionMode" en "None".

¿Puedo escribir DataFrames en formato Excel?

No. El conector integrado Excel admite solo lectura. Para exportar datos, use un formato de escritura compatible, como CSV o Parquet.

¿Se admite el uso de Excel protegido con contraseña?

No. Si esta funcionalidad es fundamental para los flujos de trabajo, póngase en contacto con el representante de la cuenta de Databricks.