The carriage return within a "cell" is actually the problem I ran into and never looked back at what I will do to work past it. This requirement is kind of low on my list of things to get done so just keeps moving towards the back burner. The data I have is populated into an Excel spreadsheet and then exported to CSV format from within Excel. Unfortunately this project is hosted in a shared environment and the ability to put anything 3rd party is slim to none in actually happening. If I could just use SQLLDR then it would be a cake walk, but that is not going to be an option. The last time I looked into this I was using "java.io.BufferedReader" and ran into it not liking those carriage returns, had some suggestions on work arounds and will look into those whenever I get back to this need.
-- Aaron Rouse http://www.happyhacker.com/ On Tue, 11 Jan 2005 10:48:11 -0500, Dave Carabetta <[EMAIL PROTECTED]> wrote: > 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). > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189927 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
