Membaca dan melakukan streaming file Excel

Azure Databricks mencakup dukungan bawaan untuk membaca .xls dan .xlsx file, menghilangkan kebutuhan akan pustaka eksternal atau konversi file manual. Anda bisa membaca lembar apa pun dari buku kerja multi-lembar, menargetkan rentang sel tertentu, secara otomatis menyimpulkan skema dan tipe data, dan bekerja dengan nilai rumus sebagai hasil komputasinya. Excel file dapat dibaca dari penyimpanan cloud atau diunggah langsung di UI Tambahkan Data, dan mendukung beban kerja batch dan streaming menggunakan Auto Loader.

Prasyarat

Membaca dan mengalirkan file Excel memerlukan Databricks Runtime 17.1 atau lebih tinggi dan Auto Loader untuk beban kerja streaming.

Opsi

.option() Gunakan metode .options() dan DataFrameReader untuk mengonfigurasi sumber data Excel. Untuk daftar lengkap opsi yang didukung, lihat DataFrameReader opsi Excel dan DataFrameWriter opsi Excel.

Usage

Contoh berikut menunjukkan pembacaan file Excel menggunakan batch Spark (spark.read) dan API streaming. Secara default, pengurai membaca semua sel dari kiri atas ke sel non-kosong kanan bawah di lembar pertama; dataAddress gunakan opsi untuk menargetkan lembar atau rentang sel tertentu. Skema disimpulkan secara otomatis, atau Anda dapat menentukan sendiri.

Membuat atau mengubah tabel di UI

Anda dapat menggunakan antarmuka pengguna Buat atau ubah tabel untuk membuat tabel dari file Excel. Mulailah dengan muat file Excel atau pilih file Excel dari volume atau lokasi eksternal. Pilih lembar, sesuaikan jumlah baris header, dan tentukan rentang sel secara opsional. UI mendukung pembuatan satu tabel dari file dan lembar yang dipilih.

Membaca file Excel

Anda dapat membaca file Excel dari penyimpanan cloud (misalnya, S3, ADLS) menggunakan spark.read.excel atau fungsi SQLread_files.

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

Mengalirkan file Excel menggunakan Auto Loader

Anda dapat melakukan streaming file Excel menggunakan Auto Loader dengan mengatur cloudFiles.format ke excel. Contohnya:

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

Mengimpor file Excel menggunakan COPY INTO

Gunakan COPY INTO untuk memuat file Excel dari penyimpanan cloud ke dalam tabel Delta secara idempoten.

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

Daftar lembar

Anda dapat mencantumkan lembar dalam file Excel menggunakan operasi listSheets. Skema yang dikembalikan adalah struct dengan bidang berikut:

  • sheetIndex:panjang
  • sheetName:String

Contohnya:

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

Mengurai lembar Excel yang tidak terstruktur kompleks

Untuk lembar Excel yang kompleks dan tidak terstruktur (misalnya, beberapa tabel per lembar, pulau data), Databricks merekomendasikan untuk mengekstrak rentang sel yang Anda butuhkan untuk membuat Spark DataFrames menggunakan opsi dataAddress.

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

Keterbatasan

  • File yang dilindungi kata sandi tidak didukung.
  • Hanya satu baris header yang didukung.
  • Nilai sel yang digabungkan hanya mengisi sel kiri atas. Sel anak yang tersisa diatur ke NULL.
  • Streaming Excel file menggunakan Auto Loader didukung, tetapi evolusi skema tidak. Anda harus secara eksplisit mengatur schemaEvolutionMode="None".
  • "Strict Open XML Spreadsheet (Strict OOXML)" tidak didukung.
  • Eksekusi makro dalam .xlsm file tidak didukung.
  • Opsi ignoreCorruptFiles tidak didukung.

FAQ

Temukan jawaban atas tanya jawab umum tentang konektor Excel di Lakeflow Connect.

Dapatkah saya membaca semua lembar sekaligus?

Pengurai hanya membaca satu lembar dari file Excel pada satu waktu. Secara default, ia membaca lembar pertama. Anda dapat menentukan lembar yang berbeda menggunakan dataAddress opsi . Untuk memproses beberapa lembar, pertama-tama ambil daftar lembar dengan mengatur opsi operation ke listSheets, lalu iterasi atas nama lembar dan baca masing-masing dengan memberikan namanya pada opsi dataAddress.

Bisakah saya menyerap file Excel dengan tata letak kompleks atau beberapa tabel per lembar?

Secara default, pengurai membaca semua sel Excel dari sel kiri atas ke sel non-kosong kanan bawah. Anda dapat menentukan rentang sel berbeda menggunakan opsi dataAddress.

Bagaimana rumus dan sel gabungan ditangani?

Rumus diserap sebagai nilai komputasinya. Untuk sel gabungan, hanya nilai kiri atas yang dipertahankan (sel anak adalah NULL).

Bisakah saya menggunakan penyerapan Excel di Auto Loader dan pekerjaan streaming?

Ya, Anda dapat melakukan streaming file Excel menggunakan cloudFiles.format = "excel". Namun, evolusi skema tidak didukung, jadi Anda harus mengatur "schemaEvolutionMode" ke "None".

Apakah Excel yang dilindungi kata sandi didukung?

Tidak. Jika fungsionalitas ini sangat penting untuk alur kerja Anda, hubungi perwakilan akun Databricks Anda.

Sumber daya tambahan

  • Membaca dan menulis file CSV: Jika sumber data Anda dapat mengekspor ke CSV, CSV adalah format yang lebih sederhana dengan dukungan alat yang lebih luas dan tidak ada dependensi pada parser khusus.
  • Last updated on