Importing a text file

[bjoz]

Hi,

i've been happily importing a 1.5mb "|" delimited txt file into my web database for the past few years using cfhttp with the code below;

----------------------------------------------------

<cfhttp method="get" textqualifier=" " firstrowasheaders="no" delimiter="|" username="xxxxx" password="xxxxxx" name="test" url="http://xxxxxxxx/xxx/xxxxxxx.txt">

  
<cfloop query="test">

<cfquery datasource="#client.DSN#" username="#client.UserName#" passWord="#client.PassWord#">
INSERT INTO products (product, description, unitofmeas, SOH, Price, discount)
VALUES ('#test.column_1#', '#test.column_2#', '#test.column_3#', '#test.column_4#', '#test.column_5#', '#test.column_6#')
</cfquery>

</cfloop>
-----------------------------------------------------

Recently my web server is throwing an error "Request aborted due to heavy load" and the transaction fails. I suspect my web host has limited or put some restriction on cfhttp or something like this that is causing my process to fail. Until i get some answers from my web host I'm trying alternate methods that may be more efficient to do the update  but im having all sorts of problems with null value float values (columns - SOH, Price).

------------------------------------------------------

<cffile action="read" file="d:\inetpub\xxxxxx\xxx\xxxxxxxx.txt" variable="txtfile">

<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
('#gettoken('#index#',1, '|')#',
'#gettoken('#index#',2, '|')#',
'#gettoken('#index#',3, '|')#',
#gettoken('#index#',4, '|')#',
'#gettoken('#index#',5, '|')#',
'#gettoken('#index#',6, '|')#')
</cfquery>

</cfloop>

----------------------------------------------------

when i run the code above i get the following error.

----------------------------------------------------

[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to float.

INSERT INTO products_bretttest (product, description, unitofmeas, SOH, Price, discount) VALUES ('43667', 'OBSTAPEWIPES8051X150-OBS', 'ROL', '0.000', 'C3', '')

--------------------------------------------------

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.

Does anybody know what i can do to handle these null value floats in my text file to get around the error? Or if someone has a better or more efficient way to read a text file and insert it into a dtabase table, that would be great as well?

Thanks in advance

Brett

[ilssac]

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. 🙂

[-__cfSearching__-]

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.

[Dan_Bracuk]

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#">

[BKBK]

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 = getToken(index,i, '|')>

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

             <cfif i LT 6>,</cfif>

        </cfloop>

    )

</cfquery>

</cfloop>

[Dan_Bracuk]

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

[BKBK]

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.

[Dan_Bracuk]

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.

[bjoz]

Thanks for all the help guys. Still working through it but your posts have helped