Skip to main content
B
bjoz
Participant
January 13, 2010
Question

Importing a text file

  • January 13, 2010
  • 2 replies
  • 915 views

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

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
January 14, 2010

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
Community Expert
BKBKCommunity Expert
Community Expert
January 15, 2010

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>

D
Dan_Bracuk
Inspiring
January 15, 2010

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

ilssac
ilssac
Inspiring
January 13, 2010

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__-
Inspiring
January 13, 2010

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices