How to Import an Excel File to SQL Database in VB .NET

Introduction

Two effective administration and analysis tools are SQL databases and Excel, each with special functions and capabilities.

Excel

Excel is a popular spreadsheet program created by Microsoft. Its user-friendly interface and adaptability in managing different data organization, computation, analysis, and visualization activities have earned it a reputation. Users may enter data into Excel sheet cells arranged in rows and columns, carry out mathematical operations, and produce charts, graphs, and pivot tables to summarize and analyze data or import data from Excel. Excel is a widely utilized tool among individuals, corporations, and organizations for many purposes such as financial analysis, inventory management, reporting, and budgeting.

SQL

Structured data is stored, managed, and retrieved using databases that support SQL (Structured Query Language). Their foundation is the relational model, and they are made up of tables with rows and columns. Users may build, change, and query databases using SQL commands and SQL databases such as MySQL, PostgreSQL, SQL Server, and SQLite. These databases offer strong data retrieval and manipulation capabilities, scalability, and integrity assurance.

In this article, we are going to see how VB.NET imports Excel files into the SQL Server database table without using the provider Microsoft.ACE.OLEDB.12.0.

How to Use VB.NET to Import Excel Files to SQL Database

1. Create a new Visual Studio project.
2. Install the required library.
3. Now we can import an Excel file and convert it into a DataTable or retrieve data.
4. Connect to the SQL database.
5. Save the imported data into the database table.
6. Close the SQL connection and dispose of the object.

What is IronXL

A powerful .NET framework called IronXL was created to facilitate dealing with Excel files written in C#, VB.NET, Visual Basic, and other .NET languages. It is compatible with the XLS and XLSX file formats. This library makes it easier and faster for developers to write, read, edit, and generate Excel spreadsheets. A vast array of tools and functions are also available.

Key features and capabilities of IronXL include:

• Data handling: IronXL makes it easy to read, write, and manipulate data in Excel spreadsheets. A two-dimensional array may be used to retrieve cells, and computations, formulas, and data formatting are all possible.
• Excel file creation and altering: In addition to generating new Excel files and altering ones that already exist, developers may also add, remove, and manage worksheets. They can also work with many Excel components.
• IronXL may be utilized in a range of application scenarios and is compatible with multiple .NET platforms, including Xamarin, .NET Core, and .NET Framework, thanks to its cross-platform interoperability.
• Versatility and compatibility: It supports both the older XLS and the more modern XLSX Excel formats and is compatible with several Excel versions.
• Support for legacy and modern Excel formats: It can support both more contemporary XML-based formats (XLSX, which dates back to Excel 2007) and more traditional Excel file formats (XLS, which dates back to Excel 97–2003).
• Usefulness: By offering a straightforward API with easily understood properties and functions, the library makes Excel-related activities more accessible to developers with varying degrees of experience.
• Data extraction and export: IronXL facilitates the extraction of data from Excel files and the export of Excel data to several formats, making it simple to interface with databases and other systems.
• Documentation and support: IronXL provides a wealth of documentation, tutorials, and support to assist developers in using its library for Excel-related tasks.
• Automation and efficiency: By automating Excel tasks, IronXL enables users to be more productive, spend less time on manual labor, and develop data-driven, efficient applications.
• Integration & customization: It makes it easier to create personalized reports or data-driven solutions by providing choices for exporting Excel data into a variety of formats. It works well with databases and other systems as well.

Financing, data analysis, reporting, business intelligence, and software development are just a few of the numerous fields that employ IronXL. It enables developers to work with Excel files programmatically and produce dependable solutions that combine Excel integration with data manipulation. Click here to learn more.

Create a New Visual Studio Project

It's easy to create a Visual Studio console project. To create a Console Application in Visual Studio, follow these steps:

1. Launch Visual Studio: Open Visual Studio (make sure your PC has Visual Studio installed).

Start a New Project

Select File, then New, then Project.

Select your preferred programming language, such as C#, from the left panel of the "Create a new project" box.

Next, from the list of available project templates, choose the "Console App" or "Console App (.NET Core)" template.

Give your project a name by filling out the "Name" section.

Select the place where the project should be saved.

To start a new Console application project, click "Create".

Installing IronXL Library

To install the IronXL library, follow these steps:

1. Open the NuGet Package Manager Console by going to Tools > NuGet Package Manager > Package Manager Console.
2. Use the following command to install the IronXL library:

Install-Package IronXL.Excel

Alternatively, you can also install the IronXL library using the NuGet Package Manager. Just search for the package "IronXL" and choose which of the NuGet packages associated with IronXL you want to download from the list.

IronXL to Import and Add into SQL Database

These procedures can be used to import data from an Excel file using IronXL and then add it to a SQL database using VB.NET. This example demonstrates how to use IronXL to read an Excel file and insert the data into a SQL Server database.

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

Module Program
    Sub Main(args As String())
        Dim excelFilePath As String = "Demo.xlsx"
        Dim connectionString As String = "Data Source=DESKTOP-QBIBUNV;Initial Catalog=Mohammed;Integrated Security=True;Encrypt=False"

        ' Load Excel file using IronXL
        Dim workbook As WorkBook = WorkBook.Load(excelFilePath)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

        ' Set up SQL connection
        Using connection As New SqlConnection(connectionString)
            connection.Open()

            ' Iterate through rows and insert data into SQL database
            For Each row In worksheet.ToDataTable().AsEnumerable().ToList()
                ' Extract cell data
                Dim cellData As List(Of String) = New List(Of String)()

                For Each cell In row.ItemArray
                    cellData.Add(cell)
                Next

                ' Insert data into SQL database
                InsertDataIntoSQL(connection, cellData)
            Next
        End Using
    End Sub

    ' Method to insert data into SQL database
    Private Sub InsertDataIntoSQL(connection As SqlConnection, data As List(Of String))
        ' Define your SQL INSERT query
        Dim sqlQuery As String = "INSERT INTO ExcelData (Name, Age) VALUES (@Value1, @Value2)"

        ' Create a SqlCommand object with parameters
        Using command As New SqlCommand(sqlQuery, connection)
            ' Set parameters (adjust as per your column names and data)
            command.Parameters.AddWithValue("@Value1", data(0))
            command.Parameters.AddWithValue("@Value2", data(1))

            ' Execute the SQL command
            command.ExecuteNonQuery()
        End Using
    End Sub
End Module

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

Module Program
    Sub Main(args As String())
        Dim excelFilePath As String = "Demo.xlsx"
        Dim connectionString As String = "Data Source=DESKTOP-QBIBUNV;Initial Catalog=Mohammed;Integrated Security=True;Encrypt=False"

        ' Load Excel file using IronXL
        Dim workbook As WorkBook = WorkBook.Load(excelFilePath)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

        ' Set up SQL connection
        Using connection As New SqlConnection(connectionString)
            connection.Open()

            ' Iterate through rows and insert data into SQL database
            For Each row In worksheet.ToDataTable().AsEnumerable().ToList()
                ' Extract cell data
                Dim cellData As List(Of String) = New List(Of String)()

                For Each cell In row.ItemArray
                    cellData.Add(cell)
                Next

                ' Insert data into SQL database
                InsertDataIntoSQL(connection, cellData)
            Next
        End Using
    End Sub

    ' Method to insert data into SQL database
    Private Sub InsertDataIntoSQL(connection As SqlConnection, data As List(Of String))
        ' Define your SQL INSERT query
        Dim sqlQuery As String = "INSERT INTO ExcelData (Name, Age) VALUES (@Value1, @Value2)"

        ' Create a SqlCommand object with parameters
        Using command As New SqlCommand(sqlQuery, connection)
            ' Set parameters (adjust as per your column names and data)
            command.Parameters.AddWithValue("@Value1", data(0))
            command.Parameters.AddWithValue("@Value2", data(1))

            ' Execute the SQL command
            command.ExecuteNonQuery()
        End Using
    End Sub
End Module

Using its API, IronXL provides a convenient method for loading Excel files (WorkBook.Load) and retrieving their contents, allowing iteration through rows and cells (ExcelRow and ExcelCell objects).

The program reads the data from the Excel file using IronXL, retrieves the cell values for each row, and stores them in appropriate data structures for later processing.

Next, the program connects to the SQL Server database using a connection string and the System.Data.SqlClient namespace. SQL INSERT queries are then prepared to insert the Excel data into the corresponding SQL table. The program uses parameterized SQL commands (SqlCommand) to insert the Excel data into the SQL database by mapping the retrieved cell values to the appropriate SQL query parameters and executing the INSERT command using command.ExecuteNonQuery().

To learn more about the code, check here.

Conclusion

The IronXL library, which is an Excel object library, is widely used by several industries, including software development, finance, data analysis, and reporting. It is an essential tool for businesses and developers seeking to maximize their operations using Excel. IronXL allows for the creation of dynamic, data-centric applications that efficiently and programmatically manage Excel files.

In summary, using IronXL in a VB.NET application enables seamless data transfer from Excel files to SQL databases, providing efficiency and flexibility in data management across both platforms. IronXL offers a free Community Edition with limitations for non-commercial use. Paid versions, starting at $749, can be purchased through subscription- or perpetual-based licensing models and provide additional features, support, and complete functionality. Please visit IronXL's official website for comprehensive and up-to-date licensing information. For more information on Iron Software products, visit this link.