csv array with gaps

[Guest]

I am inserting a csv into a table, but some of the csv "fields" are empty and need to be NULL so that it can move on to the next field. I've tried creating a cfparam with a default of "" but that doesnt work. not sure where to head now... the code works perfectly fine until there is an empty field.

any help here?
thanks.

[Kronin555]

Replace this:
<cfloop index="csvCell" list="#csvRecord#" delimiters=",">
<cfset csvArray[variables.csvCellCount] = csvCell>
<cfset csvCellCount = csvCellCount + 1>
</cfloop>

with this:
<cfset csvArray = ListToArray(csvCell,",",true)>
<cfset csvCellCount = ArrayLen(csvArray)>

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_l_21.html#131626

[Guest]

I'm sorry i should have described my situation more clearly. I am using CF7 which ListToArray only takes 1 or 2 parameters. I tried doing ListToArray(csvCell,",")> But, i get the following error in the log...

quote:

---

Complex object types cannot be converted to simple values.The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. <p> The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a &lt;CFIF&gt; tag. This was possible in ColdFusion 2.0 but creates an error in later versions.

---

[Guest]

Actually, now that i have tweaked it alittle, it is still just skipping over the blank areas. since i am using cf7, i cannot use the includeEmptyFields parameter. does anyone know how to do this without the parameter.

Thanks.

[elDonrico · Accepted solution]

Try just using this.

[Guest]

very cool. it worked better than i had expected... over 480,000 fields in less than 15seconds. thanks elDonrico.

[Dan_Bracuk]

I approach these matters a bit more methodically. First, for every field that is allowed to be null, I'll do something like this:

if (something)
FieldxNull = true;
else
FieldxNull = false;

Then when I get to the query, I do this
<cfqueryparm null="#FieldxNull#">