Skip to main content
L
LVSRRC
Participating Frequently
August 8, 2011
Question

inserting csv into sql server using coldfusion

  • August 8, 2011
  • 3 replies
  • 3666 views

Hi,

I am trying to upload a CSV file into SQL Server 2005 DB using coldfusion.

But we are facing performance issues while uploading multiple files at a time.

Logic we implemented is below.

1. Parse the CSV into Array

2. loop over Array and do validation. If validation failes throw error dont upload. if validation passed goto 3.

3. loop over Array and insert each and every record.

Here problem is while uploading file Coldfusion consuming 80% of memory and getting timeouts.

Can you please suggest any good solution.

Thanks and Regards,

LVSR Ravichandra

    This topic has been closed for replies.

    3 replies

    Upen1
    Upen1
    Inspiring
    August 8, 2011

    Good one .

    L
    LVSRRCAuthor
    Participating Frequently
    August 9, 2011

    Hi,

    Thanks for response.

    I have one more question. Is CFLOOP take more memory while executing.

    I observed that while performing CFLOOP memory was increasing to 800 MB and getting timeouts.

    Is there any other alternative there? We are using ColdFusion 9.

    Thanks and Regards,

    LVSR Ravichandra

    A
    Adam Cameron.
    Inspiring
    August 9, 2011

    Is there any other alternative there? We are using ColdFusion 9.


    Did you see Dan's very first comment in reply to your question:

    "The best solution is to use a bulk loading tool instead of coldfusion."

    Don't use CF for dataloading.

    --

    Adam

    Dileep_NR
    Dileep_NR
    Inspiring
    August 8, 2011

    Hi,

    Instead of doing all process in a single page. Do it as a wizard,

    Steps:

    1. Upload CSV

    2. Create a flat table with field      names as CSV heading

    3. Insert(http://forums.asp.net/t/1140851.aspx/1)      values from CSV into that table

    4. Parse the data from the  flat      table and insert into the relevant tables

    5. Drop the Flat tables after the      process

    This is  only a suggestion

    D
    Dan_Bracuk
    Inspiring
    August 8, 2011

    The best solution is to use a bulk loading tool instead of coldfusion.  But, if you must use coldfusion, open the csv file with cfhttp.  This gives you a query object and saves you the step of parsing into an array.

    Next, just loop through the data once and put your data into a staging table.  Then validate the entire dataset with sql, and, if it passes, you are two queries away from completing the task.

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