Skip navigation
bjoz
Currently Being Moderated

Importing a Text File to Database

Apr 3, 2013 3:07 AM

Tags: #cfhttp #text_file

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>

 

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points