ASP.NET - mapping excel with db fields - Asked By SVK N on 11-Nov-11 07:07 AM

Mapping excel columns when importing to the database field in sql table

currently i am selection the column names from excel ans then checking with teh sql table

Jitendra Faye replied to SVK N on 11-Nov-11 07:12 AM

Using sqlBulk Class you can export excel file to DataBase.

Use this code-

protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path
string path = fileuploadExcel.PostedFile.FileName;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Try this code and let me know.

dipa ahuja replied to SVK N on 11-Nov-11 07:13 AM
Untitled document
private void button2_Click(object sender, EventArgs e)
{
   string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
   ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
   string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
   SqlConstr += "Integrated Security=True;User Instance=True";
 
   using (OleDbConnection con = new OleDbConnection(ExcelConstr))
   {
     con.Open();
     OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
     OleDbDataReader dr = com.ExecuteReader();
     using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
     {
       sqlcon.Open();
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
       {
         bulkCopy.DestinationTableName = "Table1";
         bulkCopy.ColumnMappings.Add("srno", "srno");
         bulkCopy.ColumnMappings.Add("amount", "amount");
         bulkCopy.WriteToServer(dr);
       }
     }
     dr.Close();
     dr.Dispose();
   }
   MessageBox.Show("successfully imported!");
   //display the imported data in the datagrid
   SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
   DataTable dt = new DataTable();
 
   da.Fill(dt);
 
   dataGridViewX1.DataSource = dt;
}
Kirtan Patel replied to SVK N on 11-Nov-11 07:21 AM
you can import Excel file content into databale and then you can checke that columns match with the Database column or not ..

string connstr ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0"/>
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT * FROM [Sheet$]";
 
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
 
SVK N replied to dipa ahuja on 11-Nov-11 07:58 AM
but i dont have definate no of columns in excel

in my db  i have a set of 70 columns
depending on the users excel sheet which is imported i need to map the no fo columns
i cant hardcode it needs to be dynamic
SVK N replied to Kirtan Patel on 12-Nov-11 12:32 AM
this code is simple & i already have it as its just for importing
wht i need is when the importing takes place the column header from excel shld be mapped with the column header from databse & then data inserted

i can use bulkcopy as there is no predefined no of columns that will be imported