Parsing CSV file into Dictionary C#

I am looking to put the contents of a csv file into a dictionary:

public IDictionary<string, string> allContracts = new Dictionary<string, string>();

I am wondering how to do this as I have tried to find ways to use a csv parser however cannot find an effective method. I would like the code to run within:

public void CheckAvailability()
    {
       //this runs following a click even in a different section of my code

    }

Below is some sample data from the csv file I am looking to manipulate:

Vehicle ID,Customer ID,Booked Date,Booked_date_week_number,Number of Days Booked,BookingStatus
    135,CU1,1-Jul-19,27,0,Booked
    71,CU2,1-Jul-19,27,0,Booked
    73,CU3,1-Jul-19,27,0,Booked
    93,CU4,1-Jul-19,27,0,Booked
    137,CU5,1-Jul-19,27,0,Booked
    24,CU6,1-Jul-19,27,0,Booked

As you can see the column names are also included in the csv file, this is not an issue and is actually fundamental for the program I am creating.

Also please note I think I need to use a csv parser as the files being used are bigger than what's shown here, and so I don't want to encounter memory issues.

Solution 1:

I would approach this differently, by making a class that represents each row and, if needed, creating a dictionary from that

void Main()
{
    // See https://joshclose.github.io/CsvHelper/
    using (var reader = new StreamReader(@"C:\Temp\Bookings.csv"))
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        var records = csv.GetRecords<Booking>();
        var dictionary = records.ToDictionary(r => r.VehicleID, r => r);    
    }
}

public enum BookingStatus
{
    Booked,
    // TOOD: Add other statuses
}
public class Booking
{
    [Name("Vehicle ID")]
    public int VehicleID { get; set; }
    [Name("Customer ID")]
    public string CustomerID { get; set; }
    [Name("Booked Date")]
    public DateTime BookedDate { get; set; }
    [Name("Booked_date_week_number")]
    public int BookedDateWeekNumber { get; set; }
    [Name("Number of Days Booked")]
    public int NumberOfDaysBooked { get; set; }
    [Name("BookingStatus")]
    public BookingStatus BookingStatus { get; set; }
}

Note that using the Vehicle ID as the key in your dictionary fails if the same vehicle ID appears more than once in the CSV file. You may find it more useful to create an array or list, which you could then query as needed with Linq, e.g. replace the line that creates the dictionary

var dictionary = records.ToDictionary(r => r.VehicleID, r => r);

with

var csvData = records.ToArray();

then you can do things like

var bookingsForVehicle = csvData.Where(d => d.VehicleID == 42);

The reason to call a method like .ToArray() or .ToList() is to avoid enumerating the results multiple times, which would cause the file to be parsed multiple times.

Solution 2:

I maintain some libraries that can help with this: Sylvan.Data.Csv and Sylvan.Data. These libraries offer an API very similar to the CsvHelper library that Eric J. suggested. The main difference is that you don't need to manually map the column names assuming that the property names are a "PascalCase" conversion of the header in the CSV, which can be a marginal convenience. My data binder (the code behind GetRecords<T>) is DbDataReader-agnostic, so it can also be used with other data sources, such as a SqlServer result set, or my Sylvan.Data.Excel library (see commented out lines below). These libraries are also the fastest-in-class in the .NET ecosystem.

CsvHelper probably offers some capabilities that my libraries do not, but for direct mappings like this the code is nearly identical. Modified Eric J.s sample:

using Sylvan.Data;
using Sylvan.Data.Csv;
//using Sylvan.Data.Excel;

using (var data = CsvDataReader.Create("bookings.csv"))
//using (var data = ExcelDataReader.Create("bookings.xlsx"))
{
    var dictionary =
        data.GetRecords<Booking>()
        .ToDictionary(r => r.VehicleId, r => r);
}

public enum BookingStatus
{
    Booked,
    // TODO: Add other statuses
}

public class Booking
{
    public int VehicleId { get; set; }
    public string CustomerId { get; set; }
    public DateTime BookedDate { get; set; }
    public int BookedDateWeekNumber { get; set; }
    public int NumberOfDaysBooked { get; set; }
    public BookingStatus BookingStatus { get; set; }
}