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
Thank you for your comments James, especially the comment about using an array - that's a great tip! :)