How to insert data from excel file into microsoft localdb database using C#

Question

I am trying to insert data from exel files to Microsoft SQL server local db table. I can show a dataGridview with the excel data in a windows form application but I cant figure out how to put the data into a table.

try
{   
    System.Data.OleDb.OleDbConnection cnn;
    System.Data.DataSet DtSet;
    System.Data.OleDb.OleDbDataAdapter cmd;

    cnn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\CGData\\user\\Desktop\\Book1.xls';Extended Properties = Excel 8.0");
    cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", cnn);
    cmd.TableMappings.Add("Table", "TestTable");
    DtSet = new System.Data.DataSet();
    cmd.Fill(DtSet);
    dataGridView1.DataSource = DtSet.Tables[0];
    cnn.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

What Else do I need to do to copy the Data into a localDB Database table?

fra · Accepted Answer · 2019-11-20 12:41:23Z

0

You already have a datatable (DtSet.Tables[0]), all you need is to bulkcopy that datatable to SQL. check this answer: Insert entire DataTable into database at once instead of row by row?

answered Nov 20, 2019 at 12:41

fra

1861 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert data from excel file into microsoft localdb database using C#

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related