Importing a text file

bjoz wrote:

 

It looks like what is happening is when my process hits the first line in the txt file where price(column5) has a null value, it's shifting the string discount(column6) value into column 5.

That is the documented and expected, if mightily frustrating for many developers, behavior for empty list values in ColdFusion.

The usual work around is some pre-processing using string functions of your choice to place some type  of 'null' character or string into those places.  There are at least a few functions already built to do this for you on the http://www.cflib.org site.

If you have access to ColdFusion 9, I understand they have finaly heard our pleas, and provided a paramter to tell the list functions to NOT ignore empty list elements.  But if you don't have access to 9, that is probably not much help. :-)

In addition, since you are using MS SQL you might want to try using a simple BULK INSERT instead of looping.

...I understand they have

finaly heard our pleas, and provided a paramters to tell the

list functions to NOT ignore empty list elements. 

Wohoo! I did not know that. 'Bout darned time too.

Here are some snippets where we process a pipe delimited file.

//make sure every list element has a value
thisRow=REreplace(thisRow, '\r\n', ' ','ALL');//remove crlfs
thisRow = Trim(REReplace(ThisRow, "\|(?=\|)","|null","all"));  // this line replaces all || s with |null|
if (right(ThisRow, 1) is "|") 
    ThisRow = ThisRow & "null";

dot dot dot

<!--- possible nulls --->
<cfloop list="2,3,4,5,6,11,12,13,14,18" index="element" delimiters=",">
<cfscript>
if (ListGetAt(ThisRow, element, "|") is "null") {
variables[RTrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = true;
}
else {
variables[Rtrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = false;
}

dot dot dot

insert into micro_temp
(#ThisColumnList#)
values
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#ptnumber#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#admitdate#" null="#admitdate_null#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#regno#" null="#regno_null#">
, <cfqueryparam cfsqltype="cf_sql_char" value="#admitphys1#" null="#admitphys1_null#">
, <cfqueryparam cfsqltype="cf_sql_char" value="#orderphys#" null="#orderphys_null#">

Could it be simpler to follow Ian's tip and replace the empty string with NULL, like this

<cfset token = arrayNew(1)>

<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">

    <cfquery name="importtxt" datasource="#client.DSN#" username="#client.UserName#" passWord="#client.PassWord#">

    INSERT INTO products (product, description, unitofmeas, SOH, Price, discount)

    VALUES

    (

    <cfloop index="i" from="1" to="6">

            <cfset token[i] = getToken(index,i, '|')>

            <cfif trim(token[i]) is "">NULL<cfelse>'#token[i]#'</cfif>

             <cfif i LT 6>,</cfif>

        </cfloop>

    )

</cfquery>

</cfloop>

Try that with date and integer fields and get back to us on how simple it is.

Try that with date and integer fields and get back to us on how simple it is.

I think there is no need to try that, as Bjoz has apparently settled for text fields.

Regarding,

I think there is no need to try that, as Bjoz has apparently settled for text fields.

Fair enough.  Now try it where the last field in any line of text does not have a value.  See if your array has the correct number of elements.