I have thousands of CSV files that have similar but non-identical headers under a single directory. The structure is as follow:
path/to/files/unique_parent_directory/*.csv
One csv file can be:
|Column_A|Column_B|Column_C|Column_D|
|V1 |V2 |V3 |V4 |
The second CSV file can be:
|Coulmn_A|Coulmn_B|Coulmn_E|Coulmn_F|
|V5 |V6 |V7 |V8 |
The result I want to create is a single Spark Dataframe that merges the files correctly without overlapping columns, the output for the previous example should be like this:
|Column_A|Column_B|Column_C|Column_D|Coulmn_E|Coulmn_F|
|V1 |V2 |V3 |V4 |Null |Null |
|V5 |V6 |Null |Null |V7 |V8 |
The code I am using to create the dataframes is:
val df = sparkSession.read
.format("csv")
.option("header", "true")
.option("inferSchema", "true")
.option("mergeSchema", "true")
.load(path/to/files/unique_parent_directory/*.csv)
.persist(StorageLevel.MEMORY_AND_DISK_SER)
But I get the following result:
|Column_A|Column_B|Column_C|Column_D|
|V1 |V2 |V3 |V4 |
|V5 |V6 |V7 |V8 |
Is there a way to obtain the desired dataframe without running a header unification process?