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();
Subscribe to:
Post Comments (Atom)
variable can not pass to mysql
ReplyDeleteprivate void btnpath_Click(object sender, EventArgs e)
ReplyDelete{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel Files(.xls)|*.xls|Excel Files(.xlsx)|*.xlsx| Excel Files(*.xlsm)|*.xlsm";
openFile.ShowDialog();
txtpath.Text = openFile.FileName;
}
private void btnimport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = Parse(txtpath.Text, "Sheet1");
DataTable dt1 = new DataTable();
dt1 = RemoveNullColumnFromDataTable(dt);
//string ssqlconnectionstring = "Data Source=SAILEE-PC;Initial Catalog=Mahimkar;User ID=sa;Password=bombay@19";
string mycon = ("Server=localhost;Database=sanil;Uid=root;");
string ssqltable = "employee";
//string sclearsql = "delete from " + ssqltable;
MySqlConnection con = new MySqlConnection(mycon);
//SqlConnection con = new SqlConnection(ssqlconnectionstring);
//SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
con.Open();
//sqlcmd.ExecuteNonQuery();
// con.Close();
MySqlBulkLoader bulkcopy = new MySqlBulkLoader(con);
bulkcopy.TableName = ssqltable;
bulkcopy.Equals(dt1);
//con.Close();
}
public static DataTable Parse(string fileName, string workSheetName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
string query = string.Format("SELECT * FROM [{0}$]", workSheetName);
DataTable data = new DataTable();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(data);
}
return data;
}
public DataTable RemoveNullColumnFromDataTable(DataTable dt)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][1] == DBNull.Value && dt.Rows[i][2] == DBNull.Value && dt.Rows[i][3] == DBNull.Value)
dt.Rows[i].Delete();
}
return dt;
}
private void button1_Click(object sender, EventArgs e)
ReplyDelete{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string path = System.IO.Path.GetFullPath(ofd.FileName);
string querry = "SELECT * FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
OleDbDataAdapter adapter = new OleDbDataAdapter(querry, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
dataGridView1.DataSource = dataSet.Tables[0];
departmentsTableAdapter.Update(mbmsDataSet.departments);
}
else
{
ofd.Dispose();
}
}