how to add new column to existing table with value from excel?

Posted by Tejamanimala under C# on 11/12/2013 | Points: 10 | Views : 1497 | Status : [Member] | Replies : 4
hi i have a application which is used to import the excel sheet into database,in this case i need to maintain same columns iin table which excel sheet have,i maintaine it,and i have uploaded the excel sheet,using below code,in this code i need to select the table to import te excel sheet........my doubt is some other times i need to insert one or extra columns with data to same table from excel sheet.....how it is possible,can any one please teel me ....



protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDatabaseTables();
}
}

private void PopulateDatabaseTables()
{
string tableName = string.Empty;
string sql = "SELECT *, name AS table_name " +
" FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (DataTable table = new DataTable())
{
conn.Open();
using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
{
dAd.Fill(table);
}
ListBox1.DataSource = table;
ListBox1.DataBind();
}
}
}

protected void ImportNow_Click(object sender, EventArgs e)
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table in which you want to import data from excel sheet";
}
else if ((fileuploadExcel.FileName != ""))
{
string extension = Path.GetExtension(fileuploadExcel.FileName); // fileuploadExcel.PostedFile.FileName

string excelConnectionString;
SqlConnection conn = new SqlConnection(connStr);
string tableName = ListBox1.SelectedValue;
string path = Server.MapPath("~/fileuploadExcel/" + fileuploadExcel.FileName);
fileuploadExcel.SaveAs(path);
//Create connection string to Excel work book
if (extension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Excel 8.0;HDR=Yes;IMEX=1";
}
else
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;sData Source=" + path + ";Excel 12.0;HDR=Yes;IMEX=1";
}
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
conn.Open();
SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
SqlCommand identityChange = conn.CreateCommand();
identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr);
//Give your Destination table name
sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
conn.Close();
lblMessage.ForeColor = Color.Green;
lblMessage.Text = "Import into table <b>" + tableName + "</b> successful!<br />";
}
else
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please first upload (Select) excel file.";
}
}

protected void viewdata_Click(object sender, EventArgs e)
{
BindData();
}

private void BindData()
{
try
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table for which you want to view data in Gridview";
}
else
{
string tableName = ListBox1.SelectedValue;
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter("select * from " + tableName, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
gvdetails.DataSource = ds;
gvdetails.DataBind();
}
}
catch (DataException de)
{
lblMessage.Text = de.Message;
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}

protected void gvdetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvdetails.PageIndex = e.NewPageIndex;
BindData()

manimala


Responses

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Before inserting data to existing table you are deleting all records by using TRUNCATE...
So you can check the column count of both excel file and table and if NOT matched then import excel data to new table...

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

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

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link for exporting excel data to Sql table
http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx

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

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

Posted by: Tejamanimala on: 11/12/2013 [Member] Starter | Points: 25

Up
0
Down
thanks,but directly i want to add extra column to existing table from excel sheet,how it is possible?

manimala

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

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link once..
http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

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

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

Login to post response