Creating Excel file with StringBuilder, running slow

A Repasky

I have attached a file with the code I am talking about. I am converting a program from VB.NET and I thought StringBuilder would be much more efficient. I took the concatenation and changed it to StringBuilder, but it is running for ever. Or is it because the insert is taking so long? I commented out the concatenated code, just in case I needed it again.

This is taking too long for one file and I have to make multiple files. The Excel file starts with a blank file with only a header row.

I hope someone can help me with this.

Thanks,
arep

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh15y ago
Accepted answer
Please paste your connection string, remember to set dummy values for confidential information in the connectionstring before pasting here.
Javeed M Shaikh15y ago
great! good work.
A Repasky15y ago
I found the problem. I had to take the creating of the Connection object and the Command object out of the method and out of the loop. Once I did that it ran really fast. Where it had been running more then a day, it only took a little over 3 minutes.

arep
Javeed M Shaikh15y ago
did you take a look at the code i have uploaded?
A Repasky15y ago
Here is the connection string
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\MMOExtract\\extract\\"
+ sFilename + ".xls;" + "Extended Properties=Excel 8.0";
Javeed M Shaikh15y ago
i tried your code with my connection string and inserting one record and it was very quick: attached is the code and the excel file with data: