On Tue, 11 Jan 2005 12:35:56 -0500, Ben Rogers <[EMAIL PROTECTED]> wrote: > > Which further emphasizes my point that basic ColdFusion solutions of > > looking for carriage returns and the like is not a robust way to solve > > this problem. > > I wasn't debating that point. However, it's worth noting that this is > entirely dependent on the application which generated the CSV file. If the > files are all coming from Excel -- which I believe does not allow embedded > carriage returns -- then scanning for carriage returns is perfectly fine.
Agreed. For the record, it looks like Excell (I'm using 2003) does indeed allow carriage returns. I just ran a quick test (when in the cell, hit Alt+Enter to start a new line within it) and it saved out with the carriage return. > > > Well, I would have said "validated" if I meant to a DTD or XSD or some > > such, and I've never even heard of any "validation" mechanisms for CSV > > files other than parsing the file itself. > > But you did use the word "valid". That is a loaded term on a ColdFusion > mailing list filled with Web developers. I was merely trying to clarify the > statement. Yeah, I guess I can see the confusion. > > Your messages seemed to suggest that there was a right and wrong way to > parse a CSV file. I was simply trying to convey that there are as many > correct ways to parse CSV files as there are different flavors of CSV > because CSV is not a standard. That wasn't my point at all. I was actually pointing out the opposite -- that there were too many different ways to parse CSV files, so the usually common ColdFusion suggestion of looping over rows and looking for carriage returns won't necessarily suffice. > > > Sure, but the same could be said of almost any transport medium. For > > example, an XML file might be valid on one system, but not necessarily > > on another if the DTD/XSD it validates against is different. > > I think you're conflating the issue (possibly because I mentioned DTDs in > the previous message). > > There is an XML standard. Consequently, I can validate the XML document to > ensure that it's properly *formatted*. I can be reasonably sure that, if I > create a standards compliant XML document, that other applications will be > able to parse it. What they do with it from there is up to them. > > It's worth noting that, if they can't, then it's probably their fault. In > rare cases, it may be an ambiguity in the standard. This is not the case > with CSV. The format of a CSV file can be perfectly valid for one > application but invalid for another. I'm with you now. > > XML also supports DTDs, which allow you to validate the *structure* of the > document (as opposed to the format). If a DTD uses an absolute URI (or is > otherwise available on both systems), then the structure of the document can > be validated on both systems. > > Since there is no CSV standard, the format cannot be validated. The closest > thing you get to a DTD in a CSV file is when some applications include the > column names in the first line of the file. This tells you the number of > columns that you can expect. > > > That's a good one. I'm particularly interested in the CSV to XML > > converter they posted. Thanks for the link. > > BTW, I agree with the posts that suggest using DTS, SQLLDR and other such > apps. They've already solved a good deal of the integration problems. DTS in > particular provides a nifty wizard which lets the developer describe the CVS > flavor that you have. I use Oracle here at work, and my experience with SQLLDR is that, while it's an extremely powerful way of bulk loading data, the format of the file needs to be perfect for it to succeed. Hence the suggestion of going through the different steps to verify that you have a validly structured CSV file (with the Ostermiller utilities, etc.), particularly when I have external clients who will mess up their files from time to time. I've not used DTS personally, but by many accounts, it's an invaluable part of the SQL Server product and is a great fit for this sort of work. Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189937 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
