On Mon, 10 Jan 2005 20:06:12 -0600, Aaron Rouse <[EMAIL PROTECTED]> wrote: > And if using Oracle you could use SQLLDR. I have the need to do this > just with CF and been using a CFC I got off this list for uploading it > but still have not found the time to really progress with it. > > On Mon, 10 Jan 2005 16:53:15 -0700, Paul Malan <[EMAIL PROTECTED]> wrote: > > If you're using SQL Server, you can create a DTS package then call it > > from CF. I couldn't believe the difference in speed when I switched > > to DTS. (If you need to use CF to validate the incoming data, use DTS > > to populate a temp table, CF to pull it out and insert the validated > > data into its permanent home. I'd wager still much quicker than using > > a loop over cffile contents, though I'd be curious to know if you wind > > up trying both...) > > > > > > On Mon, 10 Jan 2005 17:36:09 -0500, Ken <[EMAIL PROTECTED]> wrote: > > > Hi. I want to enable the user to upload a csv file, then the data > > > should be validated against pre-defined criteria for different columns > > > in the csv file. Once that is done, I want to write the validated > > > rows from the csv file to the DB. > > >
CSVs are much trickier than a lot of people realize to handle in my experience. For example, there are two separate CSV formats -- one the world uses and one Microsoft uses. How do you know which type you're going to get? Secondly, did you know that you can have carriage returns within a given field, and it's perfectly valid? There goes the #Chr(13)##Chr(10)# delimiter check. Lastly, ColdFusion by itself is going to be pretty slow and chew up lots of RAM as you increase the size of the CSV file. You might be able to plan for current requirements (basic validation, small files, etc.), but what about down the road? I just finished a project that needed to take multiple CSV files and do all sorts of validation on the data before inserting into the database. We wound up going with a multi-tiered solution: 1) First upload the file and do a basic file extension check via ColdFusion to make sure it's a CSV file 2) Use the awesome Ostermiller utilities at http://ostermiller.org/utils/CSV.html to actually parse the CSV file, which returns a clean array with the data that you can then use ColdFusion to loop over (i.e., to check file headers to make sure they match what you're expecting). Simply download the jar file from the site I mentioned, drop it in your lib directory, restart ColdFusion, and off you go. Here's the CF syntax needed to use it if you get stuck: <cffile action="READ" file="#fileNameToRead#" variable="fileContents" /> <cfset parser = createObject("java", "com.Ostermiller.util.ExcelCSVParser") /> <cfset parsedFile = parser.parse(fileContents) /> <cfdump var="#parsedFile#" /> 3) At this point, you can do your specific data validation. This part I'll leave to you because we bought a third party product that cost us a ton of cash but has been worth every penny (we needed to geocode, do a point-in-polygon analysis, and validate data ranges and stuff on potentially tens of thousands of records at a time, and this product is lightning quick at it...better than ColdFusion could ever be). Hope this helps get you on your way. Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189909 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
