import csv file into database table in asp.net

Question

i want to import csv file into database table in asp.net using upload button. where table field are like 1.name 2.address 3.ph_no 4.email and my csv file structure are each and every time are different different like 1st time i am upload a csv file whose structure are like bellow 1.address 2.name 3.email 4.ph_no 2nd time i am upload a csv file whose structure are like bellow 1.email 2.name 3.ph_no how can it possible ? please help me.

saroj bhattacharya · Answer

i make a code for import csv file but there is table column and csv file header are same order like table column: and csv file header order are: 1.name 1.name 2.address 2.address 3.email 3.email but i want to import with any order like table column: 1st csv file header order are: and 2nd csv file header are: 1.name 1.address 1.email 2.address 2.name 2.address 3email 3.email 3.name how can it possible? my code is bellow please modify my code csvupload.aspx code: Well Come To WildCard Techno Services Pvt Ltd File Upload Select File ToolTip=Select Only Excel File Height=27px /> CaptionAlign=Top HorizontalAlign=Justify DataKeyNames=file_id ToolTip=Excel FIle DownLoad Tool CellPadding=4 ForeColor=#333333 GridLines=None> ' runat=server>Download HeaderText=phone_number ItemStyle-Width = 150 /> and my csvupload.aspx.cs code is bellow ?using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace csvfileupload2 { public partial class csvupload : System.Web.UI.Page { string consString = @Data Source=ANIRUDDHA-PC;Initial Catalog=sark;Integrated Security=True; protected void Page_Load(object sender EventArgs e) { BindGrid(); } protected void Upload(object sender EventArgs e) { //Upload and save the file string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string csvPath = Server.MapPath(~/Files/) + fileName; FileUpload1.SaveAs(csvPath); int file_id = 0; using (SqlConnection con = new SqlConnection(consString)) { con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = insert into files (filenamecreated_date) values(' + fileName + '' + DateTime.Now.ToString(yyyy-MM-dd HH:mm:ss) + '); int i = cmd.ExecuteNonQuery(); con.Close(); con.Open(); SqlCommand cmd1 = con.CreateCommand(); cmd1.CommandText = SELECT TOP 1 * FROM files ORDER BY file_id DESC; SqlDataReader rdr = cmd1.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); file_id = Convert.ToInt32(rdr[0]); } con.Close(); } DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[16] { new DataColumn(file_id typeof(int)) new DataColumn(phone_number) new DataColumn(title) new DataColumn(first_name) new DataColumn(middle_initial) new DataColumn(last_name) new DataColumn(address) new DataColumn(city) new DataColumn(state) new DataColumn(province) new DataColumn(postal_code) new DataColumn(country_code) new DataColumn(gender) new DataColumn(date_of_birth) new DataColumn(alt_phone) new DataColumn(email)}); string csvData = File.ReadAllText(csvPath); foreach (string row in csvData.Split('
')) { if (!string.IsNullOrEmpty(row)) { dt.Rows.Add(); dt.Rows[dt.Rows.Count - 1][0] = Convert.ToInt32(file_id); int i = 1; foreach (string cell in row.Split('')) { dt.Rows[dt.Rows.Count - 1][i] = cell; i++; } } } using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = dbo.profilesrr; sqlBulkCopy.ColumnMappings.Add(file_id file_id); sqlBulkCopy.ColumnMappings.Add(phone_number phone_number); sqlBulkCopy.ColumnMappings.Add(title title); sqlBulkCopy.ColumnMappings.Add(first_name first_name); sqlBulkCopy.ColumnMappings.Add(middle_initial middle_initial); sqlBulkCopy.ColumnMappings.Add(last_name last_name); sqlBulkCopy.ColumnMappings.Add(address address); sqlBulkCopy.ColumnMappings.Add(city city); sqlBulkCopy.ColumnMappings.Add(state state); sqlBulkCopy.ColumnMappings.Add(province province); sqlBulkCopy.ColumnMappings.Add(postal_code postal_code); sqlBulkCopy.ColumnMappings.Add(country_code country_code); sqlBulkCopy.ColumnMappings.Add(gender gender); sqlBulkCopy.ColumnMappings.Add(date_of_birth date_of_birth); sqlBulkCopy.ColumnMappings.Add(alt_phone alt_phone); sqlBulkCopy.ColumnMappings.Add(email email); con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } } public void BindGrid() { using (SqlConnection con = new SqlConnection(consString)) { con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = select * from files; SqlDataReader rdr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(rdr); GV_List.DataSource = dt; GV_List.DataBind(); con.Close(); } } protected void Button1_Click(object sender EventArgs e) { using (SqlConnection con = new SqlConnection(consString)) { con.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(select * from profilesrr con); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); da.Dispose(); cmd.Dispose(); con.Close(); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); } } } } please modify my code

Vipul Malhotra · Answer

OLEDB Connection could me made by using the below code: where: strFilePath = Path of the file strSheetName = Name of the sheet in that file System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + strFilePath + ';Extended Properties=Excel 12.0;); MyCommand = new System.Data.OleDb.OleDbDataAdapter(select * from [ + strSheetName + $] MyConnection); MyCommand.TableMappings.Add(Table TestTable); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); System.Data.DataTable dt = DtSet.Tables[0]; MyConnection.Close(); return dt; will share the EP Plus code in a few hours

saroj bhattacharya · Answer

please sir send me the code using asp.net it really argent for me

Vipul Malhotra · Answer

Hi You can use an open source Dll called EPPlus to fulfill this purpose. They have a complete website to guide you for the same - http://epplus.codeplex.com/ Also OLEDB Connection can be used as well (Although it will require Ms Office to be installed on the server where you host your application) I will try and share the code as well.