Click here to Skip to main content
Click here to Skip to main content

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

, 16 Dec 2011
Rate this:
Please Sign up or sign in to vote.
When the user uploads the Excel file to the server its data must dumped to the SQL server database
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();
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

No Biography provided

Comments and Discussions

 
Questionhi Suraj [modified] PingroupHari Chowdary13-Jun-12 8:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 16 Dec 2011
Article Copyright 2011 by Suraj S Koneri
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid