Transferring data from an Excel file into a MySQL table

Question

I have an Excel 2003 spreadsheet that I need to import into MySQL. I have a table called dev_products with two fields; product_id and product_description. The Excel file has exactly the same column structure - but I don't know how to transfer the data (through code - as I will have to do this again).

I am able to connect to the file....but that's about as far as I have got....and I really need some help.

Thanks - as always.

    const string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strExcelFilenameandLocation + "; Extended Properties=Excel 8.0;";
    OleDbConnection xlConn = new OleDbConnection(connectionString);
    xlConn.Open();

    string strQuery = "??????????";

    var con = new MySqlConnection(ClsVariables.StrDb);
    con.Open();
    MySqlCommand command = new MySqlCommand(strQuery, con);
    command.ExecuteNonQuery();
    con.Close();
    xlConn.Close();

Answer 1

Save the excel file in csv

1. Direct insert to db using mysql bulk insert BULK INSERT in MYSQL

2. Read the data from csv using the function given below The function will return the data in DataTable Extract the data and create mysql query to insert in db

   using System.IO;
   using System.Windows.Forms;
   using System.Data;
   public class readCSV
   {
   public DataTable getDataTable()
   {
       DataTable dTable = new DataTable();
       try
       {
           using (StreamReader readFile = new StreamReader(this.filename))
           {
               string line;
               string[] row;
               int rowcount = 0;
               DataRow dRow;
   
               while ((line = readFile.ReadLine()) != null)
               {
                   row = line.Split(',');
                   dRow = dTable.NewRow();
   
                   for (int i = 0; i < row.Length; i++)
                   {
                       if (rowcount == 0)
                       {
                           dTable.Columns.Add(row[i]);
                       }
                       else
                       {
                           dRow[dTable.Columns[i]] = row[i];
                       }
                   }
   
                   if (rowcount != 0)
                   {
                       dTable.Rows.Add(dRow);
                   }
                   rowcount++;
               }
           }
       }
       catch (Exception e)
       {
           MessageBox.Show(e.Message);
       }
   
       return dTable;
   }
   }
   

Answer 2

Simple way to directly import using sql command

Save as text (CSV) and use LOAD DATA INFILE LOCAL in the mysql command line client to load the file ...

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

--

Ref : http://forums.mysql.com/read.php?32,216343,216344#msg-216344