Consolidating data based on headers (1 Viewer)

I am building a data consolidation tool in access.

There are multiple Excel files from different vendors, each vendor has different format and for some years data the format keeps changing for the same vendor.

I worked on a single vendor and created a unique field list for that particular vendor and based on the unique field list, mapped required fields to the master tables field list and uploaded data to master table. I was able to upload the data to the database correctly.

Now since I have to work on more then 20 vendors with different formats, I am trying to collect the unique headers for each vendors manually but this is very time consuming. I created an Excel macro to collect the headers in Excel for each file and collect the required fields from these files. Still I have to work on 5300 excel files manually and get the fields from these files.

The issue I am facing is there can be multiple headers of same name as that of master table field.

Is there some other way to get the headers mapped to the master table field list and upload these to a table in Access table.

Something like get all the header in a table for each file and select the required fields to map it to Master table.

I have 15 fields in Master table and the input files can have more then 15 or less then 15 fields in the data table.

Any suggestions would be highly appreciated

You lost me here:

The issue I am facing is there can be multiple headers of same name as that of master table field.

Is there some other way to get the headers mapped to the master table field list and upload these to a table in Access table.

Click to expand...

So there could be 2 'FirstName' fields in the same file? Perhaps you can demonstrate this with data.

One of my gigs is consolidating mailing lists from 60 sources every month. And 20 of them seem to forget that we do this every month and start from scratch each time producing a file that doesn't match anything they've ever sent.

I solved this with 2 R scripts (https://www.r-project.org/). I have a mapping file whcih will convert their columns to the ones I want ('Address'->'a1', 'AddressLine1'->'a1', 'Street Address'->a1, etc.). I dump all their files in a directory, run an R script to find new flelds these yahoos came up with and add them to my mapping file. Next, I run a processing script that goes through all those files and extracts the data I need from the file using the mapping file. It generates a master import file with the column headings I want regardless of what they used.

Even if you don't use this entire method, R makes it very easy to scrape a ton of data files of their column headings.