1. IronXL
2. IronXL Blog
3. Using IronXL
4. How to Read Excel Data and Insert to Database Table in C#

Published May 2, 2023

How to Read Excel Data and Insert to Database Table in C#

Working with data from Excel spreadsheets is a common task in many applications, and transferring that data into a database table can streamline data management processes. One way to achieve this task in C# is by using libraries that allow reading and manipulating Excel files. In this process, developers can utilize the features and functionality of such libraries to extract data from an Excel sheet and insert it into a database table. One such library, IronXL, provides these capabilities and is known for its robust features and ease of use, making it a popular choice among developers. By utilizing this library, developers can easily extract data from an Excel sheet and insert it into a database table, simplifying the data management process and reducing the chances of errors.

In this article, we will explore how to use IronXL, a C# Excel Library, to read data from an Excel file and insert it into a database table.

1. IronXL

IronXL is a powerful software library designed for .NET developers, providing them with an easy-to-use API to read, write, and manipulate Excel files in their .NET applications. It offers a comprehensive set of features for creating, editing, and exporting Excel spreadsheets, all without requiring Microsoft Office or Excel to be installed on the target machine. With support for a wide range of file formats, including XLS, XLSX, CSV, TSV, and more, this library makes it easy for developers to create Excel-based applications that can be deployed anywhere. IronXL also offers advanced features like chart creation, data visualization, and data analysis to streamline the development process for small to large-scale projects.

In this article, we will discuss how to add data from an Excel file to a SQL Server table in a SQL Server database using IronXL.

2. Prerequisites

Before you can use the IronXL library to write to a database from an Excel file, you must fulfill certain prerequisites. These include:

• Having Visual Studio installed on your computer to create a C# project.
• Ensuring that ASP.NET is also installed on your system before creating a C# project.
• Installing the IronXL library on your system to export data. You can obtain it by downloading the IronXL NuGet package from the NuGet package manager in Visual Studio.
• Having SQL installed in Visual Studio.

3. Creating a New Project in Visual Studio

Before you can utilize the IronXL library to perform Excel-related operations, you need to create a .NET project in Visual Studio. While any version of Visual Studio is compatible, it is recommended to use the latest version available. You can choose from various project templates such as Windows Forms and ASP.NET, based on your project requirements. For the purposes of this tutorial, we will use the Console Application project template to demonstrate how to work with IronXL.

Once you have chosen the project type, you need to name the project and select its location. You can also specify the preferred framework, such as .NET Core 6, for the project.

After the new project is created, you can access the program.cs file where you can write code and execute the application.

Now the Visual Studio project is created, let's install IronXL.

4. Install IronXL

The IronXL library can be downloaded and installed in different ways, but for this article, we will cover two methods:

• Using NuGet packages in Visual Studio.
• Using the Visual Studio Command-Line.

4.1 Using Visual Studio

To install the IronXL library, we can use the NuGet Package Manager in Visual Studio. Simply open the NuGet Package Manager and search for IronXL in the Browse tab. Once you have located IronXL in the search results, select it and proceed with the installation. Once the installation is complete, you can start using the IronXL library in your project.

The following screenshot shows how we can open the NuGet Package Manager in Visual Studio.

The following screenshot shows IronXL in the search results:

4.2 Using the Visual Studio Command-Line

Many developers prefer to install packages using a command line interface. To install IronXL using the command line, follow these steps:

• In Visual Studio, go to Tools > NuGet Package Manager > Package Manager console.

• Enter the following line in the package manager console tab:

  
  :ProductInstall

The package will be downloaded and installed into the current project.

5. Installing and Configuring the SQL Server Database

To install and integrate the SQL server database table with your C# project, first, go to the NuGet package manager, search for System.Data.SqlClient, and install it.

Once installed, go to the project menu and click on "Add New Item".

A new window will appear. Select data from the side menu and then click on Service-Based Database from the list. Write an appropriate name for the database and click the Add button.

Then, in the Solution Explorer, right-click on the newly created database and select "Open". This will open a new sidebar.

In the new sidebar, click on your database and go to its properties. From there, copy the connection string.

After copying the connection preferences, click on your database instance to open a new list. Right-click on the table folder and select "Add New Table."

To create a new table in the database, follow these steps:

1. Open a new data table design page.
2. Add the following SQL Query, which will create a new table with three columns: Id, Name, and Number.
3. Click on the "Update" button at the top of the page.
4. The newly generated table will be added to the database.

CREATE TABLE [dbo].[Table]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [Name] varchar(100) NOT NULL,
  [number] INT
)

CREATE TABLE [dbo].[Table]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [Name] varchar(100) NOT NULL,
  [number] INT
)

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'CREATE TABLE [dbo].[Table] ([Id] INT @NOT NULL PRIMARY KEY, [Name] varchar(100) @NOT NULL, [number] INT)

Now that our SQL environment is set up, let's create some sample data to fill our database from Excel data.

6. Import Excel File Data and Export to a Database using IronXL

With IronXL, developers can automate the process of transferring data between Excel files and databases, which can save a significant amount of time and effort. By utilizing IronXL, developers can streamline their workflow and eliminate the need for manual data entry, ensuring that the data is accurate and up-to-date.

Once the SQL server is set up and the connection string is copied, just paste the connection string into the code below, link your Excel file with the code, and change the SQL query if needed. Then simply run the code and the data will be exported to the database table.

    using IronXL;
    using System.Data;
    using System.Data.SqlClient;

    WorkBook workBook = WorkBook.Load("book.xlsx");

    DataSet dataSet = workBook.ToDataSet();

    string sql = "SELECT * FROM [dbo].[Table]";

    string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
        adapter.Update(dataSet);
    }

    using IronXL;
    using System.Data;
    using System.Data.SqlClient;

    WorkBook workBook = WorkBook.Load("book.xlsx");

    DataSet dataSet = workBook.ToDataSet();

    string sql = "SELECT * FROM [dbo].[Table]";

    string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
        adapter.Update(dataSet);
    }

Imports IronXL
	Imports System.Data
	Imports System.Data.SqlClient

	Private workBook As WorkBook = WorkBook.Load("book.xlsx")

	Private dataSet As DataSet = workBook.ToDataSet()

	Private sql As String = "SELECT * FROM [dbo].[Table]"

	Private connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"

	Using connection As New SqlConnection(connectionString)
		connection.Open()
		Dim adapter As New SqlDataAdapter(sql, connection)
		adapter.Update(dataSet)
	End Using

To check if the data has been successfully exported to the database, right-click on the table directory again and click on "New Query." A new page will open, select your database from the top bar and run the appropriate SQL query to retrieve the data.

  SELECT * FROM [dbo].[Table]

Press the green button, and the result will be displayed in a second.

This is how you can write data imported from a Microsoft Excel file to a database.

7. Conclusion

Working with Excel spreadsheets is a common task in many applications, and inserting data from an Excel sheet into a database table can streamline data management processes. One way to achieve this task in C# is by using libraries that allow reading and manipulating Excel files, such as IronXL. By utilizing this library, developers can easily extract data from an Excel sheet and insert it into a database table, simplifying the data management process and reducing the chances of errors. This article covers the steps involved in adding data from an Excel file to a SQL Server table in a SQL Server database using the IronXL library. It also provides a brief introduction to the IronXL library, discusses the prerequisites needed to insert data, and describes how to create a new project in Visual Studio, install IronXL, and configure a SQL Server database. For more tutorials like this, please visit the following link. For more information on how to read data from an Excel file, please visit the following link.

Users can also benefit from Iron Suite, a collection of software development tools that includes IronPDF, IronOCR, IronXL, IronBarcode, and IronWebscraper.