Excel ファイルの読み取り

組み込みの Excel ファイル形式のサポートを使用して、バッチ ワークロードとストリーミング ワークロードについて Excel ファイルの取り込み、解析、クエリを実行できます。 スキーマとデータ型が自動的に推論されるため、外部ライブラリや手動のファイル変換が不要になります。 この機能により、ローカル アップロードとクラウド ストレージの両方からのシームレスなインジェストが提供されます。

主な機能

• Databricks SQL API と Spark API を使用して、 .xls ファイルと .xlsx ファイルを直接読み取ります。
• データの追加 UI を使用して、 .xls ファイルと .xlsx ファイルを直接アップロードします。 「Azure Databricks にファイルをアップロードする」を参照してください。
• マルチシート ファイルから任意のシートを読み取る。
• セルの境界または範囲を正確に指定します。
• スキーマ、ヘッダー、およびデータ型を自動的に推論します。
• 評価済みの数式をインポートする。
• Excel ファイルの構造化ストリーミングには自動ローダーを使用します。

[前提条件]

Databricks Runtime 17.1 以降。

UI でテーブルを作成または変更する

テーブルの 作成または変更 UI を使用して、Excel ファイルからテーブルを作成できます。 まず、 Excel ファイルをアップロード するか、 ボリュームまたは外部の場所から Excel ファイルを選択します。 シートを選択し、ヘッダー行の数を調整し、必要に応じてセル範囲を指定します。 UI では、選択したファイルとシートから 1 つのテーブルを作成できます。

Excel ファイルのクエリを実行する

Spark バッチ (spark.read) API とストリーミング (spark.readstream) API を使用して Excel ファイルのクエリを実行できます。 スキーマを自動的に推論するか、独自のスキーマを指定して Excel ファイルを解析するかを選択できます。 既定では、パーサーは、左上のセルから、最初のシートの右下の空でないセルに始まるすべてのセルを読み取ります。 別のシートまたはセル範囲を読み取る場合は、 dataAddress オプションを使用します。

operation オプションを listSheets に設定すると、Excel ファイル内のシートの一覧に対してクエリを実行できます。

Excel 解析オプション

Excel ファイルを解析するには、次のオプションを使用できます。

例示

組み込みのコネクタ Lakeflow Connect を使用して Excel ファイルを読み取るコード例を見つけます。

Spark バッチ読み取りを使用して Excel ファイルを読み取る

spark.read.excelを使用して、クラウド ストレージ (S3、ADLS など) から Excel ファイルを読み取ることができます。 例えば次が挙げられます。

# 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 を使用して Excel ファイルを読み取る

read_filesテーブル値関数を使用して、SQL を使用して Excel ファイルを直接取り込むことができます。 例えば次が挙げられます。

-- 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 ファイルをストリーム配信する

自動ローダーを使用して Excel ファイルをストリーミングするには、 cloudFiles.format を excel に設定します。 例えば次が挙げられます。

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

COPY INTO を使用して Excel ファイルを取り込む

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

複雑な非構造化 Excel シートを解析する

複雑で構造化されていない Excel シート (シートごとに複数のテーブル、データ アイランドなど) の場合、Databricks では、 dataAddress オプションを使用して Spark DataFrame を作成するために必要なセル範囲を抽出することをお勧めします。 例えば次が挙げられます。

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

シートを一覧表示する

listSheets操作を使用して、Excel ファイル内のシートを一覧表示できます。 返されるスキーマは、次のフィールドを持つ struct です。

• sheetIndex：長い
• sheetName: 文字列

例えば次が挙げられます。

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

制限事項

• パスワードで保護されたファイルはサポートされていません。
• サポートされているヘッダー行は 1 つだけです。
• 結合されたセルの値は、左上のセルにのみ設定されます。 残りの子セルは NULLに設定されます。
• 自動ローダーを使用した Excel ファイルのストリーミングはサポートされていますが、スキーマの進化はサポートされていません。 schemaEvolutionMode="None"を明示的に設定する必要があります。
• "Strict Open XML Spreadsheet (Strict OOXML)" はサポートされていません。
• .xlsm ファイルでのマクロの実行はサポートされていません。
• ignoreCorruptFiles オプションはサポートされていません。

FAQ

Lakeflow Connect の Excel コネクタに関してよく寄せられる質問に対する回答を見つけます。

すべてのシートを一度に読み取ることができますか?

パーサーは、Excel ファイルから一度に 1 つのシートのみを読み取ります。 既定では、最初のシートを読み取ります。 dataAddress オプションを使用して、別のシートを指定できます。 複数のシートを処理するには、最初に operation オプションを listSheets に設定してシートの一覧を取得し、次にシート名を反復処理し、 dataAddress オプションでその名前を指定して各シートを読み取ります。

複雑なレイアウトまたはシートごとに複数のテーブルを含む Excel ファイルを取り込むことができますか?

既定では、パーサーは左上のセルから右下の空でないセルにすべての Excel セルを読み取ります。 dataAddress オプションを使用して、別のセル範囲を指定できます。

数式と結合セルはどのように処理されますか?

数式は計算値として取り込まれます。 結合されたセルの場合、左上の値のみが保持されます (子セルは NULL)。

自動ローダージョブとストリーミング ジョブで Excel インジェストを使用できますか?

はい。 cloudFiles.format = "excel"を使用して Excel ファイルをストリーミングできます。 ただし、スキーマの進化はサポートされていないため、 "schemaEvolutionMode" を "None" に設定する必要があります。

パスワードで保護された Excel はサポートされていますか?

No. この機能がワークフローにとって重要な場合は、Databricks アカウントの担当者にお問い合わせください。