Examples: Load Multiple Files - crealytics/spark-excel GitHub Wiki
Purpose: Load multiple Excel files into single data frame
Dataset
Spark Excel supports loading multiple excel files with glob pattern as well as Key=Value structured folder. For example, in test's resource folder, there is an example ca_dataset:
src/test/resources/spreadsheets/ca_dataset/
└── 2019
├── Quarter=1
│ └── ca_03.xlsx
├── Quarter=2
│ ├── ca_04.xlsx
│ ├── ca_05.xlsx
│ └── ca_06.xlsx
├── Quarter=3
│ ├── ca_07.xlsx
│ ├── ca_08.xlsx
│ └── ca_09.xlsx
└── Quarter=4
├── ca_10.xlsx
├── ca_11.xlsx
└── ca_12.xlsx
5 directories, 10 files
Note: Names and IDs in this ca_dataset are not real.
With Glob Pattern
This dataset can be loaded with glob pattern
ROOT="/<please-change>/to/your-path"
df = spark.read.format("excel") \
.option("header", True) \
.option("inferSchema", True) \
.load(f"{ROOT}/src/test/resources/spreadsheets/ca_dataset/2019/*/*.xlsx")
With glob pattern, we can load subset of data by using a matching pattern
df = spark.read.format("excel") \
.option("header", True) \
.option("inferSchema", True) \
.load(f"{ROOT}/src/test/resources/spreadsheets/ca_dataset/2019/Quarter=4/*.xlsx")
Key=Value Folder Structure
Or, relies on known structure of Key=Value
df = spark.read.format("excel") \
.option("header", True) \
.option("inferSchema", True) \
.load(f"{ROOT}/src/test/resources/spreadsheets/ca_dataset/2019")
df.printSchema()
df.groupBy("Quarter").count().show()
Got
root
|-- Day: integer (nullable = true)
|-- Month: integer (nullable = true)
|-- Customer ID: string (nullable = true)
|-- Customer Name: string (nullable = true)
|-- Standard Package: integer (nullable = true)
|-- Extra Option 1: integer (nullable = true)
|-- Extra Option 2: integer (nullable = true)
|-- Extra Option 3: integer (nullable = true)
|-- Staff: string (nullable = true)
|-- Quarter: integer (nullable = true)
+-------+-----+
|Quarter|count|
+-------+-----+
| 1| 7|
| 3| 186|
| 4| 224|
| 2| 161|
+-------+-----+
With Quarter
column and its values are populated from directory structure.
References
- #74 Allow reading multiple files specified as a list OR by a pattern
- #97 Reading multiple files
- #185 Cannot read/load folder in spark
- #230 Can you support reading directories?
- #371 Unable to Partition while saving as Excel file
- #365 Add option for pathGlobFilter and partitioning
- #396 Not able to read multiple xlsx files together