Solved

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

Posted on 2014-12-28
2
48 Views
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

Company_Name|Address|Phone_Number|2010|2011|2012|2013|2014
My first idea was to use a distributed query like below, but this is not allowed in current environment that I’m in.
 SELECT * into NEW_TABLE FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  '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.
0
Comment
Question by:kbennett13
2 Comments
 
LVL 3

Expert Comment

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

Accepted Solution

by:
Utkarsh Kulkarni earned 500 total points
ID: 40537456
More simple code you can get at
http://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-import-Excel-SpreadSheet-data-into-SQL-Server-in-ASPNet-using-C-and-VBNet.aspx

Or Code snippet like
try
        {
            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);

                connection.Open();
                using (OleDbDataReader dr = command.ExecuteReader())

                using (SqlBulkCopy bulkcopy =
                    new SqlBulkCopy(SQLconnection))
                {
                    bulkcopy.DestinationTableName = "dbo.newTable";
                    bulkcopy.WriteToServer(dr);
                    Console.WriteLine("The data has been exported from Excel to SQL.");
                    Console.ReadLine();
                }
                connection.Close();
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.ReadLine();
        }

Open in new window

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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