I need to load excel spreadsheets into SQL Server on a daily basis. It's simple enough to build an SSIS package but, the problem that I am facing is that the client is allowed to name the column headers as they like, the columns can be in whatever order, and the column count can vary.
This is the best I can get the owner to agree to.
- Each client's excel file will have one column that is the same no matter what. This column will be the Tax ID Number.
- Each client must use the same format on their excel spreadsheet each time they submit their file.
This is what the final product must look like.
- I need to be able to load automate loading all the excel data and headers into a SQL Server database without losing any data with each client having varying column counts.
- the asp.net mvc application must be able to query a tax id that is entered by the customer. The query must return the headers from the excel spreadsheet and all the corresponding data associated with that record.
I tried to get better consistency but, this is the best I can get. I have normally worked with data that had predefined parameters so, I would appreciate any help with suggestions to help me move this forward.