Click here to Skip to main content
13,197,796 members (43,434 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


20 bookmarked
Posted 16 Dec 2011

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 
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 
Dear Mr.Suraj,

I am getting "The type or namespace name 'dataclassesdatacontext' could not be found' error.

Even i am adding reference .NET tab......Afterwards also i am facing such type of error.

Please give me your suggestions.

Thanks & Regards,

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
Hari Chowdary13-Jun-12 8:57
groupHari 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
memberPramod 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 | Terms of Use | Mobile
Web01 | 2.8.171020.1 | Last Updated 16 Dec 2011
Article Copyright 2011 by Suraj S Koneri
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid