inserting csv into sql server using coldfusion

[LVSRRC]

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

[Dan_Bracuk]

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.

[Dileep_NR]

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

[Upen1]

Good one .

[LVSRRC]

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

[Adam Cameron.]

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