How to load large amount of data to DataTable

Question

I am exporting data from Excel to a DataTable, but I am getting some performance issues when my Excel file contains large amount of rows...

public DataView LoadFromExcel()
{
    Microsoft.Office.Interop.Excel.Application application = 
                           new Microsoft.Office.Interop.Excel.Application();
    Workbook workbook = null;
    Worksheet worksheet = null;
    string filename = null;
    OpenFileDialog file = new OpenFileDialog();
    if (true == file.ShowDialog())
    {
        filename = file.FileName;
    }
    workbook = application.Workbooks.Open(filename, true, true);
    worksheet = workbook.Sheets[1];
    Range range = worksheet.UsedRange;
    int row = range.Rows.Count;
    int columns = range.Columns.Count;
    System.Data.DataTable dt = new System.Data.DataTable();
    for (int i = 1; i <= columns; i++)
    {
        dt.Columns.Add((range.Cells[1, i] as Range).Value2.ToString());
    }
    for (row = 2; row <= range.Rows.Count; row++)
    {
        DataRow dr = dt.NewRow();
        for (int column = 1; column <= range.Columns.Count; column++)
        {
            dr[column - 1] = (range.Cells[row, column] as
                    Microsoft.Office.Interop.Excel.Range).Value2.ToString();
        }
        dt.Rows.Add(dr);
        dt.AcceptChanges();
    }
    workbook.Close(true, Missing.Value, Missing.Value);
    application.Quit();
    return dt.DefaultView;
}

Is there any way I can solve this problem? Please help.

You don't have to load all records to memory in one shot! Reactive Extension (Rx) might be useful to stream your data, and when a record is read, you just subscribe it and handle it at runtime. — David, May 28 '13 at 06:49
Using Interop is slow. You better take a look at .Net libraries that read excel files without it. (Flexcel, Gembox, there's a [lot of them](http://stackoverflow.com/q/15828/861716)). — Gert Arnold, May 28 '13 at 06:56

score 1 · Answer 1 · answered May 28 '13 at 07:00

You can do it with the help of OLEDb provider. I have tried doing for 50000 records. It may help you, just try below code:

        // txtPath.Text is the path to the excel file.
        string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + txtPath.Text + ";" + "Extended Properties=" + "\"" + "Excel 12.0;HDR=YES;" + "\"";

        OleDbConnection oleCon = new OleDbConnection(conString);

        OleDbCommand oleCmd = new OleDbCommand("SELECT field1, field2, field3 FROM [Sheet1$]", oleCon);

        DataTable dt = new DataTable();

        oleCon.Open();
        dt.Load(oleCmd.ExecuteReader());
        oleCon.Close();

You have to take care of few things:

Name of the sheet should be Sheet1 or else give the proper name in the query.
While reading the sheet, sheet should not be open.
The column name should be properly defined in the query
Column name should be on the first row in the sheet

I hope it will help you... Let me know if any thing more you require... :)

score 1 · Answer 2 · answered May 28 '13 at 07:02

1

You can use Sql bulk copy to perform such operation.

answered May 28 '13 at 07:02

Pawan

1,065
5
10

score 1 · Accepted Answer · answered May 28 '13 at 07:11

I think this is not the right approach.

For inserting large amount of data into a table, you should use "Bulk Insert" feature of your database and during bulk insert, you should turn off the database log and roll-back features. Otherwise the bulk insert would act just like bunch of ordinary inserts.

I know Oracle and SQL Server has this feature and some NoSQL databases has it too. Since you have not mentioned what is your database, it helps to google it.

score -1 · Answer 4 · answered May 28 '13 at 07:00

Try reading the values to variables and do some filters in order to avoid sending wrong values that can affect your database. It is wrong to save unknown data to database most expecially MS SQL - do some filtering to make the saving easier and preserve your DB health..

How to load large amount of data to DataTable

4 Answers4