if (fileUpload.PostedFile != null)
{
HttpPostedFile myfile = fileUpload.PostedFile;
int filelen = myfile.ContentLength;
if (filelen == 0)
{
filename = "No file was uploaded";
return filename;
}
byte[] myData = new Byte[filelen];
myfile.InputStream.Read(myData, 0, filelen);
// Make sure a duplicate file doesn’t exist. If it does, keep on appending an
// incremental numeric until it is unique
string sFilename = System.IO.Path.GetFileName(myfile.FileName);
string sFileExtention = System.IO.Path.GetExtension(myfile.FileName);
int file_append = 0;
The other gridview retieve datas from a created arraylist (rejectList)
I need help with 3 scenarios, please who ever reading this, i NEED your advice and knowledge :D
1. I want to know how to retrieve the checkbox == true seleted row via (primary key, string. email) from both the gridview.
2. I want to know how to display the dateTime type element (dateOfBirth) without the 12:00A.M. in the gridview. (it is saved into the database with only the date without the time)
3. Is there another way to export excel, the current one have to go through some check before it can be opened.
Thanks!!
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;
using System.Security.Cryptography;
using System.Text;
using System.IO;
namespace EmailBlastAppProject_ASPXConfirmed
{
public partial class Importer : System.Web.UI.Page
SqlConnection conn = new SqlConnection("Data Source=IT7682P48\\MSSQLSERVER5;Initial Catalog=AljuniedTownCouncil;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into Residence(firstName,lastName,gender,age,nric,dateOfBirth,nationality,address,area,phoneHome,phoneHP,email,remark,hashID)"
thomas.leera
None
0 Points
6 Posts
HELP! Importing excel to database
Jul 22, 2010 04:25 AM|LINK
Hi, i am currently working on importing excel sheets into my database.
SqlBulkCopy seems to be working fine, when i whole import the data over.
But i can't do automated validation (i.e. checking for identical data in the database & etc, i am stuck!)
Is there anyway i can import the excel sheet data a line at a time , and perform automated validation check on them.
Please Help!
Cheers
Thomas
stanly
Star
13201 Points
2490 Posts
Re: HELP! Importing excel to database
Jul 22, 2010 04:53 AM|LINK
http://forums.asp.net/t/1417621.aspx
weblogs.asp.net/stanly
lasantha
Contributor
2361 Points
844 Posts
Re: HELP! Importing excel to database
Jul 22, 2010 04:58 AM|LINK
hi
convert exel file to csv file and use this code
public DataSet loadCVS(int noofrows)
{
DataSet ds = new DataSet();
try
{
// Creates and opens an ODBC connection
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
//Creates the select command text
if (noofrows == -1)
{
sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
}
else
{
sql_select = "select top " + noofrows + " * from [" + this.FileNevCSV.Trim() + "]";
}
//Creates the data adapter
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
//Fills dataset with the records from CSV file
obj_oledb_da.Fill(ds, "csv");
//closes the connection
conn.Close();
}
catch (Exception e) //Error
{
}
return ds;
}
public string fileUpload(FileUpload fileUpload, string savePath)
{
//flag variables
//string savePath;
string filename = null;
//application form variables
//string appsavePath;
//string appfilename = null; ;
//savePath = "~/Admin/uploadFiles/flags/";
//appsavePath = "~/Admin/uploadFiles/appforms/";
if (fileUpload.PostedFile != null)
{
HttpPostedFile myfile = fileUpload.PostedFile;
int filelen = myfile.ContentLength;
if (filelen == 0)
{
filename = "No file was uploaded";
return filename;
}
byte[] myData = new Byte[filelen];
myfile.InputStream.Read(myData, 0, filelen);
// Make sure a duplicate file doesn’t exist. If it does, keep on appending an
// incremental numeric until it is unique
string sFilename = System.IO.Path.GetFileName(myfile.FileName);
string sFileExtention = System.IO.Path.GetExtension(myfile.FileName);
int file_append = 0;
while (System.IO.File.Exists(Server.MapPath(savePath + sFilename)))
{
file_append++;
sFilename = System.IO.Path.GetFileNameWithoutExtension(myfile.FileName)
+ file_append.ToString() + sFileExtention;
}
// Save the stream to disk
try
{
System.IO.FileStream newFile
= new System.IO.FileStream(Server.MapPath(savePath + sFilename),
System.IO.FileMode.Create);
newFile.Write(myData, 0, myData.Length);
newFile.Close();
filename = savePath + sFilename;
}
catch
{
}
}
return filename;
}
protected void importbtn_Click(object sender, EventArgs e)
{
string file_name = fileUpload(bankuploader, "~/Admin/doc/csv/");
string[] s = file_name.Split('/');
this.dirCSV = Server.MapPath("~/Admin/doc/csv/");
this.fileNevCSV = s[4].ToString();
DataSet ds = loadCVS(-1);
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
temp.bankname = row[0].ToString();
temp.bankcode = row[1].ToString();
temp.branchcode = row[2].ToString();
}
}
thomas.leera
None
0 Points
6 Posts
Re: HELP! Importing excel to database
Jul 27, 2010 07:24 AM|LINK
Hi, its me again! H~E~L~P M~E!!!
This is the scenario. below is my codes.
I have 2 gridview and 2 buttons (very simple layout) one projected from database
Residence (firstName, lastName, gender, age, nric, dateOfBirth, nationality, area, address, phoneHome, phoneHP, email,remark,hashID)
The other gridview retieve datas from a created arraylist (rejectList)
I need help with 3 scenarios, please who ever reading this, i NEED your advice and knowledge :D
1. I want to know how to retrieve the checkbox == true seleted row via (primary key, string. email) from both the gridview.
2. I want to know how to display the dateTime type element (dateOfBirth) without the 12:00A.M. in the gridview. (it is saved into the database with only the date without the time)
3. Is there another way to export excel, the current one have to go through some check before it can be opened.
Thanks!!
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;
using System.Security.Cryptography;
using System.Text;
using System.IO;
namespace EmailBlastAppProject_ASPXConfirmed
{
public partial class Importer : System.Web.UI.Page
{
ArrayList rejectList = new ArrayList();
protected void Page_Load(object sender, EventArgs e)
{
//Display the Residence data in the gridview
ResGridView.DataBind();
}
public void bindRGridView()
{
RejectGridView.DataSource = rejectList;
RejectGridView.DataBind();
}
public bool compareEmail(OleDbDataReader myreader, int stval)
{
bool identical = false;
ArrayList al = RetrieveEmail();
string email = myreader[stval].ToString();
foreach (string a in al)
{
if (a == email)
{
identical = true;
}
}
return identical;
}
public Residence insertRejects(string firstName, string lastName,
string gender, string age, string nric, string dateOfBirth, string nationality, string address, string area,
string phoneHome, string phoneHP, string email, string remark)
{
Residence r = new Residence(firstName, lastName, gender, age, nric, dateOfBirth, nationality, address, area, phoneHome, phoneHP, email, remark);
return r;
}
public ArrayList RetrieveEmail()
{
ArrayList al = new ArrayList();
string queryString = "SELECT email FROM Residence;";
string connectionString = "Data Source=IT7682P48\\MSSQLSERVER5;Initial Catalog=AljuniedTownCouncil;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
al.Add(reader[0]);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
}
return al;
}
protected void btnImport_Click(object sender, EventArgs e)
{
//tbxConnection.Text = "C:\\excel\\";
//string str = tbxConnection.Text + FileUpload1.FileName;
OleDbConnection oconn = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\excel\\book4.xls;" +
"Extended Properties=Excel 8.0;");
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string firstName = "";
string lastName = "";
string gender = "";
string age = "";
string nric = "";
string dateOfBirth = "";
string nationality = "";
string address = "";
string area = "";
string phoneHome = "";
string phoneHP = "";
string email = "";
string remark = "";
string hashID = "";
while (odr.Read())
{
firstName = valid(odr, 0);
lastName = valid(odr, 1);
gender = valid(odr, 2);
age = valid(odr, 3);
nric = valid(odr, 4);
dateOfBirth=valid(odr, 5);
nationality=valid(odr, 6);
address = valid(odr, 7);
area=valid(odr, 8);
phoneHome=valid(odr, 9);
phoneHP = valid(odr, 10);
email = valid(odr, 11);
if (compareEmail(odr, 11) == true)
{
remark = "Identical Record Existed";
Residence r = insertRejects(firstName, lastName, gender, age, nric, dateOfBirth, nationality, area, address, phoneHome, phoneHP, email,remark);
rejectList.Add(r);
}
else
{
remark = "None";
hashID = CreateMD5Hash(email);
insertdataintosql(firstName, lastName, gender, age, nric, dateOfBirth, nationality, area, address, phoneHome, phoneHP, email,remark,hashID);
}
}
if (rejectList.Count != 0)
{
bindRGridView();
RejectGridView.Visible = true;
lblReject.Visible = true;
btnExportReject.Visible = true;
}
ResGridView.DataBind();
}
public void insertdataintosql(string firstName, string lastName,
string gender, string age, string nric, string dateOfBirth, string nationality, string address,string area,
string phoneHome, string phoneHP, string email, string remark, string hashID)
{//inserting data into the Sql Server
SqlConnection conn = new SqlConnection("Data Source=IT7682P48\\MSSQLSERVER5;Initial Catalog=AljuniedTownCouncil;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into Residence(firstName,lastName,gender,age,nric,dateOfBirth,nationality,address,area,phoneHome,phoneHP,email,remark,hashID)"
+"values(@firstName,@lastName,@gender,@age,@nric,@dateOfBirth,@nationality,@address,@area,@phoneHome,@phoneHP,@email,@remark,@hashID)";
cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName;
cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName;
cmd.Parameters.Add("@gender", SqlDbType.NVarChar).Value = gender;
cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = age;
cmd.Parameters.Add("@nric", SqlDbType.NVarChar).Value = nric;
cmd.Parameters.Add("@dateOfBirth", SqlDbType.Date).Value = Convert.ToDateTime(dateOfBirth).ToShortDateString();
cmd.Parameters.Add("@nationality", SqlDbType.NVarChar).Value = nationality;
cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = address;
cmd.Parameters.Add("@area", SqlDbType.NVarChar).Value = area;
cmd.Parameters.Add("@phoneHome", SqlDbType.NVarChar).Value = phoneHome;
cmd.Parameters.Add("@phoneHP", SqlDbType.NVarChar).Value = phoneHP;
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@remark", SqlDbType.NVarChar).Value = remark;
cmd.Parameters.Add("@hashID",SqlDbType.NVarChar).Value = hashID;
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
/*public void convertDt()
{
string dateOfBirth ="11/12/2010";
DateTime date= Convert.ToDateTime(dateOfBirth);
date.ToShortDateString();
}*/
public string valid(OleDbDataReader myreader, int stval)
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
public string CreateMD5Hash(string input)
{
// Use input string to calculate MD5 hash
MD5 md5 = System.Security.Cryptography.MD5.Create();
byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
byte[] hashBytes = md5.ComputeHash(inputBytes);
// Convert the byte array to hexadecimal string
StringBuilder sb = new StringBuilder();
for (int i = 0; i < hashBytes.Length; i++)
{
sb.Append(hashBytes[i].ToString("x2"));
// To force the hex string to lower-case letters instead of
// upper-case, use he following line instead:
// sb.Append(hashBytes[i].ToString("x2"));
}
return sb.ToString();
}
protected void btnExportReject_Click(object sender, EventArgs e)
{
foreach (GridViewRow row in RejectGridView.Rows)
{
CheckBox cb = (CheckBox)row.FindControl("ResSelector");
if (cb == null && cb.Checked==false)
{
//H~E~L~P H~E~R~E
}
}
// Code seleted here
Export("Resident.xls", this.RejectGridView);
}
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
Importer.PrepareControlForExport(current);
}
}
}
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
Importer.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
Importer.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
Importer.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
}
}
<div></div>
Hong-Gang Ch...
All-Star
74695 Points
6768 Posts
Re: HELP! Importing excel to database
Jul 27, 2010 11:30 AM|LINK
Check this link,
http://www.eggheadcafe.com/community/aspnet/14/10091901/validate-excel-data-before-importing-to-sql-server.aspx
If you have any feedback about my replies,please contactmsdnmg@microsoft.com.
Microsoft One Code Framework