Importing from Excel to MySQL using C#

This post will tell you how to import data stored in Microsoft Excel(97-2003) spreadsheets into a MySQL database using C#.

The whole operation is basically divided into 3 steps:
1. Read from the Excel sheet
2. Save the data into variables
3. Write the data from the variables into the MySQL database.

You will also need to download the MySQL ODBC driver from here

1. Reading from Excel sheet

First you must create an OleDB connection to the Excel file.

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

Here path refers to the location of your Excel spreadsheet. E.g. "D:\abc.xls"
Then you have to query your table. For this we must define names for the table and columns first. Click here to find out how.
Now create the command object.

OleDbCommand selectCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", xlConn);

Now we have to store the ouput of the select command into a DataSet using a DataAdapter

OleDbDataAdapter xlAdapter = new OleDbDataAdapter();
objAdapter1.SelectCommand = selectCmd;
DataSet xlDataset = new DataSet();
xlAdapter.Fill(xlDataset, "XLData");

2. Save the data into variables

Now extract cell data into variables iteratively for the whole table by using
variable = xlDataset.Tables[0].Rows[row_value][column_value].ToString() ;

3. Write the data from the variables into the MySQL database

Now connect to the MySQL database using an ODBC connection

String mySqlConnectionString = "driver={MySQL ODBC 5.1 Driver};" +
"server=localhost;" + "database=;" + "user=;" + "password=;";
OdbcConnection mySqlConn = new OdbcConnection(mySqlConnectionString);
mySqlConn.Open();

Construct your INSERT statement using the variables in which data has been stored from the Excel sheet.

OdbcCommand mySqlInsert = new OdbcCommand(insertCommand, mySqlConn);
mySqlInsert.ExecuteScalar();