bemara57
asked on
How to map Excel to SQL Server (dynamic SQL)?
I have a page that allows you to upload an Excel file. The server will then select the records from Excel and load them into a grid like this:
cnx = new OleDbConnection();
cnx.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" +
filename + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;" + (char)34;
string sql = "Select * from [" + tbl + "]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, getConnection());
DataSet dst = new DataSet();
da.Fill(dst);
cnx.Close();
Grid2.DataSource = PivotTable(dst);
Grid2.DataBind();
I have a screenshot of what this looks like. My intention is to let the user select the values in the drop down to map to the corresponding headers of the uploaded Excel file, so the system can import the Excel file into the database. This is where I'm stuck... Say I have a hashtable that gets populated with the table column name and the Excel headers, how can I use that to write the Excel records to the database with the right Excel columns going to the right db columns?
radgrid-col.png
cnx = new OleDbConnection();
cnx.ConnectionString = "Provider=Microsoft.Jet.OL
filename + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;" + (char)34;
string sql = "Select * from [" + tbl + "]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, getConnection());
DataSet dst = new DataSet();
da.Fill(dst);
cnx.Close();
Grid2.DataSource = PivotTable(dst);
Grid2.DataBind();
I have a screenshot of what this looks like. My intention is to let the user select the values in the drop down to map to the corresponding headers of the uploaded Excel file, so the system can import the Excel file into the database. This is where I'm stuck... Say I have a hashtable that gets populated with the table column name and the Excel headers, how can I use that to write the Excel records to the database with the right Excel columns going to the right db columns?
radgrid-col.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.