aidp-excel

# `aidp-excel` — Excel (.xlsx) ingestion Two ways to land Excel data in Spark: the native Spark Excel format (faster, parallel) or a pandas-mediated CSV path (no cluster setup). ## When to use - User has `.xlsx` / `.xls` files in a Volume or Object Storage bucket. - Mentioned: "Excel", ".xlsx", "spreadsheet ingestion". ## When NOT to use - For CSV files → just use [`aidp-object-storage`](../aidp-object-storage/SKILL.md). Spark reads CSV natively. ## Option C — Pure-stdlib parser (no openpyxl, no JARs) The plugin ships a stdlib-only `.xlsx` reader. **No** `openpyxl`. **No** Crealytics JAR. Works on AIDP clusters that have neither PyPI access nor Maven access for the Crealytics dependency closure. ```python import os from oracle_ai_data_platform_connectors.excel import read_xlsx_stdlib xlsx_path = os.environ["EXCEL_PATH"] header, *body = read_xlsx_stdlib(xlsx_path) df = spark.createDataFrame(body, schema=header) df.show() ``` Limitations: read-only (no stdlib path to write .xlsx), first sheet only by default (pass `sheet_path="xl/worksheets/sheet2.xml"` for others), best-effort cell type coercion. Good for ingestion of small-to-medium workbooks; for big files (>50 MB) prefer Option A's `com.crealytics.spark.excel` JAR for parallel reads. The implementation is at [scripts/oracle_ai_data_platform_connectors/excel.py](../../scripts/oracle_ai_data_platform_connectors/excel.py). ## Option A — `com.crealytics.spark.excel` format ### Cluster prerequisite Upload the Crealytics Spark Excel jar (and its Apache POI dependencies) to a Volume and attach via the cluster Library tab: | JAR | Maven coordinates | |---|---| | spark-excel | `com.crealytics:spark-excel_2.12:3.5.0_0.20.4` (matches Spark 3.5; pick the `_<spark-ver>_<release>` matching your cluster) | | poi | bundled with spark-excel; if missing, add `org.apache.poi:poi-ooxml:5.2.5` and transitive deps | ```python import os excel_path = os.environ["EXCEL_PATH"] # e.g. /Volumes/default/default/uploads/data.xlsx df = (spark.read .format("com.crealytics.spark.excel") .option("header", "true") .option("inferSchema", "true") .option("dataAddress", "'Sheet1'!A1") # optional — default is first sheet, A1 .load(excel_path)) df.show() ``` Strengths: parallel reads on large workbooks; predicate pushdown. ## Option B — pandas → CSV → Spark (no jars) ```python import os, pandas as pd excel_path = os.environ["EXCEL_PATH"] csv_path = excel_path.replace(".xlsx", ".csv") # Read with pandas (single-threaded, in-driver) pdf = pd.read_excel(excel_path) # Convert to CSV in the same Volume / Object Storage path pdf.to_csv(csv_path, index=False) print(pdf.head()) # Re-read as Spark for distributed downstream work df = spark.read.csv(csv_path, header=True, inferSchema=True) df.show() ``` Strengths: no cluster JAR install. Tradeoff: driver-side single-threaded read; OOM risk for files >500 MB. ## Gotchas - **`com.crealytics.spark.excel` jar version must match the cluster's Spark version.** A 3.4 jar on a 3.5 cluster errors out at format registration time. - **`dataAddress` for multi-sheet files** — `"'Sheet 2'!A1"` (note quotes around sheet name with spaces). - **`inferSchema=true` is slow** for big files — pre-declare schema with `.schema(...)` for production jobs. - **Encoding / merged cells** — pandas handles most quirks; the Spark Excel jar can choke on merged-cell headers. If you see misaligned columns, prefer Option B. - **Excel files in `oci://`** — both options work; pass `oci://bucket@ns/path/file.xlsx` directly, or pre-stage to `/Volumes/...` for repeated reads. ## References - Official sample: [oracle-samples/oracle-aidp-samples → `data-engineering/ingestion/Read_excel_data/read_excel.ipynb`](https://github.com/oracle-samples/oracle-aidp-samples/blob/main/data-engineering/ingestion/Read_excel_data/read_excel.ipynb) - Crealytics Spark Excel: <https://github.com/crealytics/spark-excel>