Coldfusion Lists, values like “,,,”

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>

2 comments on this post.
  1. cturner999:

    TO Admin:

    First, I appreciate your post and want to ask for some advice.

    I am experiencing the same problem where I have an empty column as you described above; when the loop finds an empty column it breaks.

    Looks like this. value1, value2, value3,,value5,,value7 (just like your ex.)

    I am using the following script below, and want to know if I can implement your script along with mine in order to replace the ,, situations with either a space or all together just skip the import of that column(null value would be okay in the DB)

    My import method:

    INSERT INTO Table1 (
    VALUE_1, VALUE_2, VALUE_3, VALUE_4, VALUE_5
    )
    VALUES (
    ‘#listgetAt(‘#RC#’,1, ‘,’)#’, ‘#listgetAt(‘#RC#’,2, ‘,’)#’, ‘#listgetAt(‘#RC#’,3, ‘,’)#’, ‘#listgetAt(‘#RC#’,4, ‘,’)#’, ‘#listgetAt(‘#RC#’,5, ‘,’)#’
    )

    ** Where would I implement your script in order to fix the empty column situations ?

    ** Thank you in advance for your help.

  2. cturner999:

    Some of my post got cut off…

    I am using the Cffile command… and cfloop List…

    I hope I was not stupid/confusing with my post 😉

    Thanks.

Leave a comment