I have some script which loops through a number of similarly structured Excel files, pulls out the field headers, and dumps these into a single table - example as follows:
Exposure.FileName
Exposure.Sheet
Exposure.A
Exposure.B
Exposure.C
Exposure.D
Exposure.E
Exposure.F
Exposure.G
Exposure.H
Exposure.I
Exposure.J
Exposure Report 2014 - 2018 ROI.xlsx
ROI
Record Id
Broker Name
Client Reference
Geolocation
Trade Name
Policy Type
Start Date
End Date
Name of Insured & Business Name
Eircode
Exposure Report 2014 - 2018 UK.xlsx
UK
Record Id
Broker Name
Client Reference
Geolocation
Scheme Name
Sub Trade
Policy Type
Start Date
End Date
Name of Insured & Business Name
Exposure Report 2018 - 2019 UK.xlsx
UK
Record Id
Broker Name
Client Reference
Geolocation
Trade Name
Policy Type
Start Date
End Date
Name of Insured & Business Name
Postcode
Exposure Report 2018 - 2019 ROI.xlsx
ROI
Record Id
Broker Name
Client Reference
Geolocation
Trade Name
Policy Type
Start Date
End Date
Name of Insured & Business Name
Eircode
Exposure Report Jan 2020.xlsx
UK
Broker Name
Client Reference
Geolocation
Trade Name
Policy Type
Start Date
End Date
Name of Insured & Business Name
Postcode
Address Line 1
Exposure Report Jan 2020.xlsx
ROI
Broker Name
Client Reference
Geolocation
Trade Name
Policy Type
Start Date
End Date
Name of Insured & Business Name
Postcode
Address Line 1
As you can see, the field headers for some of the files change over time.
Where I'd like to get to is a single table which captures all field names (once), which I can then use as a template to load in data from the files above - so a table like the following:
Record ID
Broker Name
Client Reference
Geolocation
Trade Name
Scheme Name
Sub Trade
Policy Type
Start Date
End Date
Name of Insured & Business Name
Postcode
Eircode
Address Line 1
I think I need to create a dummy / helper table to load distinct values from the first table, but I'm not quite sure how to achieve this where the headers I'm after appear in the multiple fields shown above (Exposure.A, Exposure.B etc.).
Hope the above makes sense and someone can assist...thanks in advance!
