Hi everyone,

I am working on an application that allows a user to upload a datafile (Excel or CSV) that contains customer data to the database. This file can have different structures. For example:

File 1: Name Surname Address DOB

File 2: Family name DOB Address

This data needs to be entered in a generic table that has a foreign key that links to another entity (one to many relationship).

So somehow the columns in the Excel sheet need to be mapped to the columns in the database table. Ideally the users would be able to do this themselves.

Does anyone know of the easiest way to achieve this? Or has anyone done anything similar?

Rens

Solution

Hi,


There is no easy way, but to build it yourself.

ExcelPackage you can use to read columns/rows/cells on  more finegrained way.

Then you need to have a mapping table to do the mapping.

This can be a little bit hardcoded, or you going to dig into some system-entities (Entity, Entity_Attributes) to fetch the names and maps them accordingly and inserting the dynamically with adv. sql.


That said, Generic Tables are not the best way to handle data. It will increase your maintenance costs!


Solution

Hi J,

Thanks for the quick reply!

I was thinking something similar to your suggestion. In our existing application we have a mapping table that links source field to target field for each record. Ideally the users would upload data files with a generic structure but unfortunately that's not the case a.t.m.

Thanks for the ExcelPackage tip, I will have a look at it.

Rens