Link to home
Start Free TrialLog in
Avatar of bemara57
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.OLEDB.4.0;Data 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
ASKER CERTIFIED SOLUTION
Avatar of jose_juan
jose_juan
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial