Excel Data Validator Application

1. Project Overview
The purpose of this project is to develop a C# console application that can read Excel files and validate the data in each column according to predefined data types (Text, DateTime, Currency, Number). In case of any data inconsistency, the program should log error messages in a new column titled ValidationMessage for each row that contains an error.
2. Functional Requirements
1. Input File Handling
o The application should be able to read Excel files in .xlsx or .xls format.
o The user will specify the file path for the Excel file from the console.
2. Column Data Type Validation The module will validate each column in the Excel file for data consistency. The following data types need validation:
o Text Validation:
 If a column is identified as containing text (e.g., user-configured or by the header), each cell should contain only text.
 Non-text values should trigger an error.
o DateTime Validation:
 If a column is identified as containing DateTime values, each cell must be in a valid DateTime format (e.g., MM/dd/yyyy, yyyy-MM-dd).
 Invalid DateTime values should trigger an error.
o Currency Validation:
 If a column is identified as containing currency values, each cell must be a valid currency format (e.g., $123.45, €100.00).
 Invalid currency values should trigger an error.
o Number Validation:
 If a column is identified as containing numeric values, each cell should be a valid numeric value (integer or decimal).
 Non-numeric values should trigger an error.
3. Validation Logic
o The application will check each cell in a column for type consistency.
o For any invalid data, the program will log an error in a new column called ValidationMessage in the same row.
o The error message should specify the type of error and the affected column.
4. Error Message Format
o The ValidationMessage column will contain error messages such as:
 "Expected text but found a number."
 "Invalid date format."
 "Invalid currency format."
 "Expected a number but found text."
o If a row has no errors, the ValidationMessage column will be empty or contain "No errors."
5. Output File
o After the validation process, the program should output a new Excel file with the ValidationMessage column added.
o The new file should be saved in the same format as the input file (Excel).
3. Non-Functional Requirements
1. Performance
o The application should efficiently process files with up to 10,000 rows and 50 columns without significant delays.
2. Compatibility
o The program should work with .xlsx and .xls Excel file formats.
o The application should be compatible with C# .NET Core or .NET Framework 4.7.2 and higher.
3. Error Reporting
o The error messages should be concise and clear for easy understanding, even for users with minimal technical knowledge.
4. Ease of Use
o The user should interact with the console application to input the file path, initiate the validation process, and receive feedback about errors and the output file location.
4. Technical Requirements
1. Technology Stack
o Programming Language: C#
o Excel Library: EPPlus or ClosedXML (for reading and writing Excel files).
o .NET Framework: .NET Core or .NET Framework 4.7.2 and above.
2. Input/Output
o Input: Excel file in .xlsx or .xls format.
o Output: Modified Excel file with a ValidationMessage column containing error messages, saved with the same format as the input file.
3. Validation Logic
o For each column, determine the expected data type (Text, DateTime, Currency, Number).
o Validate each cell’s data type based on the expected data type.
o If an error is found, append the error message to the ValidationMessage column.
4. Error Logging
o Each error should include the following details:
 Row number.
 Column name.
 Description of the issue.
 Error message.
5. User Stories
1. As a user, I want to upload an Excel file and check for any data inconsistencies, so I can ensure that all data conforms to the correct type (Text, DateTime, Currency, Number).
2. As a user, I want to see error messages in the ValidationMessage column if any data in a row does not conform to the expected format.
3. As a user, I want to download a modified version of the Excel file that includes the error messages for the rows with invalid data.
6. Acceptance Criteria
1. File Handling:
o The application should successfully read an Excel file and process all the rows and columns for validation.
2. Column Validation:
o The validation logic must correctly validate Text, DateTime, Currency, and Number data types, identifying any inconsistencies and generating appropriate error messages.
3. Validation Messages:
o A ValidationMessage column should be added to the output Excel file, showing the error message for each row with invalid data.
4. Output File:
o The modified Excel file should include the new ValidationMessage column and preserve the original structure of the file.
o The user should be able to download the file after the validation process.
. Example Flow
1. User Interaction:
o The user runs the console application.
o The user is prompted to input the path of the Excel file they wish to validate.
2. Validation Process:
o The application processes the file.
o For each column, it performs type validation on each cell.
o Errors are recorded in the ValidationMessage column.
3. Output:
o The application outputs a new Excel file with the ValidationMessage column and error descriptions.
8. Future Enhancements (Optional)
1. User-Defined Column Validation:
o Allow users to specify the expected data types for each column, either via configuration or an input prompt.
2. Support for Additional Data Types:
o Extend the validation to support additional types such as Boolean or email format.
3. Command-Line Arguments:
o Allow the user to specify the file path and other options via command-line arguments instead of using interactive prompts.