Using C# as Scripting Language to Import CSV data into SQL Server

Recently, I have been working on a side project related to data ingestion and analytics using Azure SQL Database. This project utilizes CSV files as the data source, which are then imported into the database for further analysis.

I wanted to automate the data import process using some simple scripting to make my life easier.

Then I found an interesting tool called dotnet-script. It allows us to run C# scripts (.csx) directly from the command line. I utilized this tool to create a script for loading and inserting CSV data into SQL Server.

Install Dotnet Script

We first need .NET SDK (6.0 or 7.0 or 8.0) installed, then we can run this command to install the dotnet-script:

dotnet tool install -g dotnet-script

The command above will install dotnet-script globally.

Hello, World!

Let’s begin with creating a new “HelloWorld.csx” file. Open it in the text editor and add the following code:

Console.WriteLine("Hello, World!");

Save the file and run it from the command prompt:

dotnet script HelloWorld.csx

This will display the legendary “Hello, World!” on the console.

Import NuGet Package

Now let’s move to the more serious matter. We will utilise the CSVHelper library to read CSV files. We can do this by adding the NuGet package reference at the top of our script:

#r "nuget: CsvHelper, 31.0.4"

We also need Microsoft.Data.SqlClient library to connect to the database:

#r "nuget: Microsoft.Data.SqlClient, 5.2.0"

We need to add these using statements as well:

using CsvHelper;
using System.Data;
using System.Globalization;
using Microsoft.Data.SqlClient;
using CsvHelper.Configuration;

Load CSV File

There is a situation where the CSV column names are different from the database column names. To handle that, we need to define the column mapping. We can use a list of tuples to create the mapping:

List<(string CsvColumn, string TableColumn)> columns = [
    ("DocumentNumber", "doc_no"),
    ("DocumentDate", "doc_date"),
    ("Customer", "cust_name"),
    ("Product", "prod_name")
];

Next, the code below will load the CSV file and store the data in the DataTable object:

var fileName = "data.csv";

// Open CSV file
var csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);
var csvReader = new CsvReader(File.OpenText(fileName), csvConfiguration);
var dataReader = new CsvDataReader(csvReader);

var dataTable = new DataTable();

// Configure DataTable columns
foreach (var column in columns)
{
    dataTable.Columns.Add(column.CsvColumn);
}

// Load data into DataTable
dataTable.Load(dataReader);

Insert Data into SQL Server

We will use the SqlBulkCopy class to perform the bulk insert into the SQL Server table:

var connectionString = "Data Source=localhost;Initial Catalog=TestDb;Integrated Security=True;TrustServerCertificate=True";

var bulkCopy = new SqlBulkCopy(connectionString);

// Set the database table name
bulkCopy.DestinationTableName = "invoice";

// Insert 5000 data per batch
bulkCopy.BatchSize = 5000;

// Configure the column name mapping
foreach (var column in columns)
{
    bulkCopy.ColumnMappings.Add(column.CsvColumn, column.TableColumn);
}

bulkCopy.WriteToServer(dataTable);

bulkCopy.Close();

This is the complete script:

#! "netcoreapp8.0"
#r "nuget: CsvHelper, 31.0.4"
#r "nuget: Microsoft.Data.SqlClient, 5.2.0"


using CsvHelper;
using System.Data;
using System.Globalization;
using Microsoft.Data.SqlClient;
using CsvHelper.Configuration;


List<(string CsvColumn, string TableColumn)> columns = [
    ("DocumentNumber", "doc_no"),
    ("DocumentDate", "doc_date"),
    ("Customer", "cust_name"),
    ("Product", "prod_name")
];


var fileName = "data.csv";

// Open CSV file
var csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);
var csvReader = new CsvReader(File.OpenText(fileName), csvConfiguration);
var dataReader = new CsvDataReader(csvReader);

var dataTable = new DataTable();

// Configure DataTable columns
foreach (var column in columns)
{
    dataTable.Columns.Add(column.CsvColumn);
}

// Load data into DataTable
dataTable.Load(dataReader);


var connectionString = "Data Source=localhost;Initial Catalog=TestDb;Integrated Security=True;TrustServerCertificate=True";

var bulkCopy = new SqlBulkCopy(connectionString);

// Set the database table name
bulkCopy.DestinationTableName = "invoice";

// Insert 5000 data per batch
bulkCopy.BatchSize = 5000;

// Configure the column name mapping
foreach (var column in columns)
{
    bulkCopy.ColumnMappings.Add(column.CsvColumn, column.TableColumn);
}

bulkCopy.WriteToServer(dataTable);

bulkCopy.Close();

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

The CSV data was successfully copied into SQL Server, all scripted out in C#. It’s quick, efficient, and saves me a ton of manual work.

Thank you for reading 👍