From Excel to Eccess db in one click event

[TobbeK]

Can anyone provide a quick solution for INSERT the entire excel sheet into a access database.

This is a click button event that just opens a excel sheet in a windows form. What I like to see is some example how to INSERT the same excel data into a access db table within this click event. If possible within the same SQL query.

private void openToolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }

        private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                System.Data.DataSet myDataSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                openFileDialog.InitialDirectory = "c:\\";
                openFileDialog.Title = "Select file";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;
                
                try
                {
                    String connectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectString);
                    myConnection.Open();

                    //Get SheetNames
                    DataTable sheetTable = myConnection.GetSchema("Tables");
                    DataRow rowSheetName = sheetTable.Rows[0];
                    String sheetName = rowSheetName[2].ToString();

                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", myConnection);

                    //MyCommand.TableMappings.Add("Table", "TestTable");

                    myDataSet = new System.Data.DataSet();
                    MyCommand.Fill(myDataSet);
                    dataGridView1.DataSource = myDataSet.Tables[0];
                    myConnection.Close();

                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    toolStripStatusLabel1.Text = "Done";
                }

            }
        }

[kvprajapati]

Have a look,

string cnstr = "";
            cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder\Book1.xls;Extended Properties=Excel 8.0;";
            OleDbConnection cn = new OleDbConnection(cnstr);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "SELECT * INTO [MS Access;Database=C:\\test1.mdb].[Sheet1] FROM [Sheet1$]";
            cmd.Connection = cn;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();

[TobbeK]

Thanks
I will take a quick look and respond here with feedback soon...

[TobbeK]

I tried to implement the suggested code into my current query like this one below. No errors, but nothing happens during the event. I must miss something important here !?

private void openToolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }

        private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                System.Data.DataSet myDataSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                openFileDialog.InitialDirectory = "c:\\";
                openFileDialog.Title = "Select file";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;
                
                try
                {
                    String connectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectString);
                    myConnection.Open();

                    //Hämta SheetNames
                    DataTable sheetTable = myConnection.GetSchema("Tables");
                    DataRow rowSheetName = sheetTable.Rows[0];
                    String sheetName = rowSheetName[2].ToString();

                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * INTO [MS Access;Database=C://xport_data.mdb].[orderid] FROM [" + sheetName + "] WHERE orderid='Z20091892' ", myConnection);

                    //MyCommand.TableMappings.Add("Table", "TestTable");

                    myDataSet = new System.Data.DataSet();
                    MyCommand.Fill(myDataSet);
                    dataGridView1.DataSource = myDataSet.Tables[0];
                    myConnection.Close();

                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    toolStripStatusLabel1.Text = "Finished";
                }

            }
        }

[kvprajapati]

Line # 38,39 - SELECT Into query cannot return rows (result).

[TobbeK]

OK
Is there a possible solution for this within the SQL query or do I need to execute this separately. However, no data has been imported to the access db.

[kvprajapati]

Yes, you have to execute query separately.

[TobbeK]

Thanks!

OK, I am completely stucked here, really need to see a pointer for opening the excel sheet and from there to export data from one column to one column in the access db. The problem is that I see no errors and no data export happens between excel and access.

Usually there is only one datasource involved and I have some hard time to see through the entire process.

[kvprajapati]

>I am completely stucked here..

Try to write simple code (see my post #2) and see what happens.

[TobbeK]

Hi
Yes I did, and this is what I can do so far. 2 different connection strings and 2 different SQL querys. The problem is still to get any data from the excel query into the INSERT to access db. The code below has a static value INSERTED into one column for test purpose. Need to fill the InsertCommand with the excel dataset in some way. Can't find a way to use the excel dataset.

private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }

        private void openToolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }

        private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                System.Data.DataSet myDataSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                openFileDialog.InitialDirectory = "c:\\";
                openFileDialog.Title = "Select fil";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;
                
                try
                {
                    String connectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectString);
                    myConnection.Open();

                    //Get SheetNames
                    DataTable sheetTable = myConnection.GetSchema("Tables");
                    DataRow rowSheetName = sheetTable.Rows[0];
                    String sheetName = rowSheetName[2].ToString();

                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", myConnection);

                    myDataSet = new System.Data.DataSet();
                    MyCommand.Fill(myDataSet);
                    dataGridView1.DataSource = myDataSet.Tables[0];


                    //myConnection.Close();

                    String InsertconnectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C://xport_data.mdb";
                    System.Data.OleDb.OleDbConnection InsertConnection = new OleDbConnection(InsertconnectString);

                    OleDbCommand InsertCommand = new OleDbCommand();
                    InsertCommand.CommandText = "INSERT INTO order(orderid) VALUES ('K1234567') ";
                    InsertCommand.Connection = InsertConnection;
                    InsertConnection.Open();
                    InsertCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    toolStripStatusLabel1.Text = "Finished";
                }

            }
        }

[TobbeK]

I still have the same problem, cannot find a way to pull data from the existing DataSet and use that in the INSERT INTO query. The use of static values works fine to INSERT and the DataSet is used to fill a DataGrid.

So my problem is how to communicate with the DataSet and reuse the data?

private void openToolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }

        private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                System.Data.DataSet myDataSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                openFileDialog.InitialDirectory = "c:\\";
                openFileDialog.Title = "Select file";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;
                
                try
                {
                    String connectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectString);
                    myConnection.Open();

                    //Get SheetNames
                    DataTable sheetTable = myConnection.GetSchema("Tables");
                    DataRow rowSheetName = sheetTable.Rows[0];
                    String sheetName = rowSheetName[2].ToString();

                    // Get Excel tables
                    MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT kundorder FROM [" + sheetName + "]", myConnection);

                    // Dataset
                    myDataSet = new System.Data.DataSet();
                    MyCommand.Fill(myDataSet);
                    dataGridView1.DataSource = myDataSet.Tables[0];

                    // Access database connection
                    String InsertconnectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C://xport_data.mdb";
                    System.Data.OleDb.OleDbConnection InsertConnection = new OleDbConnection(InsertconnectString);

                    OleDbCommand InsertCommand = new OleDbCommand();

                    // SQL query
                    InsertCommand.CommandText = "INSERT INTO order(order) VALUES()";

                    InsertCommand.Connection = InsertConnection;
                    InsertConnection.Open();
                    InsertCommand.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    toolStripStatusLabel1.Text = "Finished";
                }

            }
        }

[kvprajapati]

Have a look at,

OleDbDataAdapter adp=new OleDbDataAdapter("select * from tablename","Provider=Microsoft.jet.oledb.4.0;Data source=C:\\xport_data.mdb")

DataTable dt=new DataTable();
adp.Fill(dt);
DataGridView1.DataSource=dt;

[TobbeK]

Hi, maybe don't fully understand your answer

But I already have a SELECT query (from Excel), and DataSet from that query. What I need now is some help to use the current DataSet and INSERT values to my Access database with values from that DataSet. I can't find a way to pick up anything. The datagrid works fine with values from the dataset.

// Get Excel tables
MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT kundorder FROM [" + sheetName + "]", myConnection);

// Dataset
myDataSet = new System.Data.DataSet();
MyCommand.Fill(myDataSet);
dataGridView1.DataSource = myDataSet.Tables[0];

[TobbeK]

Now can at least get the column name from the Excel column, which is the first row in sheet. Anyone who knows how get the values from rows except the column name.

In the code provided below, the Dataset pick up the column name and then it is INSERTED to access db.

string kundorder = myDataSet.Tables[0].Columns["kundorder"].ToString();

InsertCommand.CommandText = "INSERT INTO kundorder(kundorder) VALUES('"+kundorder+"')";

[kvprajapati]

In response to post #13,

private void button2_Click(object sender, EventArgs e)
        {
            openFileDialog();
        }
        private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                System.Data.DataSet myDataSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand=null;

                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                
                openFileDialog.Title = "Select file";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;

                try
                {
                    String connectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectString);
                    myConnection.Open();

                    
                    DataTable sheetTable = myConnection.GetSchema("Tables");

                    myDataSet = new System.Data.DataSet();

                    foreach (DataRow rowSheetName in sheetTable.Rows)
                    {
                        if (rowSheetName[3].ToString() == "TABLE")
                        {
                            String sheetName = rowSheetName[2].ToString();
                            OleDbCommand    cmd= new System.Data.OleDb.OleDbCommand("SELECT * INTO [MS Access;Database=C:\\test1.mdb].[" + sheetName  +"] FROM [" + sheetName + "]", myConnection);
                            cmd.ExecuteNonQuery();
                            MyCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
                            MyCommand.Fill(myDataSet, sheetName);
                        }
                    }
                    dataGridView1.DataSource = myDataSet.Tables[0];
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                     MessageBox.Show(ex.Message);
                }
            }
        }

[TobbeK]

Thanks adatapost!
awesome!
You have just saved me a lot of headache

Is it possible to change the ["+sheetName+"] in the SELECT INTO query to point at a column name in existing Access db table. Now a new table is created with all columns, which I am very happy for, but I must also fill different tables.

I tried this:
"SELECT * INTO [MS Access;Database=C:\\test.mdb].[orderid] FROM ["+sheetName+"].[orderid]", myConnection;

I also tried this:
"SELECT orderid INTO [MS Access;Database=C:\\test.mdb].[orderid] FROM ["+sheetName+"]", myConnection;

maybe I am on completely wrong track trying to handle the excel sheet like a regular database table?

[TobbeK]

OK last try

The code is rewritten in parts.
New is the for loop where I try to catch the excel column values from dataset. It is necessary becourse I need to split those into database tables.

Anyone, why are the rows empty in the for loop??

private void openFileDialog()
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                
                openFileDialog.Filter = "Microsoft Excel|*.xls|Microsoft Access|*.mdb";
                openFileDialog.InitialDirectory = "c:\\";
                openFileDialog.Title = "Välj fil";

                openFileDialog.ShowDialog();
                string filename = openFileDialog.FileName;

                try
                {
                    excelConnectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + openFileDialog.FileName + ";Extended Properties=Excel 8.0";

                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(excelConnectString);
                    myConnection.Open();

                    //Get SheetNames
                    DataTable sheetTable = myConnection.GetSchema("Tables");
                    DataRow rowSheetName = sheetTable.Rows[0];
                    sheetName = rowSheetName[2].ToString();

                    // Get exceldata
                    SqlSelect = ("SELECT * FROM [" + sheetName + "] ");
                    OleDbDataAdapter SelectCommand = new OleDbDataAdapter(SqlSelect, myConnection);
                    
                    // New DataSet
                    DataSet myDataSet = new DataSet();
                    //myDataSet = new System.Data.DataSet();
                    SelectCommand.Fill(myDataSet);
                    dataGridView1.DataSource = myDataSet.Tables[0];

                    String InsertconnectString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C://eltel/xport_data.mdb";
                    System.Data.OleDb.OleDbConnection InsertConnection = new OleDbConnection(InsertconnectString);
                    OleDbCommand InsertCommand = new OleDbCommand();


                    DataTable myTable = myDataSet.Tables[0];
                    for (int i = 0; i < myTable.Rows.Count; i++)
                    {

                        DataRow myRow = myTable.Rows[i];

                        Aonr = (int)myRow["Aonr"];
                        orderid = (string)myRow["orderid"];

                        InsertCommand.CommandText = "INSERT INTO order(orderid) VALUES('" + orderid + "')";
                        InsertCommand.Connection = InsertConnection;
                        InsertConnection.Open();
                        InsertCommand.ExecuteNonQuery();
                    }
                    InsertConnection.Close();

                    myConnection.Close();

                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    toolStripStatusLabel1.Text = "Finished";
                }

            }
        }