CODEWITHSUNDEEP
coldfusioncoldfusion-10cfspreadsheet
user3779216
user3779216

Reputation: 75

How to read populated columns only with CFSpreadsheet

I am trying to use <cfspreadsheet> to read data from a spreadsheet and insert that data into a database table. Let's say my table has 10 possible columns: FName,LName,MName, Address1,Address2,Address3,city,state,zip and email. Users will be able to submit data using Excel, but they don't have to submit data for all 10 columns. They could just submit data for FName,Email or FName, LName, City, Email. My spreadsheet code should be able to read only the columns submitted in their Excel file, instead of reading all the blank columns.

I thought by using the "columnnames" attribute of <cfspreadsheet> I can instruct cfspreadsheet to just read data from those column names, unfortunately I was wrong OR I did it wrong?

Results of CFSpreadsheet Code

Here is my cfspreadsheet code:

 <cfspreadsheet action="read" 
     src="#Trim(PathToExcelFile)#" 
     columnnames="#Trim(Form.UserColumnList)#" 
     excludeheaderrow="true" 
     query="FindData">
 </cfspreadsheet>

Mapping List and resulting Query

Upvotes: 0

Views: 872

Answers (1)

Leigh
Leigh

Reputation: 28873

user3779216 wrote: ... apparently each institution (there are 9 of them) will have a different excel layout. I may be able to create a UI for the users to map col names from their excel to col names from my table, such as, a drop down of column names from excel and a drop down of column names from my table and let user select which col from excel match which col from my table.

Actually that would make it dead simple. Just feed an ordered list of columns into cfspreadsheet, the way you were originally doing, and the query column names will automatically be mapped correctly.

For example, if the user uploaded the layout below: Uploaded File Layout

.. and selected this column order:

  • Zip,Address1,LName,FName,Email,MName,Address2,City,State

Feeding that list into cfspreadsheet produces a query with desired column mappings, which you can use as normal. (If needed, add code to detect and skip optional header names in the first row.)

CFSpreadsheet query results

<cfspreadsheet action="read" 
     src="#variables.pathToFile#"
     columnNames="#form.OrderedColumnNames#"
     query="sheetData" />

<cfdump var="#variables.sheetData#">

NB: Obviously the real code should include validation of the input list. Ensure it contains all of the required columns and was not tampered with to include invalid characters, etcetera.

Upvotes: 0

Related Questions