public bool InvalidCellData() { using (ReadAndStreamFile()) { var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; var dataSet = reader.AsDataSet(conf); var dataTable = dataSet.Tables[0]; var rows = Enumerable.Range(0, reader.FieldCount).Select(i => reader.Read()).ToArray(); errorDetected = false; for (var ColumnIndexNumber = 0; ColumnIndexNumber < dataTable.Columns.Count; ColumnIndexNumber++) { for (var RowIndexNumber = 0; RowIndexNumber < dataTable.Rows.Count; RowIndexNumber++) { //RowIndexNumber is row number starts from index number 0 //ColumnIndexNumber is column number starts from index number 0 var data = dataTable.Rows[RowIndexNumber][ColumnIndexNumber]; var excel = new ExcelDataReaderFile(); var cellAddress = excel.GetAddress(ColumnIndexNumber, RowIndexNumber + 1); if (data.ToString().Length != 0) { if (data.GetType() == reader.GetFieldType(ColumnIndexNumber) && data.ToString().Length > rowDataSizeLimit) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents."); errorDetected = true; } else if (data.ToString().Length <= rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber)) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} data {data} data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type."); errorDetected = true; } else if (data.ToString().Length > rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber)) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents. Data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type."); errorDetected = true; } else { } } //else //{ // Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is empty. Supply valid cell data."); // errorDetected = false; //} } } reader.Dispose(); reader.Close(); return(errorDetected); } }
public static void Main(string[] args) { string path = ConfigurationManager.AppSettings["excelSourcePath"].ToString(); string search = ConfigurationManager.AppSettings["extension"].ToString(); string database = ConfigurationManager.AppSettings["database"].ToString(); Logger.Info("Starting validation"); Logger.Info($"Looking for {search} files in {Regex.Unescape(path)}"); ExcelDataReaderFile ExcelDataReaderFile = new ExcelDataReaderFile(); string[] fileEntries = Directory.GetFiles(path, "*" + search + "*", SearchOption.AllDirectories); Logger.Info($"Found {fileEntries.Length} file(s) to validate"); dynamic counter = 0; BasicValidation BasicValidation = new BasicValidation(); if (!BasicValidation.NoFilesInAFolder(fileEntries, path)) { foreach (string file in fileEntries) { BasicValidation basicValidation = new BasicValidation(file, 128, 32767, 128, 128); counter += 1; Logger.Info($"Validating {basicValidation.GetFileName(file)}"); if (basicValidation.InvalidColumnNames() || basicValidation.InvalidCellData()) { //if the above statments are true then run those methods and log an error Logger.Fatal($"{counter}) {basicValidation.GetFileName(file)} in {file} has failed validation. Check the error log for details."); Console.WriteLine("Press any key to continue..."); Console.ReadKey(); } else { ExcelDataReaderFile excelDataReaderFile = new ExcelDataReaderFile(); foreach (DataTable dataTable in excelDataReaderFile.GetDataTable(file)) { Sql sql = new Sql(dataTable.TableName, dataTable, excelDataReaderFile.GetColumnNames(dataTable), excelDataReaderFile.RowsDataTypes(dataTable)); sql.UseExistingDatabaseGet_Table_Columns_Rows_DataTypes(database); Logger.Info($"{counter}) file number {counter} with file path {file} has been uploaded to the database"); } Console.WriteLine("Press any key to continue..."); Console.ReadKey(); } } } else { Console.WriteLine("Press any key to continue..."); Console.ReadKey(); } }
public Boolean DuplicateColumnNames() { using (ReadAndStreamFile()) { reader.Read(); { var ColumnsNames = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToList(); var excel = new ExcelDataReaderFile(); errorDetected = false; //looping through column names for (int columnIndexNumber = 0; columnIndexNumber < ColumnsNames.Count; columnIndexNumber++) { //looping through rows for (int columnIndexNum = 0; columnIndexNum < ColumnsNames.Count; columnIndexNum++) { var cellAddressA = excel.GetAddress(columnIndexNumber, 0); var cellAddressB = excel.GetAddress(columnIndexNum, 0); //so lets say we are talking about column A and Column C, if ColumnA and Column C are not null if (ColumnsNames[columnIndexNumber] != null && ColumnsNames[columnIndexNum] != null) { //so if column A were never put against column C to check if they match if (ColumnsNames[columnIndexNumber] != ColumnAlreadyMatchedA && ColumnsNames[columnIndexNum] != ColumnAlreadyMatchedB) { //check every column against eachother apart from checking it with itself ( say if columnIndexNumber and columnIndexNum are the same then it emans we are trying to match it with itself) // any column say ColumnsNames[columnIndexNumber] is column A already matched with B, C and D so going backwards doesnt check column B,C,D against column A if (ColumnsNames[columnIndexNumber] == ColumnsNames[columnIndexNum] && columnIndexNumber != columnIndexNum) { // and so Column a is given "ColumnAlreadyMatchedA"name ColumnAlreadyMatchedA = ColumnsNames[columnIndexNumber]; ColumnAlreadyMatchedB = ColumnsNames[columnIndexNum]; Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddressA} with column name {ColumnsNames[columnIndexNumber]} matches {cellAddressB} with column name {ColumnsNames[columnIndexNum]}"); errorDetected = true; } else { // errorDetected = false; } } } } } } reader.Dispose(); reader.Close(); return(errorDetected); } }
public static void ChooseDatabase(Sql Sql) { ExcelDataReaderFile excelDataReaderFile = new ExcelDataReaderFile(); if (DatabaseWithFolderNameExists(excelDataReaderFile.GetFolderName())) { listOfDatabases.Add(excelDataReaderFile.GetFolderName()); Console.WriteLine($"would you like to use the existing database in the list below Yes/No"); ExistingDatabases(); var answer = Console.ReadLine(); if (answer.ToLower() == "no") { Console.WriteLine("What would you like to call your database?"); while (DatabaseWithFolderNameExists(Console.ReadLine())) { Console.WriteLine($"{Console.ReadLine()} already exists. The current list of databases are."); ListOfDatabases(); Console.WriteLine("Please choose a different name"); } if (Console.ReadLine() != null) { var newDb = Console.ReadLine(); Sql.CreateDbGet_Table_Columns_Rows_DataTypes(newDb); listOfDatabases.Add(newDb); Console.WriteLine("existing database"); ExistingDatabases(); } else { Sql.UseExistingDatabaseGet_Table_Columns_Rows_DataTypes(excelDataReaderFile.GetFolderName()); } } else { Console.WriteLine("Choose database from the list below by typing its name."); ExistingDatabases(); Sql.UseExistingDatabaseGet_Table_Columns_Rows_DataTypes(Console.ReadLine()); } } else { Sql.CreateDbGet_Table_Columns_Rows_DataTypes(excelDataReaderFile.GetFolderName()); } }
public Boolean InvalidColumnNames() { using (ReadAndStreamFile()) { reader.Read(); { int counter = 0; var ColumnsNames = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToList(); if (ColumnsNames.Count != 0 && reader.Read() == true) { errorDetected = false; for (int columnNumber = 0; columnNumber < ColumnsNames.Count; columnNumber++) { var excel = new ExcelDataReaderFile(); var cellAddress = excel.GetAddress(counter, 0); counter += 1; if (ColumnsNames[columnNumber] != null && ColumnsNames[columnNumber].ToString().Length > columnNameSizeLimit) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {columnNumber.ToString().Length} characters long and exceeds {columnNameSizeLimit} character column name limit. Supply a valid column name."); errorDetected = true; } else if (ColumnsNames[columnNumber] == null) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is empty. Supply a valid column name."); errorDetected = true; } else { } continue; } } else { Logger.Error($"[{GetFileName(file)}]{reader.Name} is empty and cannot be validated. Supply a non-empty file."); errorDetected = true; }; } reader.Dispose(); reader.Close(); return(errorDetected); } }