Excel-bestanden lezen en streamen

Azure Databricks bevat ingebouwde ondersteuning voor lezen .xls en .xlsx bestanden, waardoor externe bibliotheken of handmatige bestandsconversies niet meer nodig zijn. U kunt elk blad uit een werkmap met meerdere werkbladen lezen, specifieke celbereiken instellen, automatisch schema- en gegevenstypen afleiden en met formulewaarden werken als berekende resultaten. Excel bestanden kunnen worden gelezen uit de cloudopslag of rechtstreeks worden geüpload in de gebruikersinterface Gegevens toevoegen en ondersteuning bieden voor zowel batch- als streamingworkloads met behulp van Auto Loader.

Vereiste voorwaarden

Voor het lezen en streamen van Excel bestanden is Databricks Runtime 17.1 of hoger en automatisch laden vereist voor streamingworkloads.

Options

Gebruik de .option() en .options() methoden om DataFrameReader Excel gegevensbronnen te configureren. Zie DataFrameReader Excel opties en DataFrameWriter Excel opties voor een volledige lijst met ondersteunde opties.

Usage

In de volgende voorbeelden ziet u hoe u Excel bestanden leest met behulp van de Spark-batch (spark.read) en streaming-API's. Standaard leest de parser alle cellen, van linksboven tot en met de meest rechtsonder gelegen niet-lege cel, in het eerste werkblad; gebruik de dataAddress-optie om een specifiek werkblad of celbereik op te geven. Schema wordt automatisch afgeleid of u kunt uw eigen schema opgeven.

Een tabel maken of wijzigen in de gebruikersinterface

U kunt de gebruikersinterface van Maak of wijzigen gebruiken om tabellen te maken op basis van Excel bestanden. Begin met uploading van een Excel-bestand of het selecteren van een Excel-bestand vanaf een volume of een externe locatie. Kies het blad, pas het aantal koprijen aan en specificeer optioneel een celbereik. De gebruikersinterface ondersteunt het maken van één tabel uit het geselecteerde bestand en blad.

Excel-bestanden lezen

U kunt een Excel-bestand uit cloudopslag (bijvoorbeeld S3, ADLS) lezen met spark.read.excel of met de functie read_files van SQL.

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

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

Excel-bestanden streamen met behulp van Auto Loader

U kunt Excel bestanden streamen met behulp van automatisch laden door cloudFiles.format in te stellen op excel. Voorbeeld:

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

Excel-bestanden opnemen met behulp van COPY INTO

Gebruik COPY INTO om Excel-bestanden uit cloudopslag idempotent in een Delta-tabel te laden.

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

Lijstbladen

U kunt de bladen in een Excel-bestand weergeven met behulp van de bewerking listSheets. Het geretourneerde schema is een struct met de volgende velden:

• sheetIndex: lang
• sheetName: tekenreeks

Voorbeeld:

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

Complexe niet-gestructureerde Excel bladen parseren

Voor complexe, niet-gestructureerde Excel bladen (bijvoorbeeld meerdere tabellen per vel, gegevenseilanden), raadt Databricks aan om de celbereiken te extraheren die u nodig hebt om uw Spark DataFrames te maken met behulp van de dataAddress-opties.

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

Beperkingen

• Bestanden die met een wachtwoord zijn beveiligd, worden niet ondersteund.
• Slechts één koprij wordt ondersteund.
• Samengevoegde celwaarden vullen alleen de bovenste linker cel. Resterende subcellen zijn ingesteld op NULL.
• Het streamen van Excel bestanden met autolader wordt ondersteund, maar de ontwikkeling van schema's is dat niet. U moet schemaEvolutionMode="None" uitdrukkelijk instellen.
• 'Strict Open XML Spreadsheet (Strict OOXML)' wordt niet ondersteund.
• Macro-uitvoering in .xlsm bestanden wordt niet ondersteund.
• De ignoreCorruptFiles optie wordt niet ondersteund.

Veelgestelde vragen

Vind antwoorden op veelgestelde vragen over de Excel-connector in Lakeflow Connect.

Kan ik alle bladen tegelijk lezen?

De parser leest slechts één blad uit een Excel bestand tegelijk. Standaard wordt het eerste blad gelezen. U kunt een ander blad opgeven met behulp van de dataAddress optie. Als u meerdere bladen wilt verwerken, haalt u eerst de lijst met bladen op door de operation optie in te listSheets stellen, en vervolgens de bladnamen door te lopen en elk blad te lezen door de naam ervan op te geven in de dataAddress optie.

Kan ik Excel bestanden opnemen met complexe indelingen of meerdere tabellen per vel?

Standaard leest de parser alle Excel cellen van de cel linksboven naar de niet-lege cel rechtsonder. U kunt een ander celbereik opgeven met behulp van de dataAddress optie.

Hoe worden formules en samengevoegde cellen verwerkt?

Formules worden opgenomen als berekende waarden. Voor samengevoegde cellen blijft alleen de waarde linksboven behouden (onderliggende cellen zijn NULL).

Kan ik Excel opname gebruiken in automatische laad- en streamingtaken?

Ja, u kunt Excel bestanden streamen met behulp van cloudFiles.format = "excel". Schemaontwikkeling wordt echter niet ondersteund, dus moet u instellen op "schemaEvolutionMode""None".

Is met een wachtwoord beveiligd Excel ondersteund?

Nee. Als deze functionaliteit essentieel is voor uw werkstromen, neemt u contact op met uw Databricks-accountvertegenwoordiger.

Aanvullende bronnen

• CSV-bestanden lezen en schrijven: Als uw gegevensbron naar CSV kan exporteren, is CSV een eenvoudigere indeling met bredere ondersteuning voor hulpprogramma's en geen afhankelijkheid van een toegewezen parser.