Slow nested 'for' loop to read Excel objects

Question

I am working on a form that will allow my users to choose a spreadsheet and copy its data to an SQLite database. My SQLite query is transaction and happens in about one second but the portion of my code that is not running right is where it loops through the Excel range and creates my insert statements. It is a nested for loop running over all the columns in the selected range and all the rows. This whole process for a spreadsheet with 360 rows and 43 columns takes about four minutes to run. I initially thought it was the SQLite insert so I converted it to a bulk insert. Once I dug in a bit and stepped through the code I realized it is the 'for' loop causing this. Here is my code:

int counter = 1;
        string contents = null;
        string bulkInsert = null;
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;

        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

        for (int i = 2; i <= rowCount; i++)
        {

                for (int j = 1; j <= colCount; j++)
                {
                    contents = contents + Convert.ToString("'" + xlRange.Cells[i, j].Value2) + "',";


                    //MessageBox.Show(xlRange.Cells[i, j].Value2.ToString());


                }
                contents = contents.Remove(contents.Length - 1);
                bulkInsert = bulkInsert + "INSERT INTO DeferralInput VALUES (" + contents + "); ";
                contents = "";
        }

        bulkInsert = "BEGIN TRANSACTION; " + bulkInsert + " COMMIT;";
        xlApp.Quit();
        ExecuteQuery(bulkInsert);

If anyone has any thoughts on how to speed this up or if I did something wrong I am open to try anything.

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

Importing Excel into a DataTable Quickly

I believe above question should resolve your problem. Individual calls at .value2 is causing the problem. You could copy values into array or datatable and accordingly do manipulations to speed up things.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 25 '15 at 20:23

Maertin

384
1
8

You are correct, sir. Thank you for saving me a huge headache the day before Thanksgiving! This now takes all of a millisecond. – VinnyGuitara Nov 25 '15 at 20:52

Slow nested 'for' loop to read Excel objects

1 Answers1