Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Import an excel spread sheet into SQL Server database and build the SQL Server table on the fly

Posted on 2014-12-28
Medium Priority
Last Modified: 2016-02-18
I’m not a C# or VB developer can someone help me with C# or VB code that will import an excel spread sheet into SQL Server database and build the SQL Server table on the fly.

I need to develop an ETL Process that needs to load an excel spread sheet into a SQL Server Database. The amount of columns will be dynamic, but I do know that the name of the first three columns and the rest of the columns will be integer values. For example the spread sheet structure will be like so

My first idea was to use a distributed query like below, but this is not allowed in current environment that I’m in.
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

So I think my best option is script task and use C# or vb code that will do the same thing as the SQL code above.
Question by:kbennett13
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

by:Ali HND
ID: 40520618
I think this link is useful for you:
How to import data from Excel to SQL Server

Accepted Solution

Utkarsh Kulkarni earned 1500 total points
ID: 40537456
More simple code you can get at

Or Code snippet like
            string sqlConnectionString = string.Format("Data Source=.\SQLEXPRESS;Initial Catalog=DbName;" + "Integrated Security=SSPI; User Instance=False");
            string excelConnectionString = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\\user\\ExcelFileName.xlsx; Extended Properties ='Excel 12.0 xml; HDR=YES;IMEX=1'");
            using (SqlConnection SQLconnection =
                new SqlConnection(sqlConnectionString))
            using (OleDbConnection connection =
                new OleDbConnection(excelConnectionString))
                OleDbCommand command = new OleDbCommand
                ("SELECT * INTO dbo.newTable FROM [SheetName$];", connection);

                using (OleDbDataReader dr = command.ExecuteReader())

                using (SqlBulkCopy bulkcopy =
                    new SqlBulkCopy(SQLconnection))
                    bulkcopy.DestinationTableName = "dbo.newTable";
                    Console.WriteLine("The data has been exported from Excel to SQL.");

        catch (Exception ex)

Open in new window

Ref- stackoverflow.com/questions/18826414/excel-data-to-new-sql-server-table-using-c-sharp-new-table-not-being-created

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

730 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