In today's digital world, handling data efficiently is an important task. One common requirement in software development is reading data from a CSV file and storing it in a database. This tutorial covers the steps to read data from a CSV file and store it in a SQL Server database using C#, specifically utilizing the IronXL library. This guide is designed for beginners and will be explained in a simple, engaging way.

Understanding the Basics

What is a CSV File?

A CSV (Comma-Separated Values) file is a plain text file that contains data separated by commas. It's a popular format for transferring data due to its simplicity and compatibility with various applications, such as Excel.

The Role of SQL Server and Databases

SQL Server is a database management system by Microsoft. It's used to store and manage data in a structured way. In our case, we'll store the CSV data in a SQL Server table.

Introduction to IronXL

IronXL is an Excel library tailored for .NET applications, specifically designed to enable developers to read, generate, and edit Excel files without needing the Microsoft Office Interop. This library stands out for its compatibility with various .NET versions and platforms, including .NET Core, .NET Standard, and .NET Framework, as well as its support for different operating systems like Windows, Linux, and macOS. It's a powerful library for importing data, especially for handling CSV files.

How to Read and Store Data From CSV Files in C#

1. Create a C# console program in Visual Studio.
2. Install the CSV library using NuGet Package Manager.
3. Load the CSV file in the program using the library.
4. Create a connection with the database.
5. Read content from the CSV file using the library.
6. Copy that content into the database using the SqlBulkCopy method.

Here's an example code snippet:

using System;
using System.Data;
using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead
using System.Globalization;
using System.IO;
using CsvHelper;

namespace CsvReader
{
    class Program
    {
        static void Main(string[] args)
        {
            string csvPath = @"path\to\your\csv\file.csv";

            using (var reader = new StreamReader(csvPath))
            using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
            {
                // Mapping the CSV data to the corresponding model
                var records = csv.GetRecords<YourModel>();

                using (var sqlBulkCopy = new SqlBulkCopy("your_connection_string"))
                {
                    sqlBulkCopy.DestinationTableName = "YourTableName";
                    sqlBulkCopy.WriteToServer(records.AsDataReader());
                }

                Console.WriteLine("Data imported successfully!");
            }
        }
    }

    // Define your model that maps to the CSV columns
    public class YourModel
    {
        // Define properties here representing the CSV columns
    }
}

using System;
using System.Data;
using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead
using System.Globalization;
using System.IO;
using CsvHelper;

namespace CsvReader
{
    class Program
    {
        static void Main(string[] args)
        {
            string csvPath = @"path\to\your\csv\file.csv";

            using (var reader = new StreamReader(csvPath))
            using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
            {
                // Mapping the CSV data to the corresponding model
                var records = csv.GetRecords<YourModel>();

                using (var sqlBulkCopy = new SqlBulkCopy("your_connection_string"))
                {
                    sqlBulkCopy.DestinationTableName = "YourTableName";
                    sqlBulkCopy.WriteToServer(records.AsDataReader());
                }

                Console.WriteLine("Data imported successfully!");
            }
        }
    }

    // Define your model that maps to the CSV columns
    public class YourModel
    {
        // Define properties here representing the CSV columns
    }
}

Imports System
Imports System.Data
Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead
Imports System.Globalization
Imports System.IO
Imports CsvHelper

Namespace CsvReader
    Class Program
        Shared Sub Main(args As String())
            Dim csvPath As String = "path\to\your\csv\file.csv"

            Using reader As New StreamReader(csvPath)
                Using csv As New CsvReader(reader, CultureInfo.InvariantCulture)
                    ' Mapping the CSV data to the corresponding model
                    Dim records = csv.GetRecords(Of YourModel)()

                    Using sqlBulkCopy As New SqlBulkCopy("your_connection_string")
                        sqlBulkCopy.DestinationTableName = "YourTableName"
                        sqlBulkCopy.WriteToServer(records.AsDataReader())
                    End Using

                    Console.WriteLine("Data imported successfully!")
                End Using
            End Using
        End Sub
    End Class

    ' Define your model that maps to the CSV columns
    Public Class YourModel
        ' Define properties here representing the CSV columns
    End Class
End Namespace

Imports System
Imports System.Data
Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead
Imports System.Globalization
Imports System.IO
Imports CsvHelper

Namespace CsvReader
    Class Program
        Shared Sub Main(args As String())
            Dim csvPath As String = "path\to\your\csv\file.csv"

            Using reader As New StreamReader(csvPath)
                Using csv As New CsvReader(reader, CultureInfo.InvariantCulture)
                    ' Mapping the CSV data to the corresponding model
                    Dim records = csv.GetRecords(Of YourModel)()

                    Using sqlBulkCopy As New SqlBulkCopy("your_connection_string")
                        sqlBulkCopy.DestinationTableName = "YourTableName"
                        sqlBulkCopy.WriteToServer(records.AsDataReader())
                    End Using

                    Console.WriteLine("Data imported successfully!")
                End Using
            End Using
        End Sub
    End Class

    ' Define your model that maps to the CSV columns
    Public Class YourModel
        ' Define properties here representing the CSV columns
    End Class
End Namespace

Make sure to replace "path\to\your\csv\file.csv" with the actual path to your CSV file, YourModel with the model representing your CSV data, "your_connection_string" with your database connection string, and "YourTableName" with the name of your database table.

Setting Up the Environment

Prerequisites

1. Visual Studio: Ensure you have Visual Studio installed.
2. SQL Server: You should have SQL Server installed and accessible.
3. IronXL Installation: Install IronXL by running the following NuGet command:

dotnet add package IronXL.Excel

Make sure to run these commands within the project directory where you want to install IronXL.

Creating a SQL Server Table

Before importing data, create a destination table in your SQL Server database. This table will store the CSV data.

CREATE TABLE YourTableName (
    Column1 DataType,
    Column2 DataType,
    ...
);

CREATE TABLE YourTableName (
    Column1 DataType,
    Column2 DataType,
    ...
);

Replace YourTableName, Column1, Column2, and DataType with your specific details.

Step-by-Step Guide to Import CSV Data

1. First, make sure you have a CSV file containing the data you want to import.

2. Create a new C# console application project in Visual Studio.

3. Install the CsvHelper NuGet package for reading CSV files. You can do this by opening the NuGet Package Manager Console in Visual Studio and running the following command:

   Install-Package CsvHelper

   SHELL

4. Add the necessary using statements at the top of your C# code file:

   using System;
   using System.IO;
   using System.Globalization;
   using CsvHelper;
   using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

   Imports System
   Imports System.IO
   Imports System.Globalization
   Imports CsvHelper
   Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

   C#VB

5. In your code, specify the path to your CSV file. For example:

   string csvFilePath = @"C:\path\to\your\csv\file.csv";

   Dim csvFilePath As String = "C:\path\to\your\csv\file.csv"

   C#VB

   Make sure to replace C:\path\to\your\csv\file.csv with the actual path to your CSV file.

6. Create a new instance of the StreamReader class to read the CSV file:

   using (var reader = new StreamReader(csvFilePath))
   {
       // code goes here
   }

   Using reader = New StreamReader(csvFilePath)
   	' code goes here
   End Using

   C#VB

7. Create a new instance of the CsvReader class, passing in the StreamReader object:

   using (var reader = new StreamReader(csvFilePath))
   using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
   {
       // code goes here
   }

   Using reader = New StreamReader(csvFilePath)
   Using csv = New CsvReader(reader, CultureInfo.InvariantCulture)
   	' code goes here
   End Using
   End Using

   C#VB

8. Optionally, configure the CsvReader with any necessary settings by passing a CsvConfiguration object to its constructor. For example:

   var config = new CsvConfiguration(CultureInfo.InvariantCulture)
   {
       HasHeaderRecord = true,
   };
   using var csv = new CsvReader(reader, config);

   Imports System.Globalization
   Imports CsvHelper
   
   Dim config As New CsvConfiguration(CultureInfo.InvariantCulture) With {
       .HasHeaderRecord = True
   }
   
   Using csv As New CsvReader(reader, config)
       ' Use csv here
   End Using

   C#VB

9. Use the GetRecords<T>() method of the CsvReader object to read the CSV data into a collection of objects. Replace <T> with the type of object that represents each record in the CSV file. For example:

   var records = csv.GetRecords<YourModel>();

   Dim records = csv.GetRecords(Of YourModel)()

   C#VB

   Make sure to replace YourModel with the actual name of your model class.

10. Iterate over the records and perform any required processing or validation:

    foreach (var record in records)
    {
        // Process each record as needed
    }

    For Each record In records
    	' Process each record as needed
    Next record

    C#VB

11. Optionally, establish a connection to your SQL Server database using ADO.NET or an ORM tool like Entity Framework.

12. Insert each record into the database using your chosen database access mechanism. For example, if you're using ADO.NET, you can leverage the SqlBulkCopy class to efficiently bulk insert the data.

13. Handle any exceptions that may occur during the import process and provide appropriate error messages or logging.

14. Test your application by running it and verifying that the CSV data is successfully imported into your database.

That's it! You've now successfully imported CSV data into your SQL Server database using C#.

Step 1: Reading the CSV File

When beginning the task of importing data from a CSV file, the first crucial step is to read the data within it accurately. Each line in a CSV file typically represents a data record, and each record consists of one or more fields, separated by commas.

We then employ the IronXL library to handle the CSV file. To read the CSV file using IronXL, you'll use its WorkBook and WorkSheet classes. The WorkBook class represents an entire spreadsheet, which could be an Excel file or, in our case, a CSV file. When you load the CSV file path into a WorkBook object, IronXL treats the CSV file as a spreadsheet/data table.

using IronXL;
using System.Data;

public class CSVReader
{
    // Reads a CSV file and converts it to a DataTable
    public DataTable ReadCSV(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        // Convert to DataTable for easier processing
        DataTable dataTable = sheet.ToDataTable(true); // Set to 'true' if your CSV has a header row
        return dataTable;
    }
}

using IronXL;
using System.Data;

public class CSVReader
{
    // Reads a CSV file and converts it to a DataTable
    public DataTable ReadCSV(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        // Convert to DataTable for easier processing
        DataTable dataTable = sheet.ToDataTable(true); // Set to 'true' if your CSV has a header row
        return dataTable;
    }
}

Imports IronXL
Imports System.Data

Public Class CSVReader
	' Reads a CSV file and converts it to a DataTable
	Public Function ReadCSV(ByVal filePath As String) As DataTable
		Dim workbook As WorkBook = WorkBook.Load(filePath)
		Dim sheet As WorkSheet = workbook.DefaultWorkSheet
		' Convert to DataTable for easier processing
		Dim dataTable As DataTable = sheet.ToDataTable(True) ' Set to 'true' if your CSV has a header row
		Return dataTable
	End Function
End Class

Imports IronXL
Imports System.Data

Public Class CSVReader
	' Reads a CSV file and converts it to a DataTable
	Public Function ReadCSV(ByVal filePath As String) As DataTable
		Dim workbook As WorkBook = WorkBook.Load(filePath)
		Dim sheet As WorkSheet = workbook.DefaultWorkSheet
		' Convert to DataTable for easier processing
		Dim dataTable As DataTable = sheet.ToDataTable(True) ' Set to 'true' if your CSV has a header row
		Return dataTable
	End Function
End Class

Step 2: Establishing a Database Connection

Establishing a connection to your SQL Server database is a fundamental step in the process of storing your CSV data. This step involves setting up a communication link between your application and the database server. A successful connection is crucial because, without it, transferring data to the database would be impossible.

This step focuses on creating and opening a connection using a connection string in C#. The connection string is a vital component as it contains the information needed to establish the connection. It's like a key that unlocks the door to your database.

using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

public class DatabaseConnector
{
    // Connection string to connect to the database
    private string connectionString = "your_connection_string_here";

    public SqlConnection ConnectToDatabase()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

public class DatabaseConnector
{
    // Connection string to connect to the database
    private string connectionString = "your_connection_string_here";

    public SqlConnection ConnectToDatabase()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

Public Class DatabaseConnector
    ' Connection string to connect to the database
    Private connectionString As String = "your_connection_string_here"

    Public Function ConnectToDatabase() As SqlConnection
        Dim connection As New SqlConnection(connectionString)
        connection.Open()
        Return connection
    End Function
End Class

Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

Public Class DatabaseConnector
    ' Connection string to connect to the database
    Private connectionString As String = "your_connection_string_here"

    Public Function ConnectToDatabase() As SqlConnection
        Dim connection As New SqlConnection(connectionString)
        connection.Open()
        Return connection
    End Function
End Class

The connectionString variable holds all the necessary details to connect to your SQL Server. It typically includes the server name, database name, user ID, and password. An example connection string looks like this: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;. It's essential to replace these placeholders with your actual server details.

Step 3: Storing Data in the Database

Having established a connection with the SQL Server database, the next critical step is to store the CSV data in the database. This step involves transferring the data you've read and processed into your SQL Server table. It's a crucial part of the data handling process, as it involves actual data migration from a local file to a database server.

In this step, we'll focus on how the CSV data, now stored in a DataTable, is transferred to the SQL Server database. We use a combination of C# and SQL Server features to accomplish this task efficiently.

using System;
using System.Data;
using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

public class DataImporter
{
    public void ImportData(DataTable dataTable)
    {
        using (SqlConnection connection = new DatabaseConnector().ConnectToDatabase())
        {
            // Check if the table exists and create it if it does not.
            string tableName = "CSVData"; // Use a valid SQL table name format
            string checkTable = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN ";
            string createTable = "CREATE TABLE " + tableName + " (";
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                createTable += $"[{dataTable.Columns[i].ColumnName}] NVARCHAR(MAX)";
                if (i < dataTable.Columns.Count - 1)
                    createTable += ", ";
            }
            createTable += ") END";
            SqlCommand createTableCommand = new SqlCommand(checkTable + createTable, connection);
            createTableCommand.ExecuteNonQuery();

            // Now we use SqlBulkCopy to import the data
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Data imported successfully!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

using System;
using System.Data;
using System.Data.SqlClient; // For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

public class DataImporter
{
    public void ImportData(DataTable dataTable)
    {
        using (SqlConnection connection = new DatabaseConnector().ConnectToDatabase())
        {
            // Check if the table exists and create it if it does not.
            string tableName = "CSVData"; // Use a valid SQL table name format
            string checkTable = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN ";
            string createTable = "CREATE TABLE " + tableName + " (";
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                createTable += $"[{dataTable.Columns[i].ColumnName}] NVARCHAR(MAX)";
                if (i < dataTable.Columns.Count - 1)
                    createTable += ", ";
            }
            createTable += ") END";
            SqlCommand createTableCommand = new SqlCommand(checkTable + createTable, connection);
            createTableCommand.ExecuteNonQuery();

            // Now we use SqlBulkCopy to import the data
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                    Console.WriteLine("Data imported successfully!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

Imports System
Imports System.Data
Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

Public Class DataImporter
    Public Sub ImportData(dataTable As DataTable)
        Using connection As SqlConnection = New DatabaseConnector().ConnectToDatabase()
            ' Check if the table exists and create it if it does not.
            Dim tableName As String = "CSVData" ' Use a valid SQL table name format
            Dim checkTable As String = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN "
            Dim createTable As String = "CREATE TABLE " & tableName & " ("
            For i As Integer = 0 To dataTable.Columns.Count - 1
                createTable &= $"[{dataTable.Columns(i).ColumnName}] NVARCHAR(MAX)"
                If i < dataTable.Columns.Count - 1 Then
                    createTable &= ", "
                End If
            Next
            createTable &= ") END"
            Dim createTableCommand As New SqlCommand(checkTable & createTable, connection)
            createTableCommand.ExecuteNonQuery()

            ' Now we use SqlBulkCopy to import the data
            Using bulkCopy As New SqlBulkCopy(connection)
                bulkCopy.DestinationTableName = tableName
                Try
                    bulkCopy.WriteToServer(dataTable)
                    Console.WriteLine("Data imported successfully!")
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Class

Imports System
Imports System.Data
Imports System.Data.SqlClient ' For .NET Core/.NET 5+, use Microsoft.Data.SqlClient instead

Public Class DataImporter
    Public Sub ImportData(dataTable As DataTable)
        Using connection As SqlConnection = New DatabaseConnector().ConnectToDatabase()
            ' Check if the table exists and create it if it does not.
            Dim tableName As String = "CSVData" ' Use a valid SQL table name format
            Dim checkTable As String = $"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}') BEGIN "
            Dim createTable As String = "CREATE TABLE " & tableName & " ("
            For i As Integer = 0 To dataTable.Columns.Count - 1
                createTable &= $"[{dataTable.Columns(i).ColumnName}] NVARCHAR(MAX)"
                If i < dataTable.Columns.Count - 1 Then
                    createTable &= ", "
                End If
            Next
            createTable &= ") END"
            Dim createTableCommand As New SqlCommand(checkTable & createTable, connection)
            createTableCommand.ExecuteNonQuery()

            ' Now we use SqlBulkCopy to import the data
            Using bulkCopy As New SqlBulkCopy(connection)
                bulkCopy.DestinationTableName = tableName
                Try
                    bulkCopy.WriteToServer(dataTable)
                    Console.WriteLine("Data imported successfully!")
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Class

It starts by opening a connection to the SQL Server database using the DatabaseConnector class, ensuring a pathway for data transactions. The method checks for the existence of the table named "CSVData" within the database.

If the table is not found, it proceeds to create it. The table's schema is constructed based on the DataTable schema passed to the method, with all columns set to NVARCHAR(MAX) to accommodate any text data. This is a generic approach and may need refinement to match specific data types more closely.

After that, an SQL command is formulated and executed to either verify the existence of the table or to create it. This ensures that the subsequent bulk copy operation has a destination table ready for data insertion. With the table ready, the SqlBulkCopy class is employed to transfer data from the DataTable directly into the SQL Server table. This operation is designed for high-performance bulk data transfers, making it suitable for handling large volumes of data efficiently.

Step 4: Putting It All Together

After diligently working through the earlier steps of reading CSV data, establishing a database connection, and preparing to transfer the data, we arrive at the final and crucial stage: integrating these individual components into a cohesive process.

This integration is done in the Main method of your C# application, where everything comes together, enabling the actual execution of data import from the CSV file to the SQL Server database.

class Program
{
    static void Main(string[] args)
    {
        string filePath = "path_to_your_csv_file.csv";
        CSVReader reader = new CSVReader();
        DataTable dataTable = reader.ReadCSV(filePath);
        DataImporter importer = new DataImporter();
        importer.ImportData(dataTable);
        Console.WriteLine("Data imported successfully!");
    }
}

class Program
{
    static void Main(string[] args)
    {
        string filePath = "path_to_your_csv_file.csv";
        CSVReader reader = new CSVReader();
        DataTable dataTable = reader.ReadCSV(filePath);
        DataImporter importer = new DataImporter();
        importer.ImportData(dataTable);
        Console.WriteLine("Data imported successfully!");
    }
}

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim filePath As String = "path_to_your_csv_file.csv"
		Dim reader As New CSVReader()
		Dim dataTable As DataTable = reader.ReadCSV(filePath)
		Dim importer As New DataImporter()
		importer.ImportData(dataTable)
		Console.WriteLine("Data imported successfully!")
	End Sub
End Class

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		Dim filePath As String = "path_to_your_csv_file.csv"
		Dim reader As New CSVReader()
		Dim dataTable As DataTable = reader.ReadCSV(filePath)
		Dim importer As New DataImporter()
		importer.ImportData(dataTable)
		Console.WriteLine("Data imported successfully!")
	End Sub
End Class

Replace path_to_your_csv_file.csv with the path to your CSV file.

Running the Project

Once you run the project, you'll see the following output. The success messages represent that all operations have been executed successfully and data is copied to the database.

Data imported successfully!

Data imported successfully!

Now, you can open SQL Server Management Studio (SSMS) and check the table under the database. You'll see the following data in the table.

Conclusion

This tutorial has guided you through the process of reading data from a CSV file and storing it in a SQL Server database using C#. By following these steps and utilizing the IronXL library, you can efficiently manage CSV data in your C# applications.

IronXL offers a free trial for users to experience its features before committing to a purchase. This fully functional trial allows potential users to test and evaluate the product in a live environment without any watermarks in production. After the trial period, if you decide to continue using IronXL for your projects, the licensing for the product starts from $999.