Question posted 2011 · +1 upvotes
Does anyone have an elegant suggestion for how to get the contents of an Excel spreadsheet into SQL Server via a web form? I need to allow our clients to upload modest amounts of structured data, and I need that data to ultimately reside in a sql table. I really can’t expect the clientele to produce anything but an Excel file, but I could require that it be an xlsx.
The web app is written in Coldfusion; it doesn’t need to be able to handle huge numbers of simultaneous requests, but I don’t want to consider some sort of server-side batch job processing or shunt the user to an asp.net page (which is what we are doing now).
Any recommendations (or examples of how others are successfully doing this) would be appreciated. Due to the sensitivity of the data, we really can’t do anything to compromise the security of the web or sql servers.
Accepted answer +11 upvotes
If you are using CF9, then you could easily use the cfspreadsheet tag too. I mention this one specifically because Shawn’s link did not (presumably due to its being relatively new on the CF scene). Here’s the livedoc link: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html
For full use, I would create a web form with a standard file upload field. On the backend handling the form submission, get a copy of the file with
<cffile action="upload" destination="uploaded.xls".....>
Then use:
<cfspreadsheet action="read" query="myExcelData" src="uploaded.xls" ...>
At which point, your spreadsheet content will be available as a query object. You can then loop over this query, running insert queries into your sql server each time you loop. That should do it.
2 code variants in this answer
- Variant 1 — 1 lines, starts with
<cffile action="upload" destination="uploaded.xls".....> - Variant 2 — 1 lines, starts with
<cfspreadsheet action="read" query="myExcelData" src="uploa…
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.