Import Data from excel to SQL server using C# in ASP. NET

The Page having a FileUpload control and the Upload button, on selecting the Excel file user needs to click on Upload button to store the data to Server. Here we are treating the uploaded file as database hence we need to create OLEDB connection to this file, from this connection will be created and the data is fetched to C# as DataTable. '[Sheet1$]' is the Name of the Worksheet where requires data is present.

            
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" +  + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection con = new OleDbConnection(SourceConstr);
string query = "Select * from [Sheet1$]";
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
data.Fill(dtExcel);

From this code connection will be created for the Excel file and the data in Sheet1 will be dumped to the dtExcel of type DataTable.
Note:'$' must be there after the worksheet name.

Whole code for the same is here.

fileName = FileUpload1.ResolveClientUrl(FileUpload1.PostedFile.FileName);
int count = 0;
DataClassesDataContext conLinq = new DataClassesDataContext("Data Source=server name;Initial Catalog=Database Name;Integrated Security=true");
try
{
   DataTable dtExcel = new DataTable();
   string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
   OleDbConnection con = new OleDbConnection(SourceConstr);
   string query = "Select * from [Sheet1$]";
   OleDbDataAdapter data = new OleDbDataAdapter(query, con);
   data.Fill(dtExcel);
   for (int i = 0; i < dtExcel.Rows.Count; i++)
   {
      try
      {
         count += conLinq.ExecuteCommand("insert into table name values(" + dtExcel.Rows[i][0] + "," + dtExcel.Rows[i][1] + ",'" + dtExcel.Rows[i][2] + "',"+dtExcel.Rows[i][3]+")");
      }
      catch (Exception ex)
      {
         continue;
      }
   }
   if (count == dtExcel.Rows.Count)
   {
      <--Success Message-->
   }
   else
   {
      <--Failure Message-->
   }
}
catch (Exception ex)
{
   throw ex;
}
finally
{
   conLinq.Dispose();
}

Hint: For improved responsiveness ensure Javascript is enabled and choose 'Normal' from the Layout dropdown and hit 'Update'.
You must Sign In to use this message board.

Search
	Per page