Hi all, How can i convert the excel worksheet into MS Access database, such that all the fields & data of excel becomes the fields of database, and then i could use that database for adding new item, modify and delete.
I want to do this so as to avoid the typing of all the data entries from excel to my database......
Can anyone help me in this...........

avirag, this will import your excel data into a DataSet. If you need additional help writing the DataSet to Access table, please create another thread. Play with this and see if it works for you...

// NOTE: adaptor always returned error without full path of .xls file
                string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    @"Data Source=c:\ExcelData\stockdata.xls;" +
                    "Extended Properties=Excel 8.0;";
                
                // Metals is the sheetname...
                string query = "SELECT * FROM [Metals$]";

                DataSet ds = new DataSet();

                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    try
                    {
                        conn.Open();

                        using (OleDbDataAdapter cmd = new OleDbDataAdapter(query, connStr))
                        {
                            cmd.Fill(ds, "StockData");
                            return ds;
                        }
                    }
                    catch (OleDbException ex)
                    {
                        Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

Thanks for your reply DdoubleD...........
Well i am making C# win forms application, and as i mentioned , i want to store excel in Access,
So can you please tell me in this thread that how can i store dataset to Access......
And if not than i'll start a new thread for this........

What is the code you have so far for writing to your Access table?

EDIT: Did you test the code I gave you to determine it satisfies your import of the worksheet?

Edited 7 Years Ago by DdoubleD: n/a

What is the code you have so far for writing to your Access table?

EDIT: Did you test the code I gave you to determine it satisfies your import of the worksheet?

Well DdoubleD, i have this code:

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;
}

But i am little bit confused , where i should use this....
So can you please tell me how can i do this...?

Good morning avirag (for me at least). Things are never quick and easy with you my friend. Anyway...

I wrote an ADO COM interface earlier this morning to dynamically build the Access table from a DataTable that I loaded from my spreadsheet data using the method I supplied you with yesterday. Typical morning of fun for me I suppose because I hand't tried doing this with ADO COM before. I already have a database wrapper that builds the SQL commands dynamically to do this, but I was curious about any shortcuts using ADO COM. I got it to work with a few field types, but for it to be complete, I would need to build a wrapper around it too. So, I decided to further my search...

While beginning a search to find other ways this could be done, I found that code you are using to create your Access database in your example code you gave:

Link: http://www.dotnetspider.com/forum/187304-Using-C-how-import-excel-file-into-ms-access-database-dynamic-table.aspx

Remaining code from link you are missing in your example code:

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!");
                }

Why didn't you use that remaining code to SELECT the SHEET into the database? Does it not work or something?

I tested that code to insert the sheet data into the Access table, and I didn't find a problem with it. I made some minor modifications to make it a little more flexible and to provide a more accurate error message on failure.

This method combines the SELECT from the Excel sheet and INSERT into the Access table into one command:

public static void ImportSpreadsheet(string fileName, string sheetName)
            {
                string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                    + @"Data Source=" + fileName + ";"
                    + "Extended Properties=Excel 8.0;";

                // set the Access table name to import the sheet into...
                string tableName = sheetName;

                using (OleDbConnection conn = new OleDbConnection(excelConnStr))
                {
                    try
                    {
                        conn.Open();

                        using (OleDbCommand cmd = new OleDbCommand())
                        {
                            cmd.CommandText = @"SELECT * INTO [MS Access;Database="
                                + accessDatabase + "].["
                                + tableName + "] FROM ["
                                + sheetName + "$]";

                            cmd.Connection = conn;

                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (DbException ex)
                    {
                        Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

Give it a try and mark the thread as solved if there is no problem.

This question has already been answered. Start a new discussion instead.