Excel To Access...!!!

[avirag]

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...........

[DdoubleD]

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

[avirag]

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........

[DdoubleD]

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?

[avirag]

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...?

[DdoubleD]

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?

[DdoubleD]

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.