You must Sign In to post a response.
  • Category: .NET

    Using C# how to import excel file into ms access database dynamic table

    Using C# how to import excel file into ms access database dynamic table
  • #334934
    Create an Excel sheet you want to transfer data from. Let's assume the file is named Book.xls and the first sheet is the default sheet Sheet1.

    Add reference to Microsoft Office Access Interop Assembly.

    Right click on added reference's property to ensure that the Path of the assembly points to GAC.

    e.g.:

    C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Access\10.0.4504.0__31bf3856ad364e35\Microsoft.Office.Interop.Access.dll

    Remove any previously created Access file and create a new one to import data into.


    if (File.Exists(@"C:\Book.mdb"))

    {

    File.Delete(@"C:\ Book.mdb");

    }

    Access.Application _accessData;

    _accessData = new Access.ApplicationClass();

    _accessData.Visible = false;

    _accessData.NewCurrentDatabase(@"C:\ Book.mdb");

    _accessData.CloseCurrentDatabase();

    _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
    _accessData = null;

    Now let's establish connection to our data source (Excel file) using Microsoft Jet OLE DB provider.



    string _filename = @"C:\Book.xls";

    string _conn;

    _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + _fileName + ";" +

    "Extended Properties=Excel 8.0;";
    OleDbConnection _connection = new OleDbConnection(_conn);

    Use OledbCommand object to select all the data from sheet1 and execute a ExecuteNonQuery to import data into Book.mdb.

    OleDbCommand _command = new OleDbCommand();

    _command.Connection = _connection;

    try

    {

    _command.CommandText = @"SELECT * INTO [MS Access;Database=C:\Book.mdb].[Sheet1] FROM [Sheet1$]";

    _connection.Open();

    _command.ExecuteNonQuery();

    _connection.Close();

    MessageBox.Show("The import is complete!");

    }

    catch (Exception)

    {

    MessageBox.Show("Import Failed, correct Column name in the sheet!");
    }

    This will create a new mdb data file called Book.mdb on your disc containing Sheet1 data. You will notice that table Sheet1 in Book.mdb file has taken the first rows of Sheet1 as the column names. That means that first row of your sheet contains header row data. The attribute "HDR=yes;" in connection string specifies this.

    ///////////////////////////

    //call this method by supplying it the Data Source file //name, which in the example is Book.xls

    public static void CheckUpdateDBFile(string filename)

    {

    if (File.Exists(@"C:\Book.mdb"))

    {

    File.Delete(@"C:\Book.mdb");

    }

    Access.Application _accessData;

    _accessData = new Access.ApplicationClass();

    _accessData.Visible = false;

    _accessData.NewCurrentDatabase(@"C:\Book.mdb");

    _accessData.CloseCurrentDatabase();

    _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);

    _accessData = null;

    OleDbConnection _connection = MakeExcelConnection(filename);

    FillAccessDatabase(_connection);

    }



    private static OleDbConnection MakeExcelConnection(string fileName)

    {

    string _conn;

    _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + fileName + ";" +

    "Extended Properties=Excel 8.0;";

    OleDbConnection _connection = new OleDbConnection(_conn);

    return _connection;
    }


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.