How can i update database Table values using Excel?

Question

0.00/5 (No votes)

See more:

Hi to all,
i have a table in sql server management...with name Product..
that Product Table Contains pid,pname,description,rate attributes..
i have 100 records in Product Table..i didn't give description part while inserting attribute values...
so, now i want to insert that description attribute value..
i have a Excel with pid and description for 100 products..
now i need a script code to update description in Product Table using Excel, if Product Table PID is equals to Excel PID.....

Posted 20-Nov-13 1:37am

Kumar Kovuru

Add a Solution

Comments

pradiprenushe 20-Nov-13 7:56am

Store ID & description in xls.
Read this article how to import data from xls.
http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server
This article gives code for insertion you just have to change query to update instead of update.

Kumar Kovuru 21-Nov-13 0:35am

i saw that article but it is showing error in con.open();
"Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes"
i was given like above, then where can i give my database user name and passord...
please help me regarding this...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2013-11-20T02:12:00

Solution 1

Search in Google "Import data from Excel to Database in C#".

You just need to read the Excel file, run query for each row of Excel.
In that query, check if that ID exists in Table or not, if found, then update the Description field.

Posted 20-Nov-13 2:12am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Kumar Kovuru 20-Nov-13 8:58am

i did search, but i was confuse..

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Nov-13 9:25am

What is the confusion? Please elaborate.

Kumar Kovuru 21-Nov-13 0:37am

i got this article in codeproject...
http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server
but am getting error in con.open();
then where can i give my database user name and passord...
please help me regarding this...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 21-Nov-13 0:52am

Okay, let me check.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 21-Nov-13 0:55am

Check function "insertdataintosql" inside file "Excel2sql.aspx.cs".
The below line makes the connection...

SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");

It connects to a mdf file. As per your requirements, replace the Connection String.

Kumar Kovuru 21-Nov-13 1:08am

it is exactly working in my local system.........
but i want to do this modifications in server database..HOW?
can i give server database user name and password...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 21-Nov-13 1:21am

Yes, for server, you need to replace . with the Server IP.

Replace

Data Source=.\\sqlexpress

with something like...

Data Source=IPAddressOfServer\\sqlexpress

Kumar Kovuru 21-Nov-13 3:41am

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void insertdata_Click(object sender, EventArgs e)
{
OleDbConnection oconn = new OleDbConnection
(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + ";Extended Properties=Excel 8.0");//OledbConnection and
// connectionstring to connect to the Excel Sheet
try
{
//After connecting to the Excel sheet here we are selecting the data
//using select statement from the Excel sheet
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open(); //Here [Sheet1$] is the name of the sheet
//in the Excel file where the data is present
OleDbDataReader odr = ocmd.ExecuteReader();
string Name = "";
string ShortDescription = "";
string FullDescription = "";
string AdminComment = "";
string ProductTemplateId = "";
string ShowOnHomePage = "";
string MetaKeywords = "";
string MetaDescription = "";
string MetaTitle = "";
string AllowCustomerReviews = "";
string ApprovedRatingSum = "";
string NotApprovedRatingSum = "";
string ApprovedTotalReviews = "";
string NotApprovedTotalReviews = "";
string SubjectToAcl = "";
string Published = "";
string Deleted = "";
string CreatedOnUtc = "";
string UpdatedOnUtc = "";
string ProductOffer = "";
string CityId = "";
string AreaIds = "";
string ShowOnHotOffersPage = "";
while (odr.Read())
{
Name = valid(odr, 0);//Here we are calling the valid method
ShortDescription = valid(odr, 1);
FullDescription = valid(odr, 2);
AdminComment = valid(odr, 3);
ProductTemplateId = valid(odr, 4);
ShowOnHomePage = valid(odr, 5);
MetaKeywords = valid(odr, 6);//Here we are calling the valid method
MetaDescription = valid(odr, 7);
MetaTitle = valid(odr, 8);
AllowCustomerReviews = valid(odr, 9);
ApprovedRatingSum = valid(odr, 10);
NotApprovedRatingSum = valid(odr, 11);
ApprovedTotalReviews = valid(odr, 12);//Here we are calling the valid method
NotApprovedTotalReviews = valid(odr, 13);
SubjectToAcl = valid(odr, 14);
Published = valid(odr, 15);
Deleted = valid(odr, 16);
CreatedOnUtc = valid(odr, 17);
UpdatedOnUtc = valid(odr, 18);//Here we are calling the valid method
ProductOffer = valid(odr, 19);
CityId = valid(odr, 20);
AreaIds = valid(odr, 21);
ShowOnHotOffersPage = valid(odr, 22);
//Here using this method we are inserting the data into the database
insertdataintosql(Name, ShortDescription, FullDescription, AdminComment, ProductTemplateId, ShowOnHomePage, MetaKeywords, MetaDescription, MetaTitle, AllowCustomerReviews, ApprovedRatingSum, NotApprovedRatingSum, ApprovedTotalReviews, NotApprovedTotalReviews, SubjectToAcl, Published, Deleted, CreatedOnUtc, UpdatedOnUtc, ProductOffer, CityId, AreaIds, ShowOnHotOffersPage);
}
oconn.Close();
}
catch (DataException ee)
{

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 21-Nov-13 5:06am

The connection String which connects to Database will be different as per your server's address.
That is present inside function "insertdataintosql".

Kumar Kovuru 22-Nov-13 3:53am

Am Getting This error when i connect to server database
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 22-Nov-13 5:11am

Yeah, that means the Connection String is wrong.

Kumar Kovuru 22-Nov-13 5:19am

then how should i do?

TnTinMn · Answer 2 · 2013-11-20T17:14:00

Since this appears to be a one time update, I would suggest that you just export your Excel worksheet to either a text file or xml file. To create an XML File, follow the instructions here: http://office.microsoft.com/en-us/excel-help/create-an-xml-data-file-and-xml-schema-file-from-worksheet-data-HA010263509.aspx#BM1[^]

You could read an XML file in using something like this:

VB

Dim doc As New Xml.XmlDocument
' set the path to the XML data file
Dim docpath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Data.xml")
doc.Load(docpath)

For Each row As Xml.XmlNode In doc.GetElementsByTagName("Row") ' Row is the the tag created by the Excel XML export utility
      Dim pid As Xml.XmlElement = row("pid")
      Dim pid_value As Int32 = Int32.Parse(pid.InnerText) ' assumed that this is an integer
      Dim desc As Xml.XmlElement = row("description")
      ' update the database for this pid value with desc.innertext
Next