How to map Excel to SQL Server (dynamic SQL)?

Posted on 2008-11-10
Last Modified: 2012-05-05
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();
                Grid2.DataSource = PivotTable(dst);

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?
Question by:bemara57
    1 Comment
    LVL 5

    Accepted Solution


    I suggest to you create a temp Table to cache the Excel data, then your application load data from SQL Server table (not from Excel directly).

    To load Excel to your SQL Server table you have many ways ( DTS, bcp, ...) I suggest to you other good way (see code), this is valid if your SQL Server have filesystem access to Excel file.

    Good luck!

    NOTE: you method (insert data using C#) is not good. Not in transaction!

    -- Query to create temp Table
    CREATE TABLE myTempExcel (
    -- Query to update data from Excel to myTempExcel
    	TRUNCATE TABLE myTempExcel
    	INSERT INTO myTempExcel (
    	SELECT	...cols...
    			'Excel 8.0; Database=C:\excelData\data.xls',
    -- To read data from table into your app
      It's trivial!!

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now