locked
read data from excel using c# & inster it in mySql table RRS feed

  • Question

  • User590655761 posted

    I have one excel sheet with multiple schools shown bellow

    I want to read 1st read only all school names & school address & insert them in SchoolInfo table of mySql database.

    After that I want to read data for each school & insert it in StudentsInfo table which has foreign key associated with SchoolInfo table.

    I am reading excel sheet something like this.

          public static void Import(string fileName)
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                                 ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

                var output = new DataSet();

                using (var conn = new OleDbConnection(strConn))
                {
                    conn.Open();

                    var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    if (dt != null)
                        foreach (DataRow row in dt.Rows)
                        {
                            string sheet = row["TABLE_NAME"].ToString();

                            var cmd = new OleDbCommand("SELECT * FROM [+"+sheet+"+]", conn);
                            cmd.CommandType = CommandType.Text;

                            OleDbDataAdapter xlAdapter = new OleDbDataAdapter(cmd);

                            xlAdapter.Fill(output,"School");
                        }
                }

            }

    Now I am having data in datatable of dataset, Now how do I read desired data & insert it in my sql table.

    Tuesday, April 2, 2013 9:34 AM

Answers

  • User1289186222 posted

    Loop through the table in the dataset output.Tables["School"].Rows.

    Then process each row to insert into your database

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 2, 2013 1:53 PM