If you were using a the correct custom delimiter for your file ...that's quite a bug, and quite incredible that it has gone undetected for all these years.
If you left the DSN on the default of CSV delimited...expect crazy things like that to happen, because the example you give is _not_ a valid csv file. -- jon mailto:[EMAIL PROTECTED] Friday, June 27, 2003, 5:06:46 PM, you wrote: AO> I've tested using OBDC to upload CSV files into queries and have found that AO> they do not handle the following situation properly: AO> Small Heart, with ""Red" decorations AO> is translated as AO> Small Heart, with "Red AO> and AO> Small Heart, with PINK "decorations" AO> becomes AO> Small Heart, with PINK AO> The following code handles this correctly: AO> <!--- AO> Object: ParseCSV AO> Purpose: Include to parse a CSV string named lstCSV and return in array named AO> aryCSV AO> Author: Andy Ousterhout(ACO) AO> Date: 3/3/2002 AO> Version: V1.0 AO> Usage: CSV files have fields in a record separated by commas, ",". However, AO> if a text field contains an embededd comma, the entire field is placed in AO> double quotes. For example, AO> 00007,"Bear, Swimming",Test AO> 00008,"Frank ""The Animal"" Hooter", testing AO> 00009, Testing, "Frank ""The Animal"" Hooter" AO> Should each result in 3 fields being parsed. AO> Change Record: AO> Who: When: Why: AO> ---> AO> <cfscript> AO> aryCSV = ArrayNew(1); AO> // Only do stuff if something passed .... AO> if (len(lstCSV) GT 0) { AO> // First, add a comma onto the end so that the search for ", and not "", AO> always finds something unless bad CSV string. AO> // Then, replace any ",," with ", ," so that individual array elements will AO> be found AO> lstWork = Trim(lstCSV) & ","; AO> lstWork = replace(lstWork, ",", ", ", "ALL"); AO> // Then loop thru string, parsing off to next comma and checking if AO> complete field AO> // Note that if the field has imbedded quotes, this routine will create bad AO> data. AO> // For example, [This is a test, "test"] will come through as ["This is a AO> test, ""test""] AO> // and will be parsed into two fields, [This is a test, "] and ["test"]. AO> The calling routine will AO> // need to handle the possibility that more fields will be returned then AO> expected. AO> Do { AO> // If Line starts with quote, next field is next quote+comma that is not AO> part of a quote+quote+comma AO> if (left(lstWork, 1) EQ '"') { AO> tmpStr = Right(lstWork, len(lstWork)-1); AO> EndPos = REFInd('([^"]",) | ("*"",)', tmpStr); AO> // If nothing found, look for "", ending AO> if (EndPos EQ 0) { AO> EndPos=REFInd('([^"]",) | (["]*["",])', tmpStr); AO> EndPos=REFInd('("",)', Mid(tmpStr, EndPos+1, len(tmpStr)))+EndPos; AO> } AO> // If still nothing found, Error. Stop work immediately AO> if (EndPos EQ 0) { AO> lstwork = ""; AO> NextField = ""; AO> } AO> Else { AO> NextField = mid(trim(lstWork), 2, EndPos); AO> if (len(lstWork)-(EndPos+2) EQ 0) AO> lstwork = ""; AO> else AO> lstWork = ltrim(right(lstWork, len(lstWork)-(EndPos+3))); AO> } AO> } AO> else if (left(lstwork, 1) EQ ",") { AO> lstWork = ltrim(right(lstWork, len(lstWork)-1)); AO> NextField = " "; AO> } AO> else { AO> NextField = Trim(ListFirst(lstWork, ",")); AO> lstWork = LTrim(ListDeleteAt(lstWork, 1, ",")); AO> } AO> //Append the new field, converting CSV's double quotes to single quotes AO> ArrayAppend(aryCSV, Replace(NextField, '""', '"', "all")); AO> //writeoutput(",[#lstwork#], [#NextField#]"); AO> } AO> while (len(lstWork) GT 1); AO> } AO> </cfscript> AO> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
