Xindi CMS is a small, lightweight and flexible content management system. It's great for web developers looking for a simple way to make site content editable by their clients.
Open source, free and flexible, Xindi is available in CFML and PHP versions.
I am a web developer living and working in Exeter, Devon (United Kingdom). I use my blog to document and share the things I learn and I hope fellow developers will find it to be a useful resource.
Friday, December 23, 2011
Importing data from a spreadsheet to a database table using ColdFusion
I'm currently working on a project where I need to import a list of products from a spreadsheet into a database table. Here is the ColdFusion script I have writted to complete the task.
When I import data from Excel, I intentionally duplicate the header row so that all data is imported as a "VarChar" datatype. I've found that if this is not done and the first couple rows have some empty cells, new queries datatypes aren't always correctly identified. (Coldfusion is typeless and can automatically identify dates & numbers anyhow.) I then start the loop at first row containing the actual data. I've had to also add isDefined() checks before the loop because sometimes the client forgets to add certain columns to the spreadsheet. This enables the script to determine if optional columns exist and set an optional user-defined default value if they don't.
Regarding performance, I'd recommend converting the "failedimports" variable into an array. Working with an array is both faster and safer. You never know if/when a product code will intentionally or accidentally have a comma. (I've had some admin-only scripts that took over 2 minutes to execute and converting lists to arrays reduced the time considerably.)
Wednesday, December 28, 2011
Simon Bingham
Thank you for your comments James, especially the comment about using an array - that's a great tip! :)
