Writing CSV To Datatable With identity Row

Question

I have a SQL Server database table of the following design:

    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [State]        NVARCHAR (MAX) NOT NULL,
    [Abbreviation] NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_USStates] PRIMARY KEY CLUSTERED ([Id] ASC)

I have a .csv file, in the following format:

Alabama, AL
Alaska, AK

Note: there is no ID field in the .csv file.

When I attempt to import the .csv file into my SQL Server database I receive an error because it is attempting to write "Alabama" (field 1 of the .csv) to the Id field (field 1 in the database table).

How can I tell CSVHelper that field 1 in the database is an Identity field, and to ignore it, thus writing "Alabama" to field 2, and "AL" to field 3?

Thanks in advance for your help.

ADDENDUM: I am importing using a web form. There are probably errors here as well, as this is the first time I have tried coding something like this...

public async Task<IActionResult> OnPostAsync()
        {

            using (var reader = new StreamReader(@"\\SomeComputer\SomePath\states.csv"))
            using (var csv = new CsvReader(reader))
            {
                csv.Configuration.HasHeaderRecord = false;

                IEnumerable<USStates> records = csv.GetRecords<USStates>();
                foreach (var record in records)
                {
                    _context.USStates.Add(record);
                    await _context.SaveChangesAsync();
                }
            }

            return RedirectToPage("./Index");

        }


Note: Sample states.csv data:


"Alaska","AK"
"American Samoa","AS"
"Arizona","AZ"
"Arkansas","AR"
"California","CA"
"Colorado","CO"
"Connecticut","CT"
"Delaware","DE"
"District Of Columbia","DC"
"Florida","FL"
"Georgia","GA"
"Guam","GU"
"Hawaii","HI"
"Idaho","ID"
"Illinois","IL"
"Indiana","IN"

Answer 1

You can use a ClassMap to indicate which columns to look for and where to look for them.

public async Task<IActionResult> OnPostAsync()
{
    using (var reader = new StreamReader(@"\\SomeComputer\SomePath\states.csv"))
    using (var csv = new CsvReader(reader))
    {
        csv.Configuration.HasHeaderRecord = false;
        csv.Configuration.RegisterClassMap<USStatesMap>();

        IEnumerable<USStates> records = csv.GetRecords<USStates>();
        foreach (var record in records)
        {
            _context.USStates.Add(record);
            await _context.SaveChangesAsync();
        }
    }
}

public class USStates
{
    public int Id { get; set; }
    public string State { get; set; }
    public string Abbreviation { get; set; }
}

public class USStatesMap : ClassMap<USStates>
{
    public USStatesMap()
    {
        Map(m => m.State).Index(0);
        Map(m => m.Abbreviation).Index(1);
    }
}