Click here to Skip to main content
13,895,605 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


21 bookmarked
Posted 16 Dec 2011
Licenced CPOL

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);

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");
   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);
   for (int i = 0; i < dtExcel.Rows.Count; i++)
         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)
   if (count == dtExcel.Rows.Count)
      <--Success Message-->
      <--Failure Message-->
catch (Exception ex)
   throw ex;


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

You may also be interested in...


Comments and Discussions

QuestionError in OleDbConnection Pin
cdraghu15-Nov-13 3:25
membercdraghu15-Nov-13 3:25 
GeneralMy vote of 1 Pin
preetimodanwal11-Oct-12 19:17
memberpreetimodanwal11-Oct-12 19:17 
showing invalid column name at the time of insertion
GeneralRe: My vote of 1 Pin
Suraj S Koneri12-Oct-12 1:45
memberSuraj S Koneri12-Oct-12 1:45 
QuestionThe type or namespace name 'dataclassesdatacontext' could not be found Pin
Gunasekara Perumal6-Sep-12 8:31
memberGunasekara Perumal6-Sep-12 8:31 
AnswerRe: The type or namespace name 'dataclassesdatacontext' could not be found Pin
Suraj S Koneri6-Sep-12 19:15
memberSuraj S Koneri6-Sep-12 19:15 
Questionhi Suraj Pin
Kolli Hari Chowdary13-Jun-12 8:57
groupKolli Hari Chowdary13-Jun-12 8:57 
QuestionHow to rectify this error Pin
shwetha124-Apr-12 0:33
membershwetha124-Apr-12 0:33 
QuestionQuestions Pin
xbolslock4-Apr-12 16:59
memberxbolslock4-Apr-12 16:59 
GeneralThis part ["insert into table name values"] seems to assume ... Pin
Member 40387421-Dec-11 2:11
memberMember 40387421-Dec-11 2:11 
GeneralRe: the table already will be created in the database at back en... Pin
Suraj S Koneri21-Dec-11 4:46
memberSuraj S Koneri21-Dec-11 4:46 
GeneralReason for my vote of 2 is the code incomplete? It doesn't s... Pin
Member 40387420-Dec-11 23:54
memberMember 40387420-Dec-11 23:54 
GeneralReason for my vote of 5 5ed Pin
Pramod Harithsa18-Dec-11 23:18
professionalPramod Harithsa18-Dec-11 23:18 
Generali have an access file which is password protected.can i do a... Pin
fairoozkk18-Dec-11 7:39
memberfairoozkk18-Dec-11 7:39 
GeneralRe: For the Access database above method is good but instead of ... Pin
Suraj S Koneri18-Dec-11 19:47
memberSuraj S Koneri18-Dec-11 19:47 

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

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

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190306.1 | Last Updated 16 Dec 2011
Article Copyright 2011 by Suraj S Koneri
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid