Error: 'FileId' field header not found. Parameter name: name

[HTI-DaraiusKeeka]

Hi

I am new to LumenWorks, my apologies if I have missed something in the documentation.

I have a CSV file with 200 off columns. Typically there would be close to 65000 rows. Importing these rows into a SQL Database Table was fine, until I added a new field in the SQL Database Table called "FileId" - which does not exist in the CSV File. I wish to Inject this field and the relevant value.

How do I do this please?

const string fileToWorkWith = @"C:\Data\Fidessa ETP Files\Import\2019\myCsvFile.csv";
Output.WriteLn($"Working with file {fileToWorkWith}.");

const string databaseConnectionString = "Server=MyServer;Database=DB;User Id=sa; Password = xyz;";

Output.WriteLn($"Checking if working file exists.");

if (new System.IO.FileInfo(fileToWorkWith).Exists == false)
{
    Output.WriteLn("Working file does not exist.", Output.WriteTypes.Error);
    return;
}

Output.WriteLn("Reading file.");
using (var reader = new CsvReader(new StreamReader(fileToWorkWith), true, char.Parse(",") ))
{
    reader.Columns = new List<LumenWorks.Framework.IO.Csv.Column>
    {
        new LumenWorks.Framework.IO.Csv.Column { Name = "FileId", Type = typeof(int), DefaultValue = "1" },
    };
    reader.UseColumnDefaults = true;
    
    Output.WriteLn("Checking fields in file exist in the Database.");
    foreach (var fieldName in reader.GetFieldHeaders())
    {
        
        if (Fields.IsValid(fieldName.Replace(" ","_")) == false)
        {
            Output.WriteLn($"A new field named {fieldName} has been found in the file that does not exist in the database.", Output.WriteTypes.Error);
            return;
        }
    }

    using (var sbc = new SqlBulkCopy(databaseConnectionString))
    {
        sbc.DestinationTableName = "FidessaETP.tableARC_EventsOrderAndFlow_ImportTest";
        sbc.BatchSize = 1000;

        Output.WriteLn("Mapping available Csv Fields to DB Fields");
        foreach (var field in reader.GetFieldHeaders().ToArray())
        {
            sbc.ColumnMappings.Add(field, field.Replace(" ", "_"));
        }

        sbc.WriteToServer(reader);
    }

}
{
    reader.Columns = new List<LumenWorks.Framework.IO.Csv.Column>
    {
        new LumenWorks.Framework.IO.Csv.Column { Name = "FileId", Type = typeof(int), DefaultValue = "1" },
    };
    reader.UseColumnDefaults = true;
    
    Output.WriteLn("Checking fields in file exist in the Database.");
    foreach (var fieldName in reader.GetFieldHeaders())
    {
        
        if (Fields.IsValid(fieldName.Replace(" ","_")) == false)
        {
            Output.WriteLn($"A new field named {fieldName} has been found in the file that does not exist in the database.", Output.WriteTypes.Error);
            return;
        }
    }

    using (var sbc = new SqlBulkCopy(databaseConnectionString))
    {
        sbc.DestinationTableName = "FidessaETP.tableARC_EventsOrderAndFlow_ImportTest";
        sbc.BatchSize = 1000;

        Output.WriteLn("Mapping available Csv Fields to DB Fields");
        foreach (var field in reader.GetFieldHeaders().ToArray())
        {
            sbc.ColumnMappings.Add(field, field.Replace(" ", "_"));
        }

        sbc.WriteToServer(reader);
    }

}

The Error Details

Message: 'FileId' field header not found. Parameter name: name

Source:  LumenWorks.Framework.IO

Stack Trace:
System.ArgumentException: 'FileId' field header not found.
Parameter name: name
at LumenWorks.Framework.IO.Csv.CsvReader.System.Data.IDataRecord.GetOrdinal(String name)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerCommon(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at Haitong.Test.CsvImporter.Program.Main(String[] args) in C:\Development\Workspaces\UK OPS Data Warehouse\UK OPS Data Warehouse\Haitong.Test.CsvImporter\Program.cs:line 86