Hi all,
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!
