Odczytywanie plików Excel

Ważne

Ta funkcja jest dostępna w wersji beta. Administratorzy obszaru roboczego mogą kontrolować dostęp do tej funkcji ze strony Podglądy . Zobacz Zarządzanie podglądami Azure Databricks.

Można pozyskiwać, analizować i wykonywać zapytania dotyczące plików Excel dla obciążeń wsadowych i przesyłanych strumieniowo przy użyciu wbudowanej obsługi formatu plików 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 Tworzenie lub modyfikowanie tabeli za pomocąprzesyłania pliku.
  • 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 plików Excel.

Wymagania wstępne

Databricks Runtime 17.1 lub nowszy.

Tworzenie lub modyfikowanie tabeli w interfejsie użytkownika

Możesz użyć interfejsu użytkownika Utwórz lub zmodyfikuj tabelę aby utworzyć tabele na podstawie plików Excel. Zacznij od załadowania pliku Excel lub wybrania pliku 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ń o pliki Excel

Możesz wykonywać zapytania dotyczące plików Excel, korzystając z interfejsów API Spark Batch (spark.read) i Spark Streaming (spark.readstream). Możesz automatycznie wywnioskować schemat lub określić własny schemat, aby przeanalizować pliki 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 Excel można uzyskać, ustawiając opcję operation na listSheets.

opcje analizowania Excel

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

Opcja źródła danych Description
dataAddress Adres zakresu komórek do odczytania w składni Excel. Jeżeli nie zostanie sprecyzowane, analizator odczytuje wszystkie prawidłowe komórki z pierwszego arkusza.
  • "" lub pominięty: odczytuje wszystkie dane z pierwszego arkusza.
  • "MySheet!C5:H10": Odczytuje zakres od C5 do H10 z arkusza o nazwie MySheet.
  • "C5:H10": Odczytuje zakres C5 od H10 pierwszego arkusza.
  • "Sheet1!A1:A1": Odczytuje tylko komórkę A1 z Sheet1.
  • "Sheet1": odczytuje wszystkie dane z elementu Sheet1.
  • "My Sheet!?>!D5:G10": odczytuje zakres od D5 do G10 z My Sheet!?>.
headerRows Liczba początkowych wierszy w pliku Excel do traktowania jako wierszy nagłówka i odczytywania jako nazw kolumn. Gdy dataAddress jest określony, headerRows stosuje się do wierszy nagłówkowych w obrębie tego zakresu komórek. Obsługiwane wartości to 0 i 1. Wartość domyślna to 0, w którym przypadku nazwy kolumn są generowane automatycznie przez dołączenie numeru kolumny do _c (na przykład: _c1, _c2, _c3, ...).
Examples:
  • dataAddress: "A2:D5", headerRows: "0": ustala nazwy kolumn jako _c1..._c4. Odczytuje pierwszy wiersz danych z wiersza 2: A2 do D2.
  • dataAddress: "A2:D5", headerRows: "1": Ustawia nazwy kolumn jako wartości komórek w wierszu 2: A2 na D2. Odczytuje pierwszy wiersz danych z wiersza 3: A3 do D3.
operation Wskazuje operację do wykonania w skoroszycie Excel. Wartość domyślna to readSheet, która odczytuje dane z arkusza. Druga obsługiwana operacja to listSheets, która zwraca listę arkuszy w skoroszycie. Operacja listSheets zwraca schemat struct z następującymi polami:
  • sheetIndex: długi
  • sheetName: ciąg
timestampNTZFormat Ciąg formatu niestandardowego dla wartości znacznika czasu (przechowywanej jako ciąg w Excel) bez strefy czasowej zgodnej ze wzorcem daty/godziny. Dotyczy to wartości ciągów odczytanych jako TimestampNTZType. Wartość domyślna: yyyy-MM-dd'T'HH:mm:ss[.SSS].
dateFormat Niestandardowy ciąg formatu daty zgodny ze wzorcem daty/godziny. Dotyczy to wartości ciągów odczytanych jako Date. Wartość domyślna: yyyy-MM-dd.

Przykłady

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

Odczytywanie plików Excel przy użyciu odczytu wsadowego platformy Spark

Plik Excel można odczytać z magazynu w chmurze (na przykład S3, ADLS) przy użyciu 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 Excel przy użyciu języka SQL

Możesz użyć funkcji typu table-valued read_files do bezpośredniego pozyskiwania plików Excel przy użyciu 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 Excel przy użyciu modułu ładującego automatycznego

Pliki Excel można przesyłać strumieniowo przy użyciu narzędzia 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 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 arkuszy Excel niestrukturalnych

W przypadku złożonych, niestrukturalnych arkuszy Excel (na przykład wielu tabel na jednym arkuszu oraz wyizolowanych fragmentów danych), usługa Databricks zaleca wyodrębnianie zakresów komórek potrzebnych do utworzenia struktury Spark DataFrames za pomocą 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 Excel przy użyciu operacji listSheets. 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

  • Zapisywanie ramek danych w formacie Excel nie jest obsługiwane. Dane można eksportować do innych formatów, takich jak CSV lub Parquet.
  • 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 Excel przy użyciu Auto Loader jest obsługiwane, ale ewolucja schematu nie jest obsługiwana. 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 Excel w programie Lakeflow Connect.

Czy mogę odczytać wszystkie arkusze jednocześnie?

Analizator odczytuje tylko jeden arkusz z pliku 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 Excel z złożonymi układami lub z wieloma tabelami na arkuszu?

Domyślnie analizator odczytuje wszystkie komórki Excela od lewej górnej do prawej dolnej, niepustej komórki. 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ć wczytywania plików Excel w zadaniach Auto Loader i przesyłania strumieniowego?

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

Czy mogę zapisywać DataFrames w formacie Excel?

Nie. Wbudowany łącznik Excel obsługuje tylko odczyt. Aby wyeksportować dane, użyj obsługiwanego formatu zapisu, takiego jak CSV lub Parquet.

Czy obsługiwany jest Excel chroniony hasłem?

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

  • Last updated on