SQLite C#: Import CSV File into a Table

Summary: in this tutorial, you will learn how to import data from a CSV file into a table in SQLite database using C#.

To import data from a CSV file into a table in an SQLite database using a C# program, you follow these steps:

• First, read data from the CSV file.
• Second, iterate over each line and insert them into the table.

The following program illustrates how to read data from authors.csv file that consists of two columns first name and last name, and insert each line into the authors table in the pub database:

using CsvHelper;
using System.Globalization;
using Microsoft.Data.Sqlite;
namespace Pub;

public record Author(string FirstName, string LastName);

public class Program
{
    public static IEnumerable<Author> ReadAuthorsFromCSV(string filePath)
    {
        using var reader = new StreamReader(filePath);
        using var csv = new CsvReader(reader, CultureInfo.InvariantCulture);
        
        // Skip header of the csv file
        csv.Read();

        // Read the header of the csv file to map to fields
        csv.ReadHeader(); 

        while (csv.Read())
        {
            var firstName = csv.GetField<string>("Firstname");
            var lastName = csv.GetField<string>("Lastname");

            yield return new Author(firstName, lastName);
        }
    }
    public static void Main()
    {
        var csvFilePath = @"c:\db\authors.csv";
        var sql = "INSERT INTO authors(first_name, last_name) VALUES (@first_name, @last_name)";

        try
        {
            // Open a new database connection
            using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
            connection.Open();

            // Insert lines of CSV into the authors table
            foreach (var author in ReadAuthorsFromCSV(csvFilePath))
            {
                using var command = new SqliteCommand(sql, connection);
                command.Parameters.AddWithValue("@first_name", author.FirstName);
                command.Parameters.AddWithValue("@last_name", author.LastName);
                command.ExecuteNonQuery();
            }
        }
        catch (SqliteException ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}Code language: C# (cs)

In this program, we use the CsvHelper package that you can install by running the following command in the Package Manager Console:

Install-Package CsvHelperCode language: C# (cs)

Check out this tutorial for more information on how to read a CSV file in C# using the CsvHelper package.

Note that you can download the authors.csv file here.

How the program works.

Declaring Author record

Declare an Author record that includes two fields FirstName and LastName:

public record Author(string FirstName, string LastName);Code language: C# (cs)

Defining ReadAuthorsFromCSV() method

The ReadAuthorsFromCSV() method reads data from a CSV file specified by a filePath and returns an enumerable sequence of Author records.

public static IEnumerable<Author> ReadAuthorsFromCSV(string filePath)
{
    using var reader = new StreamReader(filePath);
    using var csv = new CsvReader(reader, CultureInfo.InvariantCulture);

    // Skip header of the csv file
    csv.Read();

    // Read the header of the csv file to map to fields
    csv.ReadHeader();

    while (csv.Read())
    {
        var firstName = csv.GetField<string>("Firstname");
        var lastName = csv.GetField<string>("Lastname");

        yield return new Author(firstName, lastName);
    }
}Code language: C# (cs)

How it works.

First, create a StreamReader from the CSV file specified by the filePath. The using statement ensures that the StreamReader is properly closed:

using var reader = new StreamReader(filePath);Code language: C# (cs)

Second, create a CsvReader to parse CSV data. The CultureInfo.InvariantCulture ensures consistent parsing across different locales:

using var csv = new CsvReader(reader, CultureInfo.InvariantCulture);Code language: C# (cs)

Third, read the first line from a CSV file, the header line. It advances the reader to the next line in the CSV file:

csv.Read();Code language: C# (cs)

Fourth, read the header row of the CSV file to map the column names to fields:

csv.ReadHeader();Code language: C# (cs)

Fifth, start a loop that iterates over each line in the CSV file. The Read() method reads the next line from the CSV file and returns true if successful, or false if there are no more lines:

while (csv.Read())Code language: C# (cs)

Sixth, retrieve the values of the FirstName and LastName columns and assign them to the firstName and lastName variables respectively:

var firstName = csv.GetField<string>("Firstname");Code language: C# (cs)

Finally, yield a new Author record for each row in the CSV file. The yield return allows the method to return multiple Author records iteratively without loading them all in memory simultaneously:

yield return new Author(firstName, lastName);Code language: C# (cs)

Defining main() method

First, declare a variable and initialize it with the path to the CSV file:

var csvFilePath = @"c:\db\authors.csv";Code language: C# (cs)

Second, declare a variable and initialize it with an SQL INSERT statement:

var sql = "INSERT INTO authors(first_name, last_name) VALUES (@first_name, @last_name)";Code language: C# (cs)

The @first_name and @last_name placeholders are parameterized values.

Third, create a new SqliteConnection object that connects to the SQLite database located at C:\db\pub.db:

using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");Code language: C# (cs)

The using statement ensures that the connection is automatically closed.

Fourth, open the database connection:

connection.Open();Code language: C# (cs)

Fifth, iterate over each Author record returned by the ReadAuthorsFromCSV() method:

foreach (var author in ReadAuthorsFromCSV(csvFilePath))Code language: C# (cs)

Sixth, bind the values of the FirstName and LastName properties of the Author record to the @first_name and @last_name parameters:

command.Parameters.AddWithValue("@first_name", author.FirstName);
command.Parameters.AddWithValue("@last_name", author.LastName);Code language: C# (cs)

Seventh, execute the SQL INSERT command to insert a new row into the authors table with the first name and last name derived from the Author record:

 command.ExecuteNonQuery();Code language: C# (cs)

Finally, the catch() block catches any SqliteException that may occur during the execution of the try block. If an exception occurs, it displays the exception message to the console:

catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

Summary

• Use the CsvHelper package to read data from a CSV file.
• Call the ExecuteNonQuery of the SqliteCommand object to execute an SQL INSERT statement to insert data from the CSV file into a table in the SQLite database.