Mapping user spreadsheet columns to database fields

Question

I’m not sure where to start on this project. I know how to read the contents of the excel spreadsheet, I know how to identify the header row, I know how to loop over the contents. I believe I have the UX portion worked out but I am not sure how to process the data.

I’ve googled and only found .Net solutions but I’m looking for a ColdFusion/Lucee solution.

I have a working form allowing me to map a user's spreasheet column to my database values (this is being kept simple for this post; user does not have direct access to the database).

Now that I have my data, I'm not sure how to loop over the data results. I believe there will be several loops (an outer and an inner). Then of course I also need to loop over the file contents but I think if I can get the headings mapped out,I can figure out the remaining.

Any good links, tutorials, or guides would be greatly appreciated.

Some pseudo code might be enough to get me started.

User uploads form  
System reads headers and content. 
User is presented form with a list of columns from their uploaded spreadsheet to match with available database fields (eg “column1” matches “customer name”. 
User submits form. 
Now what?

UPDATED Here is what the data looks like AFTER the mapping has been done in my form. The column deliiter is the ::: and within the column the ||| indicates the ID associated with the selected column value. I've included the id and the column value since I plan on displaying the mapping again as a confirmation. Having the ID saves a trip to the database.

Answer 1

If I understand correctly, your question is: how do you provide the user a form allowing them to map their spreadsheet columns to that of the database

Since you have their spreadsheet column names, and you have the database column names, then this problem is essentially a UI/UX problem. You need to show both lists, and allow the user to map them. I can imagine several approaches to this. My first thought would be some sort of drag/drop operation, as follows:

Create a list of boxes, one for each field in your database table, and include the field name in (or above) the box. I'll call this the db field list. Then, create another list for each column from the spreadsheet, which I'll call the spreadsheet column list. The user would drag/drop items from the spreadsheet column list to the db field list.

When a mapping has been completed by the user, you would store the column/field names in as data for the DOM element of the db field list box. Then upon submission, you would acquire the mapping data by visiting each box and adding it to an array. Then you would serialize that array into JSON and send that to your form submission handler.

This could be difficult or easy, depending on your knowledge of UI implementations using JavaScript. jQuery makes this easy (if you know jQuery). There's even a jquery UI plugin that does this: https://jqueryui.com/droppable/.

A quick search for javascript drag drop would help, and here's a few articles I found:

https://www.w3schools.com/html/html5_draganddrop.asp

https://medium.com/quick-code/simple-javascript-drag-drop-d044d8c5bed5

You would also need to submit the array of mappings using javascript. You could search for that as well, and here's an article I found:

https://codereview.stackexchange.com/questions/94493/submit-an-array-as-an-html-form-value-using-javascript