Leer archivos de Excel

Puede ingerir, analizar y consultar archivos de Excel para cargas de trabajo por lotes y streaming mediante compatibilidad integrada con formato de archivo de 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 Carga de archivos en Azure Databricks.
• 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 el streaming estructurado de archivos de Excel.

Prerrequisitos

Databricks Runtime 17.1 o superior.

Crear o modificar una tabla en la interfaz de usuario

Puede usar la interfaz de usuario Crear o modificar tablas para crear tablas a partir de archivos de Excel. Para empezar, cargue un archivo de Excel o seleccione 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.

Consultar archivos de Excel

Puede consultar sus archivos de Excel utilizando las API por lotes de Spark (spark.read) y de streaming (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 operationlistSheets.

Opciones de análisis de Excel

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

Examples

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

Leer archivos de Excel mediante una lectura por lotes de Spark

Puede leer un archivo de 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 read_files función con valores de tabla para ingerir archivos de 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 de Excel mediante el cargador automático

Puede procesar archivos de Excel mediante el cargador automático estableciendo cloudFiles.format a 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 de 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 complejas de Excel 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 dataAddress opciones. 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 listSheets operación . El esquema devuelto es un struct con los siguientes campos:

• sheetIndex:largo
• sheetName: Cadena

Por ejemplo:

Pitón

# 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 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 la transmisión de archivos de Excel mediante el cargador automático, pero la evolución del esquema no lo es. 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 de 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 de Excel con diseños complejos o varias tablas por hoja?

De forma predeterminada, el analizador lee todas las celdas de 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 usar la ingesta de Excel en el cargador automático y los trabajos de streaming?

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

¿Se admite 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.