Create Excel Connection to Upload Data to Sql

April 11, 2022 Post a Comment

Past: | Updated: 2022-01-13 | Comments | Related: More > Import and Consign

Trouble

In a series of tutorials (run across references at the bottom) I presented how you tin consign information from an SQL database to an Excel file. In this article I will nowadays how nosotros can import data from an Excel file into a destination table in a SQL database. I will use Microsoft SQL Server 2019, Visual Studio 2019 and Microsoft Excel 365. Some of the C# code will non piece of work if yous don't use .NET 4.viii or later.

Solution

Importing data (or loading data) into a SQL database is part of an ETL procedure. Sometimes this is accomplished with an SSIS package, the SQL Server Import and Export Wizard, OPENROWSET or a Linked Server. For this process Excel files are widely used, outset because their construction is like to a database (for example Excel sheets can be seen as database tables, and columns in a sheet tin be seen equally tabular array columns), and second because it is very piece of cake and user-friendly to collect data in an Excel file and load it into the database.

In this tutorial I will show how you tin import an Excel file with multiple sheets into a new database with SQL tables.

The database I utilize for this test tin be created by the following statements:

IF Non EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'dbtest')     create database dbtest get  IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'dbtest') begin     exec('CREATE Tabular array dbtest.dbo.[Company]     (         [CompanyCode] varchar(viii) NOT NULL,         [CompanyName] varchar(24) NULL,         [BIC] varchar(11) NULL,         [IBAN] varchar(35) Nada,         CONSTRAINT [PK_Company] Principal KEY Clustered          (             [CompanyCode]         )     )')      exec('CREATE Tabular array dbtest.dbo.[EmployeeDetails]     (         [EmployeeCode] varchar(eight) NOT Nil,         [Surname] varchar(25),         [FirstName] varchar(l),         [Accost] varchar(fifty),         CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY Clustered          (             [EmployeeCode]          )     )')      exec('CREATE TABLE dbtest.dbo.[Employees]     (         [EmployeeCode] varchar(8) Non Aught,         [Manager] fleck,         [GrossIncome] float,         [BIC] varchar(11),         [IBAN] varchar(34),         CONSTRAINT [PK_Employees] Master Central Clustered          (             [EmployeeCode]          )     )') terminate

Nosotros have ii connections: one to the SQL Server and another to an Excel file.

We can apply an ODBC connectedness to connect to the Excel file. For this, the connection cord looks like:

string extension = Path.GetExtension(_excelFile); string connectionString = ""; switch(extension) {   // HDR=Yes ==> we have headers and do not want them (ignore start row)   instance ".xls": //Excel 97 to 03     connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel viii.0;HDR=YES'";     interruption;   instance ".xlsx": //Excel 2007 ->     connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'";     interruption; }  connectionString = cord.Format(connectionString, _excelFile);

As you can see, we have different providers to connect to an Excel file, depending on the Excel file version. Older Excel files created with Excel up to version 2003 use the Jet engine and newer Excel files use the ACE engine. I assume that currently most Excel files are created using the newer version of Excel.

Some other thing to mention is HDR=Yeah as an extended property in the connexion string. This says the first row in the Excel file is used as header for columns, so we can give each column a header, as in the post-obit picture:

sample excel data

Information technology is non necessary that the column header names are the aforementioned every bit the table columns in the database simply is it useful.

Reading Excel sheets is the side by side step, which tin can exist done with the following code:

DataTable dt = null; using OleDbConnection conn = new OleDbConnection(connectionString); conn.Open(); dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, goose egg); Listing<string> excelSheets = new List<cord>();  // Add the sheet name to the cord assortment. foreach(DataRow row in dt.Rows) {     string sheet = row["TABLE_NAME"].ToString();     if(sheet.StartsWith("'"))         sail = sail.Substring(one);     // Get rid of $ in the sheet name     if(sheet.EndsWith("'"))         sheet = sheet.Substring(0, sail.Length - 2);     else         canvas = canvas.Substring(0, sail.Length - 1);     excelSheets.Add together(sheet.ToLower()); }

An Excel sheet proper noun ends in $. If we utilise a space in the sheet proper noun, it volition be surrounded by quotes. For example, if we take a sheet called Employee Details (with a space in the proper noun), the name nosotros read using OleDB is 'Employee Details$', and if the canvass is called EmployeeDetails (without a space in the name) the name we read is EmployeeDetails$. Nosotros need simply the name of the sheet, then we need to go rid of the extra characters in the name we read from Excel. The purpose of the code in a higher place is to brand sure we read the right Excel sheet.

We tin, for example, give the Excel sheet the name of the corresponding table in the database, and we can check if that table exists before reading information from the sail.

In that location are basically 2 means of reading data from the Excel file. The offset mode is to read all sheets into memory and and then write to the corresponding tables. A second way is to read one row from a sheet and write it immediately to the corresponding table without keeping the data in memory. This is very useful if the Excel file is large. We will use the first approach in this article.

The Excel file we will use has three sheets, one of them being called Visitor. This sheet can be read using the post-obit code:

private void ReadCompany(OleDbConnection conn) {     string query = "SELECT * FROM [Visitor$]'";     OleDbCommand cmdCompany = new OleDbCommand(query, conn);     using OleDbDataReader dr = cmdCompany.ExecuteReader();      // Get only the first visitor found     if(mdRead())     {         List<string> cols = new List<string>();         for(int i = 0; i < dr.FieldCount; ++i)         {             cols.Add(dr[i].ToString().Supervene upon("'", "''"));         }         _company = cols.ToArray();     } }

All of the Code and Sample Excel Data

Copy data to an existing table in the database is piece of cake and can be establish in the attached C# project. The article contains the total C# project, the Excel file used as an instance, and the SQL script used to create the database (this database script is at the outset of this article).

Download the code for this article.

Next Steps

Import Excel data into a SQL database using C#, where the Excel file defines the database tables structure.
References
- Export SQL Server Table to Excel with C#
- Export SQL Server Data to Multiple Excel Worksheets with C#
- How to Make a Chart in Excel from a SQL Server Database
- Export SQL Server Data to Excel and Add together New Columns with C#
- Export Data from SQL Server to Excel and Simulate COMPUTE BY Clause