Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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

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
kbennett13
Asked:
kbennett13
1 Solution
 
Ali HNDCommented:
I think this link is useful for you:
How to import data from Excel to SQL Server
0
 
Utkarsh KulkarniCommented:
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now