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);
}
}
