Odczytywanie plików programu Excel

Możesz pozyskiwać, analizować i kwerendować pliki programu Excel dla obciążeń wsadowych i przesyłanych strumieniowo, korzystając z wbudowanej obsługi formatu plików programu Excel. Automatycznie wywnioskuje schemat i typy danych, eliminując konieczność zewnętrznych bibliotek lub ręcznych konwersji plików. Ta funkcja zapewnia bezproblemowe pozyskiwanie danych zarówno z lokalnych operacji przekazywania, jak i magazynu w chmurze.

Kluczowe funkcje

• Bezpośrednio odczytywać pliki .xls i .xlsx używając usług Databricks SQL i Spark API.
• Przekazywanie plików .xls i .xlsx bezpośrednio za pomocą interfejsu użytkownika dodawania danych. Zobacz Przekazywanie plików do usługi Azure Databricks.
• Odczytywanie dowolnego arkusza z pliku z wieloma arkuszami.
• Określ dokładne granice komórek lub zakresy.
• Automatycznie wnioskowaj schemat, nagłówki i typy danych.
• Pozyskiwanie obliczonych formuł.
• Użyj Auto Loader do przesyłania strumieniowego o strukturze plików Excel.

Wymagania wstępne

Databricks Runtime 17.1 lub nowszy.

Tworzenie lub modyfikowanie tabeli w interfejsie użytkownika

Interfejs użytkownika tworzenia lub modyfikowania tabeli umożliwia tworzenie tabel z plików programu Excel. Zacznij od przekazania pliku programu Excel lub wybrania pliku programu Excel z woluminu lub lokalizacji zewnętrznej. Wybierz arkusz, dostosuj liczbę wierszy nagłówka i opcjonalnie określ zakres komórek. Interfejs użytkownika obsługuje tworzenie pojedynczej tabeli na podstawie wybranego pliku i arkusza.

Wykonywanie zapytań względem plików programu Excel

Możesz wykonywać zapytania dotyczące plików programu Excel przy użyciu interfejsów API platformy Spark batch (spark.read) i przesyłania strumieniowego (spark.readstream). Możesz automatycznie wywnioskować schemat lub określić własny schemat, aby przeanalizować pliki programu Excel. Domyślnie analizator odczytuje wszystkie komórki rozpoczynające się od lewej górnej komórki do prawej dolnej, niepustej komórki w pierwszym arkuszu. Aby odczytać inny arkusz lub zakres komórek, użyj opcji dataAddress.

Listę arkuszy w pliku programu Excel można przeglądać, ustawiając operation opcję na listSheets.

Opcje analizowania programu Excel

Dostępne są następujące opcje analizowania plików programu Excel:

Przykłady

Znajdź przykłady kodu do odczytywania plików programu Excel przy użyciu wbudowanego łącznika Lakeflow Connect.

Odczyt plików Excel za pomocą wsadowego odczytu Spark

Plik programu Excel można odczytać z magazynu w chmurze (na przykład S3, ADLS) przy użyciu polecenia spark.read.excel. Przykład:

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

Odczytywanie plików programu Excel przy użyciu języka SQL

Za pomocą funkcji zwracającej tabele read_files można załadować pliki programu Excel bezpośrednio, używając języka SQL. Przykład:

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

Przesyłanie strumieniowe plików programu Excel przy użyciu automatycznego modułu ładującego

Pliki programu Excel można przesyłać strumieniowo przy użyciu funkcji automatycznego ładowania, ustawiając cloudFiles.format na excel. Przykład:

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

Importowanie plików Excel przy użyciu polecenia 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');

Analizowanie złożonych niestrukturalnych arkuszy programu Excel

W przypadku złożonych, niestrukturalnych arkuszy programu Excel (na przykład wielu tabel na arkusz, wysp danych) Databricks zaleca wyodrębnienie potrzebnych zakresów komórek, aby utworzyć Spark DataFrames, korzystając z opcji dataAddress. Przykład:

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

Wyświetlanie listy arkuszy

Arkusze można wyświetlić w pliku programu Excel przy użyciu listSheets operacji . Zwrócony schemat jest struct następującymi polami:

• sheetIndex: długi
• sheetName: ciąg

Przykład:

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

Ograniczenia

• Pliki chronione hasłem nie są obsługiwane.
• Obsługiwany jest tylko jeden wiersz nagłówka.
• Wartości w scalonych komórkach wypełniają tylko lewą górną komórkę. Pozostałe komórki podrzędne są ustawione na NULL.
• Przesyłanie strumieniowe plików programu Excel przy użyciu Auto Loader jest obsługiwane, ale zmiany schematu nie są. Należy jawnie ustawić schemaEvolutionMode="None".
• "Ściśle otwarty arkusz kalkulacyjny XML (Strict OOXML)" nie jest obsługiwany.
• Wykonywanie makr w .xlsm plikach nie jest obsługiwane.
• ignoreCorruptFiles Opcja nie jest obsługiwana.

Często zadawane pytania

Znajdź odpowiedzi na często zadawane pytania dotyczące łącznika programu Excel w programie Lakeflow Connect.

Czy mogę odczytać wszystkie arkusze jednocześnie?

Analizator odczytuje tylko jeden arkusz z pliku programu Excel jednocześnie. Domyślnie odczytuje pierwszy arkusz. Możesz określić inny arkusz przy użyciu dataAddress opcji . Aby przetworzyć wiele arkuszy, najpierw pobierz listę arkuszy, ustawiając operation opcję na listSheets, a następnie iteruj nazwy arkuszy i odczytaj poszczególne arkusze, podając jego nazwę w dataAddress opcji .

Czy mogę importować pliki programu Excel ze złożonymi układami lub wieloma tabelami na arkuszach?

Domyślnie analizator odczytuje wszystkie komórki programu Excel z lewej górnej komórki do komórki niepustej w prawym dolnym rogu. Możesz określić inny zakres komórek przy użyciu dataAddress opcji .

W jaki sposób są obsługiwane formuły i scalone komórki?

Formuły są wprowadzane jako ich obliczone wartości. W przypadku scalonych komórek zachowywana jest tylko lewa górna wartość (komórki podrzędne to NULL).

Czy mogę używać importu danych programu Excel w Auto Loader i w zadaniach przesyłania strumieniowego?

Tak, możesz przesyłać strumieniowo pliki programu Excel przy użyciu polecenia cloudFiles.format = "excel". Jednak ewolucja schematu nie jest obsługiwana, dlatego należy ustawić wartość "schemaEvolutionMode""None".

Czy program Excel jest chroniony hasłem?

Nie. Jeśli ta funkcja ma kluczowe znaczenie dla przepływów pracy, skontaktuj się z przedstawicielem konta usługi Databricks.