In coldfusion i’ve ran across the problem of trying to parse a comma seperated list, and inputing it into a database… there lies a work around for empty values in the list, for example.

test.csv ==============

value1, value2, value3,,value5,,value7


it is very hard to get values out of that when there is no space or anything inbetween the commas, say you are trying to take a csv file, and you are trying to make column 1 have 25 characters, and column 2 have 10 characters, and column 3 have 5 characters, you might do this if you have a dumb handheld device, that takes a .csv file, and in order to import a .csv of 1000 items, those items need to be padded, and so on and so forth, so you go about creating a script to take the bad .csv file and you import it with coldfusion, do the work, and find out, it doesnt put space in between the commas so it’s harder to work around… here is the long and short of it… you can use the following function to replace empty values with a “null” value, then do whatever you need to do to the null value, to get the right number of columns…

<cfscript>
function listFix(list) {
var delim = ‘,’;
var null = ” “;
var special_char_list = “\,+,*,?,.,[,],^,$,(,),{,},|,-“;
var esc_special_char_list = “\\,\+,\*,\?,\.,\[,\],\^,\$,\(,\),\{,\},\|,\-“;
var i = “”;

if(arrayLen(arguments) gt 1) delim = arguments[2];
if(arrayLen(arguments) gt 2) null = arguments[3];

if(findnocase(left(list, 1),delim)) list = null & list;
if(findnocase(right(list,1),delim)) list = list & null;

i = len(delim) – 1;
while(i GTE 1){
delim = mid(delim,1,i) & “_Separator_” & mid(delim,i+1,len(delim) – (i));
i = i – 1;
}

delim = ReplaceList(delim, special_char_list, esc_special_char_list);
delim = Replace(delim, “_Separator_”, “|”, “ALL”);

list = rereplace(list, “(“ & delim & “)(“ & delim & “)”, “\1” & null & “\2”, “ALL”);
list = rereplace(list, “(“ & delim & “)(“ & delim & “)”, “\1” & null & “\2”, “ALL”);

return list;
}
</cfscript>