how to insert exceldata to sqltable

Posted by Parthibansk under ASP.NET on 9/18/2013 | Points: 10 | Views : 937 | Status : [Member] | Replies : 4
i have a excelsheet which has to be uploaded to database using fileupload control in asp.net..how to code for the scenario




Responses

Posted by: Bandi on: 9/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link to upload exceldata into sql table by using file upload
http://forums.asp.net/t/1784044.aspx?How+to+Upload+Excel+File+to+Data+Set+in+C+Asp+net
http://gallery.technet.microsoft.com/Steps-How-to-import-MS-a34604b4

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Steps to be followed:
Step 1: Let’s take an example to import data to SQL Server table. I am going to import student information data from an MS Excel sheet to the tStudent SQL table:

Step 2: Now design a tStudent table in SQL Server
CREATE TABLE tdatamigrationtable

(
STUDENT VARCHAR(64),
ROLLNO VARCHAR(16),
COURSE VARCHAR(32),
)


Your MS Excel sheet and SQL table are ready, now it’s time to write C# code to import the Excel sheet into the tStudent table.

Step 3: Add these two namespaces in your class file:
USING SYSTEM.DATA.OLEDB;
USING SYSTEM.DATA.SQLCLIENT;

Step 4: Add below method in your class file, you can call this method from any other class and pass the Excel file path:

public void importdatafromexcel(string excelfilepath)

{
//declare variables - edit these based on your particular situation
string ssqltable = "tdatamigrationtable";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have
different
string myexceldataquery = "select student,rollno,course from [sheet1$]";
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=mydatabaseservername;user
id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
//execute a query to erase any previous data from our destination table
string sclearsql = "delete from " + ssqltable;
sqlconnection sqlconn = new sqlconnection(ssqlconnectionstring);
sqlcommand sqlcmd = new sqlcommand(sclearsql, sqlconn);
sqlconn.open();
sqlcmd.executenonquery();
sqlconn.close();
//series of commands to bulk copy data from the excel file into our sql table
oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
oledbconn.open();
oledbdatareader dr = oledbcmd.executereader();
sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
bulkcopy.destinationtablename = ssqltable;
while (dr.read())
{
bulkcopy.writetoserver(dr);
}

oledbconn.close();
}
catch (exception ex)
{
//handle exception
}
}


In the above function you have to pass the MS Excel file path as a parameter. If you want to import your data by providing the client access to select the Excel file and import, then you might have to use the ASP.NET File control and upload the Excel file on the server in some temp folder, then use the file path of the uploaded Excel file and pass the path in the above function. Once data import is complete then you can delete the temporary file.

The above method first deletes the existing data from the destination table, then imports the Excel data into the same table.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/18/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use the below link:-

http://www.codeproject.com/Tips/300639/Import-Data-from-excel-to-SQL-server-using-Csharp

Happy Coding

If it helps you or directs U towards the solution, MARK IT AS ANSWER

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark as Answer"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response