0

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.

dump of form submission after mapping has been performed by user

HPWD
  • 2,232
  • 4
  • 31
  • 61
  • When you say "now what?" are you asking how to insert those fields into their corresponding colums? – Redtopia Oct 12 '18 at 06:32
  • How did the .Net solution do it? Is it translatable to CFML? – Shawn Oct 12 '18 at 14:06
  • I suspect the now what depends on what is being submitted. If you included info on how you are building your form that would be useful – James A Mohler Oct 12 '18 at 15:59
  • Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, [describe the problem](http://meta.stackexchange.com/q/139399/) and what has been done so far to solve it. – Miguel-F Oct 12 '18 at 16:03
  • 1
    I would suggest starting with a very basic, static form. You already know how to read the spreadsheet and get the columns. Just create a static form with the columns from the spreadsheet, On each row, next to those, add a select drop-down with the available database column names. Once you know their selections, you just add the data to the database table. The table columns will be defined by you so they can be static in your form. Or you could get fancy and read them out dynamically. BUT regardless of all that it seems like a really bad idea to let users decide where to put data. – Miguel-F Oct 12 '18 at 16:07
  • It would seem more appropriate to have a job (scheduled task) that processes these spreadsheets and translates the information into the database. Your code will define which spreadsheet columns go where in the database. – Miguel-F Oct 12 '18 at 16:09
  • James regarding the “now” the file itself is being submitted which contains the column headings and the data within of course. Then I picture a new row being prepended above the excel heading row. In here for each cell would be a drop down where the user could select from a list of available database fields (eg a name, address, whatever). Once the mappings have been selected, those values would also be included with the submit. – HPWD Oct 12 '18 at 16:10
  • As for the .Net approach, there is a lot of looping but that’s the best I can tell. I may need to spend more than a few minutes looking at their solution if I am to replicate it. – HPWD Oct 12 '18 at 16:15
  • When I did that kind of import in the past, I used an approach like Miguel-F described. Presented the spreadsheet columns (source) and had a list of database columns (destination) next to each. The user selected the mappings and submitted the form. The action page translated the mappings (source columns 3,1,2) => (target columns 1,2,3) and built the sql statement. Pretty straight forward. We chose to use a mapping table in the database to avoid exposing the actual db column names, but it could be done with static values too. What's your dbms? – SOS Oct 12 '18 at 17:14
  • The form will essentially create the mapping. The user wont have direct access to the actual database fields (I worded it that way for brevity). The dbms is Maria 10.2.x. Ageax: in the past, did you have a 1:1 ratio or were there cases when the user data might contain more columns than what your database would accept? For example, the user might have customer's pet's name whereas we love animals, we don't need to know their pet's name so we'd ignore that column. I mention this because of your 3,1,2 => 1,23 reference. – HPWD Oct 12 '18 at 17:22
  • Both. It was usually a 1:1 mapping, but for files with optional/extra columns, we just added an option like "None" or "Skip Import". Any columns with that option selected were ignored. – SOS Oct 12 '18 at 17:27
  • Excellent, this is a great start! Thank you Ageax and Miguel-F for sticking with me on this. This should be a fun exercise. – HPWD Oct 12 '18 at 17:29
  • 1
    One other thing. I'd strongly recommend the use of staging tables. Importing spreadsheet data directly into production tables is madness! ;-) Always import into temp/staging tables first, then validate, validate, validate... – SOS Oct 12 '18 at 19:25
  • There are intermediate tables in play here. Just trying to keep it basic to avoid that noise. :D Thanks for the recommendation though. – HPWD Oct 12 '18 at 20:05
  • 1
    Gotcha. You just never know.. seen people do some crazy things. Either wya you'll need to loop through the file contents, but ... have you considered using that to generate a temp tab delimited file, then bulk loading it - instead of multiple inserts? – SOS Oct 12 '18 at 20:43
  • No but what a very interesting idea! Would it go something like this, `1` read the contents, `2` get the header row, `3` query the spreadsheet, `4` export to tab delimited file, `5` use MySQL data import to read the spreadsheet data. `6` allow user to map the column headings ? – HPWD Oct 12 '18 at 21:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/181783/discussion-between-ageax-and-hpwd). – SOS Oct 12 '18 at 21:22

1 Answers1

0

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

Redtopia
  • 4,947
  • 7
  • 45
  • 68
  • I hadn't thought about drag-n-drop here but have used it before in the past.Interesting. I'll have to see if it's mobile friendly (specifically tablet friendly). Thank you for the SE link. Upon initial review, that looks like a good read and poses a different way of submitting the data that I hadn't considered. – HPWD Oct 12 '18 at 17:27
  • While this is a perfect UX answer, I need more of a processing the submitted data answer (the "now what"). I do sincerely appreciate you taking the time to formulate your answer. I do apologize for the confusion when I wrote up my initial question. I didn't realize until now that I needed to make it more precise. I've added some clarity. – HPWD Oct 12 '18 at 18:00
  • @HPWD, are you saying you need to know how to process the uploaded spreadsheet and do inserts on the mapped fields? – Redtopia Oct 12 '18 at 18:25
  • No, I've got that part down. I even have the form created allowing the user to select which spreadsheet forms map to which database field. When the user submits this form, I need to loop over the submitted data and reference the spreadsheet data to the one they selected from the drop down. I suspect it'll be a lot of loops and a lot of `listGetAt()`. – HPWD Oct 12 '18 at 18:43
  • I've updated the question to be more on target and specific as to what I'm needing help with. – HPWD Oct 12 '18 at 20:03
  • @HPWD, I assume you've uploaded the file to a temporary directory or someplace where you can access it. Is that correct? In that case, are you looking for some pseudo code to loop through the lines in the CSV file and insert into the db? – Redtopia Oct 12 '18 at 22:11
  • @HPWD - also, I don't understand how a column would have an ID since typically an ID would reference a row. If IDs are being assigned to columns, then you probably need to edit your question to include your schema. – Redtopia Oct 12 '18 at 22:18