0 Replies Latest reply: Apr 3, 2013 3:07 AM by bjoz RSS

    Importing a Text File to Database

    bjoz

      Hi There,

       

      Years ago with limited Coldfusion experience I created a Cold fusion process (below) that deletes the contents of a database table and inserts the contents of a 7mb text file which is passed via FTP down to our web server at regular intervals. Admittingly the code below is probably clunky to the more experienced, however i have found this process to work for the last 4 years without a problem. Recently my web server (shared hosted environment) has been throwing this error when this page runs,

      "Request aborted due to heavy system load.",

       

      I asked my host if there has been any changes, extra sites recently added to the server that may be causing this congestion which they naturally replied "no". I even tried reducing the text file size to about 3mb but still got the same issue. Is there any other more efficient coldfusion code options I can try that may be quicker and prevent the error from being thrown?

       

      Im on Coldfusion 7 and using MS SQL server, database and coldfusion are on differnt boxes in a shared environment.The text file can contain null values and is delimeted by a pipe character "|"

       

      Any help appreciated

       

       

       

      <cfsetting requesttimeout="600">

       

      <cftransaction>

       

      <cfquery name="delete" datasource="#client.dsn#" username="#client.username#" passWord="#client.password#">

      delete from xxxxxx

      </cfquery>

       

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

       

      Records Read <cfoutput>#test.recordcount#</cfoutput>

       

      <cfloop query="test">

       

      <cfquery name="insert" datasource="#client.dsn#" username="#client.username#" passWord="#client.password#">

      INSERT INTO xxxxx

      (OrderNo, Line, AccountNo, AccountNoShip, Shipment, PurchaseOrder, Completed, OrderDate, ShipmentDate, Product, QtyOrdered, QtyShipped, Price, Carrier, Connote, Delivery1, Delivery2, Delivery3, Delivery4, Delivery5, Delivery6, Delivery7, Delivery8, expected)

      VALUES (

      <cfqueryparam value='#test.column_1#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_2#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_3#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_4#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_5#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_6#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_7#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_8#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_9#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_10#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_11#' cfsqltype="cf_sql_float">,

      <cfqueryparam value='#test.column_12#' cfsqltype="cf_sql_float">,

      <cfqueryparam value='#test.column_13#' cfsqltype="cf_sql_float">,

      <cfqueryparam value='#test.column_14#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_15#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_16#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_17#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_18#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_19#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_20#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_21#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_22#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_23#' cfsqltype="cf_sql_varchar">,

      <cfqueryparam value='#test.column_24#' cfsqltype="cf_sql_varchar">

      )

       

      </cfquery>

      </cfloop>

       

      <cfparam name="OZ" default=""><cfset OZ=#dateAdd("h",18,now())#>

      <cfset OZ=#CreateODBCDateTime(OZ)#>

       

      <cfquery datasource="#client.dsn#" username="#client.username#" password="#client.password#">

      UPDATE

      UpdateTimes

      SET

             time=#OZ#

        WHERE

        id=<cfqueryparam value="1" cfsqltype="cf_sql_integer">

      </cfquery>

       

      </cftransaction>