How to Import data from excel sheet to Access Database using DataGridView control in C# Windows Form
In this article how to data can be transferred from excel sheet to a table of Access Database through DataGridView using OleDB database connectivity in C#. It is complete apps in which you can import data from a particular formatted excel sheet and insert into a table of Access Database with eliminating duplicate data.
To transfer data from one format (system/file) to another format (system/file) is a common and essential task for a software developer. Here I have explained elaborately with very simple code snippet and developed three functions in C# which are the heart of this apps. Firstly, GetExcel() function is called to load data into DataGridView from excel sheet, InsertData() function work to save data into Access Database from DataGridView and third function name is CheckDuplicate() which play role to check the duplicate data. 
Figure show the form design view.
In Database connectivity with Access Database, I've used the OleDbConnection object which represents the unique connection to data source. All the properties values are set to their initial values after creating a instance of OleDbConnection. Here a single connection OleDbConnection cnEMP = new OleDbConnection(); been declared globally and two different DataReader is used to read data from excel sheet & database.
DataGridView is very nice & fast grid control in Visual Studio. I've used here only to contain data from excel. To insert the value of each cell of DataGridView I developed the code like as foreach (DataGridViewRow EMPRow in this.dgvEMP.Rows) where EMPRow.Cells[0].Value assign value.
The main task of GetExcel() function is to transfer data from each cell of excel sheet to each cell of DataGridView by using OleDbConnection. Excel file with extension *.xlx path is taken by a textbox using the OpenFileDialog control and code snippet for that control is given below: private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofdCntrList = new OpenFileDialog();
ofdCntrList.Multiselect = false;
ofdCntrList.Filter = "Excel Files(*.XLS;*.XLSX;)|*.XLS;*.XLSX;";
ofdCntrList.Title = "Select a Excel File.";
if (ofdCntrList.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
txtFilePath.Text = ofdCntrList.FileName;
}
btnLoad.Enabled = true;
}
The format specification of excel file is that excel sheet name must be sheet1 and the first row is header text. The code of GetExcel() function is as: private void GetExcel()
{
if (txtFilePath.Text != "")
{
DataTable dtEMP = new DataTable();
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFilePath.Text + "; Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection cnEMP = new OleDbConnection(ConnectionString.ToString()))
{
cnEMP.Open();
using (OleDbCommand cmdEMP = new OleDbCommand("SELECT * FROM [Sheet1$]", cnEMP))
{
using (OleDbDataReader drEMP = cmdEMP.ExecuteReader())
{
dtEMP.Load(drEMP);
dgvEMP.DataSource = dtEMP;
}
}
cnEMP.Close();
}
}
}
In order to save data of each cell of DataGridView into Access Database, InsertData() function has been used. The code is given below: private void InsertData()
{
bool SaveStat = false;
cnEMP.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CS.NET Test Project\DataImporter\dbEMP.accdb";
foreach (DataGridViewRow EMPRow in this.dgvEMP.Rows)
{
if (CheckDuplicate(EMPRow.Cells[0].Value.ToString()) == false)
{
cnEMP.Open();
OleDbCommand cmdEMP = new OleDbCommand();
cmdEMP.Connection = cnEMP;
string qryEMP = "INSERT INTO EmpInfo([EmpName], [Designation], [Department], [Mobile]) ";
qryEMP=qryEMP+"Values('" + EMPRow.Cells[0].Value + "','" + EMPRow.Cells[1].Value + "','"+EMPRow.Cells[2].Value+"','"+EMPRow.Cells[3].Value+"')";
cmdEMP.CommandText = qryEMP;
cmdEMP.CommandType = CommandType.Text;
cmdEMP.ExecuteNonQuery();
cnEMP.Close();
SaveStat = true;
}
}
if (SaveStat == true)
MessageBox.Show("Employee Info save successfully.", "Emp Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
MessageBox.Show("Duplicate name can not be inserted.", "Emp Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Before saving each row values of GridView data can be checked whether it is duplicate or not by using the function CheckDuplicate(). This function pass a string parameter and return a Boolean value. The code is given below: private bool CheckDuplicate(string EName)
{
if (EName.Length <=25)
{
cnEMP.Open();
DataSet dsChk = new DataSet();
string qryChk = "SELECT COUNT(*) AS RC FROM EmpInfo WHERE EmpName='" + EName + "'";
var cmdChk = new OleDbCommand(qryChk, cnEMP);
var rdChk = cmdChk.ExecuteReader();
rdChk.Read();
var RowCount = (int)rdChk["RC"];
if (RowCount > 0)
{
cnEMP.Close();
return true;
}
else
{
cnEMP.Close();
return false;
}
}
else
return true;
}
To run this apps we need a database in Microsoft Access 2010 with the below specification.
Database: dbEMP
Table: EmpInfo
Fields: EmpName-text, Designation-text, Department-Text and Mobile-text.
Figure show the Database snapshot
Click on the below link to download complete project.
Thank You so much its really very very help full so well defined article ..