Excel ファイルの読み取り

Important

この機能は パブリック プレビュー段階です。

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

主な機能

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

[前提条件]

Databricks Runtime 17.1 以降。

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

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

Excel ファイルに対してクエリを実行する

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

operation オプションを listSheets に設定することで、Excel ファイル内のシートの一覧を照会できます。

Excel解析オプション

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

データ ソース オプション Description
dataAddress Excel構文で読み取るセル範囲のアドレス。 指定しない場合、パーサーは最初のシートからすべての有効なセルを読み取ります。
  • "" または省略: 最初のシートからすべてのデータを読み取ります。
  • "MySheet!C5:H10": C5という名前のシートからH10からMySheetまでの範囲を読み取ります。
  • "C5:H10": 最初のシートからC5からH10までの範囲を読み取ります。
  • "Sheet1!A1:A1": A1からセルSheet1のみを読み取ります。
  • "Sheet1": Sheet1からすべてのデータを読み取ります。
  • "My Sheet!?>!D5:G10": My Sheet!?>から D5 から G10 までを読み取ります。
headerRows ヘッダー行として扱い、列名として読み取るExcel ファイル内の初期行の数。 dataAddressを指定すると、headerRowsそのセル範囲内のヘッダー行に適用されます。 サポートされている値は、0 と 1です。 既定値は 0 です。この場合、列番号を _c に追加することで列名が自動的に生成されます (例: _c1、 _c2、 _c3、...)。
例 :
  • dataAddress: "A2:D5"、 headerRows: "0": 列名を _c1..._c4 として推論します。 行 2 からデータの最初の行を読み取ります。 A2 から D2。
  • dataAddress: "A2:D5"、headerRows: "1": 行2のセルの値を列名として設定します: A2からD2まで。 3 行目からデータの最初の行を読み取ります: A3 から D3 まで。
operation Excel ブックで実行する操作を示します。 既定値は readSheet で、シートからデータを読み取ります。 サポートされているもう 1 つの操作は listSheetsであり、ブック内のシートの一覧を返します。 listSheets操作の場合、返されるスキーマは次のフィールドを持つstructです。
  • sheetIndex：長い
  • sheetName: 文字列
timestampNTZFormat datetime パターン形式に従うタイムゾーンのないタイムスタンプ値のカスタム書式指定文字列 (Excelに文字列として格納されます)。 これは、 TimestampNTZTypeとして読み取られた文字列値に適用されます。 既定値: yyyy-MM-dd'T'HH:mm:ss[.SSS]。
dateFormat 日時パターン形式に従うカスタム日付の書式指定文字列。 これは、 Dateとして読み取られた文字列値に適用されます。 既定値: yyyy-MM-dd。

例示

組み込みのコネクタ 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 DataFrames を作成するために必要なセル範囲を抽出することをお勧めします。 例えば次が挙げられます。

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

制限事項

  • Excel形式への DataFrame の書き込みはサポートされていません。 CSV や Parquet などの他の形式にデータをエクスポートできます。
  • パスワードで保護されたファイルはサポートされていません。
  • サポートされているヘッダー行は 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. 組み込みのExcel コネクタは読み取り専用です。 データをエクスポートするには、CSV や Parquet などのサポートされている書き込み形式を使用します。

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

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

  • Last updated on