I've tested using OBDC to upload CSV files into queries and have found that
they do not handle the following situation properly:
Small Heart, with ""Red" decorations
is translated as
Small Heart, with "Red
and
Small Heart, with PINK "decorations"
becomes
Small Heart, with PINK
The following code handles this correctly:
<!---
Object: ParseCSV
Purpose: Include to parse a CSV string named lstCSV and return in array named
aryCSV
Author: Andy Ousterhout(ACO)
Date: 3/3/2002
Version: V1.0
Usage: CSV files have fields in a record separated by commas, ",". However,
if a text field contains an embededd comma, the entire field is placed in
double quotes. For example,
00007,"Bear, Swimming",Test
00008,"Frank ""The Animal"" Hooter", testing
00009, Testing, "Frank ""The Animal"" Hooter"
Should each result in 3 fields being parsed.
Change Record:
Who: When: Why:
--->
<cfscript>
aryCSV = ArrayNew(1);
// Only do stuff if something passed ....
if (len(lstCSV) GT 0) {
// First, add a comma onto the end so that the search for ", and not "",
always finds something unless bad CSV string.
// Then, replace any ",," with ", ," so that individual array elements will
be found
lstWork = Trim(lstCSV) & ",";
lstWork = replace(lstWork, ",", ", ", "ALL");
// Then loop thru string, parsing off to next comma and checking if
complete field
// Note that if the field has imbedded quotes, this routine will create bad
data.
// For example, [This is a test, "test"] will come through as ["This is a
test, ""test""]
// and will be parsed into two fields, [This is a test, "] and ["test"].
The calling routine will
// need to handle the possibility that more fields will be returned then
expected.
Do {
// If Line starts with quote, next field is next quote+comma that is not
part of a quote+quote+comma
if (left(lstWork, 1) EQ '"') {
tmpStr = Right(lstWork, len(lstWork)-1);
EndPos = REFInd('([^"]",) | ("*"",)', tmpStr);
// If nothing found, look for "", ending
if (EndPos EQ 0) {
EndPos=REFInd('([^"]",) | (["]*["",])', tmpStr);
EndPos=REFInd('("",)', Mid(tmpStr, EndPos+1, len(tmpStr)))+EndPos;
}
// If still nothing found, Error. Stop work immediately
if (EndPos EQ 0) {
lstwork = "";
NextField = "";
}
Else {
NextField = mid(trim(lstWork), 2, EndPos);
if (len(lstWork)-(EndPos+2) EQ 0)
lstwork = "";
else
lstWork = ltrim(right(lstWork, len(lstWork)-(EndPos+3)));
}
}
else if (left(lstwork, 1) EQ ",") {
lstWork = ltrim(right(lstWork, len(lstWork)-1));
NextField = " ";
}
else {
NextField = Trim(ListFirst(lstWork, ","));
lstWork = LTrim(ListDeleteAt(lstWork, 1, ","));
}
//Append the new field, converting CSV's double quotes to single quotes
ArrayAppend(aryCSV, Replace(NextField, '""', '"', "all"));
//writeoutput(",[#lstwork#], [#NextField#]");
}
while (len(lstWork) GT 1);
}
</cfscript>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
