public List <RemarketingData> ReadRmkDataFromSourceFile(ConverterConfiguration config) { var fileData = new List <RemarketingData>(); // The incoming file is a BIFF 5.0 version of the excel file (Excel 2010) // so we have to use ExcelReader to read the contents. // running ExcelDataReading on .NET Core requires we specify the encoding provider System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); // the easiest way to access the data is to read the entire workbook into a data set // and then parse the dataset data DataSet ds; using (var stream = File.Open(config.SourceFile, FileMode.Open, FileAccess.Read)) { using var reader = ExcelReaderFactory.CreateReader(stream); ds = reader.AsDataSet(); } // each worksheet in the excel file is a data table in the data set var sheet = ds.Tables[0]; int rowIndex = -1; foreach (DataRow row in sheet.Rows) { // track the row number so we can skip the first N header rows rowIndex++; if (rowIndex < config.NumberOfHeaderRows) { continue; } var items = row.ItemArray; // convert the data table data row to a strongly typed row of data. // ReSharper disable once UseObjectOrCollectionInitializer var data = new RemarketingData(); data.AccountNumber = items[0].ToString(); // if we encounter a missing account number, we'll use that to signify the end of the data rows. if (string.IsNullOrEmpty(data.AccountNumber)) { break; } data.LoanNumber = items[1].ToString(); data.LastName = items[2].ToString(); data.FirstName = items[3].ToString(); data.Balance = Convert.ToDecimal(items[4]); data.Year = items[5].ToString(); data.Make = items[6].ToString(); data.Model = items[7].ToString(); data.Vin = items[8].ToString(); data.Mileage = Convert.ToInt32(items[9]); data.RepoAgentName = items[10].ToString(); data.RepoAgentsLookup = items[11].ToString(); data.LocationOfUnit = items[12].ToString(); data.DateOfRepo = Convert.ToDateTime(items[13]); data.DateOfClear = Convert.ToDateTime(items[14]); fileData.Add(data); } _logger.Information($"Read {fileData.Count} row(s) from: {config.SourceFile}"); return(fileData); }
public List <RemarketingData> ReadRmkDataFromSourceFile(ConverterConfiguration config) { var fileData = new List <RemarketingData>(); // The incoming file is a BIFF 5.0 version of the excel file (Excel 2010) // so we have to use ExcelReader to read the contents. // running ExcelDataReading on .NET Core requires we specify the encoding provider // the easiest way to access the data is to read the entire workbook into a data set // and then parse the dataset data DataSet ds; using (var stream = File.Open(config.SourceFile, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { ds = reader.AsDataSet(); } } var colMap = config.ClientConfiguration.SourceColumnMap; // each worksheet in the excel file is a data table in the data set var sheet = ds.Tables[0]; int rowIndex = -1; foreach (DataRow row in sheet.Rows) { // track the row number so we can skip the first N header rows rowIndex++; if (rowIndex < config.ClientConfiguration.NumberOfHeaderRows) { continue; } var items = row.ItemArray; // convert the data table data row to a strongly typed row of data. // ReSharper disable once UseObjectOrCollectionInitializer var data = new RemarketingData(); data.AccountNumber = Get <string>(items, config.ClientConfiguration.SourceColumnMap.AccountNumber, rowIndex, nameof(data.AccountNumber)); // if we encounter a missing account number, we'll use that to signify the end of the data rows. if (string.IsNullOrEmpty(data.AccountNumber)) { _logger.Debug($"encountered empty Account Number value at row {rowIndex} col {colMap.AccountNumber}. Assuming end of file."); break; } data.LoanNumber = Get <string>(items, colMap.LoanNumber, rowIndex, nameof(data.LoanNumber)); data.LastName = Get <string>(items, colMap.LastName, rowIndex, nameof(data.LastName)); data.FirstName = Get <string>(items, colMap.FirstName, rowIndex, nameof(data.FirstName)); data.Balance = Get <decimal>(items, colMap.LoanBalance, rowIndex, nameof(data.Balance)); data.Year = Get <string>(items, colMap.Year, rowIndex, nameof(data.Year)); data.Make = Get <string>(items, colMap.Make, rowIndex, nameof(data.Make)); data.Model = Get <string>(items, colMap.Model, rowIndex, nameof(data.Model)); data.Vin = Get <string>(items, colMap.Vin, rowIndex, nameof(data.Vin)); data.Mileage = Get <int>(items, colMap.Mileage, rowIndex, nameof(data.Mileage)); data.RepoAgentName = Get <string>(items, colMap.RepoAgentName, rowIndex, nameof(data.RepoAgentName)); data.RepoAgentsLookup = Get <string>(items, colMap.RepoAgentsLookup, rowIndex, nameof(data.RepoAgentsLookup)); data.LocationOfUnit = Get <string>(items, colMap.LocationOfUnit, rowIndex, nameof(data.LocationOfUnit)); data.DateOfRepo = Get <DateTime>(items, colMap.DateOfRepo, rowIndex, nameof(data.DateOfRepo)); data.DateOfClear = Get <DateTime>(items, colMap.DateOfClear, rowIndex, nameof(data.DateOfClear)); fileData.Add(data); } _logger.Information($"Read {fileData.Count} row(s) from: {config.SourceFile}"); return(fileData); }