It sounds like your client needs a smarter application. When your client uploads their XLS document, you can add functionality that displays back to them the first line of the excel spreadsheet and have them identify which column is filled with which data...( email|first name|last name|company)
Then you can instruct whichever method you end up using to correlate the correct column with the correct data. William ---------------------------------- William Seiter -----Original Message----- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 5:51 PM To: cf-talk Subject: Re: Avoiding a boat load of queries inserting multiple records - Better Way? > Not knowing much about your overall process, I'll try to stick to some > generic ideas. Let's assume you don't need this stuff dumped into the > database the very second it gets sent to you. This is actually part of a client's admin system on their website. They send out legal newsletters. LOTS of legal newsletters. And they have boats loads of various list they can send them too. Some lists go to 15,000 or more subscribers at a time. They want to be able to import as many list as they want with as many records at any time they want by just clicking the "import list" button... This app have been around for awhile. They just upgraded their server, so up to this point the import function was using the POI utility. Even when they entered a "legal" spreadsheet into the system, if it had too many records or extra crap for POI to chew through, the whole thing would time out. They KNOW that the damn spreadsheet is supposed to be formatted a very specific way for this to work, and there's a template they're supposed to use, but clients being clients, they will attempt to shove ANYTHING in there at times, and then yell when it doesn't import. Even the concept of "the email address HAS to be the first column" just gets ignored a lot. This particular application has been a huge challenge from day one, mostly because the rules get ignored half the time, so the app kicks the spreadsheet back out at them and demands it be reformatted, and they yell about it thinking that it should be able to import whatever they try to put in. Ain't never gonna happen. Really, how hard is: email|first name|last name|company? But I see sheets with hidden columns, paragraphs of extra stuff, formulas (what the heck for? It's supposed to be a list of addresses!!), email address in the 9th column when the app is specifically set to ignore anything past the first four ... it's unreal. Since the server upgrade, sheets that would time out seem to get parsed pretty quickly by cfspreadsheet functions. I'm happier with it now. But, I'm trying to improve on the efficiency of the whole thing by optimizing the queries, because I KNOW that running 20,000 queries to import 5000 email addresses isn't a good thing as far as cpu cycles go. I'm having my local SQL Server expert meet with me tomorrow and we'll get a stored procedure written to take care of all this in one fell swoop... Clients, huh?? Sheesh! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
