How to Read Data From CSV File And Store It In Database C#

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 an 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;
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))
            {
                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!");
            }
        }
    }
}

using System;
using System.Data;
using System.Data.SqlClient;
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))
            {
                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!");
            }
        }
    }
}

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports CsvHelper

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

			Using reader = New StreamReader(csvPath)
			Using csv = New CsvReader(reader, CultureInfo.InvariantCulture)
				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
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:

Install-Package IronXL.Excel

or

dotnet add package IronXL

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,
    ...
);

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

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

   using System;
   using System.IO;
   using CsvHelper;

   using System;
   using System.IO;
   using CsvHelper;

   Imports System
   Imports System.IO
   Imports CsvHelper

   VB   C#

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

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

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

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

   VB   C#

   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 (var reader = new StreamReader(csvFilePath))
   {
       // code goes here
   }

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

   VB   C#

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 (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

   VB   C#

8. Optionally, configure the CsvReader object with any necessary settings. For example:

   csv.Configuration.HasHeaderRecord = true;

   csv.Configuration.HasHeaderRecord = true;

   csv.Configuration.HasHeaderRecord = True

   VB   C#

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>();

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

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

   VB   C#

   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)
    {
        // code goes here
    }

    foreach (var record in records)
    {
        // code goes here
    }

    For Each record In records
    	' code goes here
    Next record

    VB   C#
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;
public class CSVReader
{
    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;
public class CSVReader
{
    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
Public Class CSVReader
	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.

public class DatabaseConnector
{
    private string connectionString = "your_connection_string_here";

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

public class DatabaseConnector
{
    private string connectionString = "your_connection_string_here";

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

Public Class DatabaseConnector
	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.

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);
                }
            }
        }
    }
}

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);
                }
            }
        }
    }
}

Public Class DataImporter
	Public Sub ImportData(ByVal 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 i
			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

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!

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 an 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 $749.