Excel Data Importer Assignment
Frontend Requirements
1. File Import Page:
• Create a page with a drag-and-drop file upload option. Provide a fallback
file input button for users who prefer not to drag and drop.
• Only accept .xlsx files with a maximum file size of 2 MB.
2. Error Display:
• If the backend returns validation errors for the file, display them in a
modal dialog.
• Include the row number and a description of the error for each invalid row.
• For files with multiple sheets, show validation errors in separate tabs, one
for each sheet.
3. Data Preview:
• Provide a dropdown listing the names of all sheets in the uploaded file.
• When a sheet is selected, display its data in a paginated table
• Format dates in DD-MM-YYYY format.
• Format numeric values using the Indian number format (e.g.,
12,34,456.00).
• Allow users to delete rows:
o Show a delete icon next to each row.
o Prompt the user with a confirmation dialog before deleting a row.
o Delete rows only after user confirmation.
4. Data Import:
• Provide an Import button to import all rows without errors to the
database.
• If some rows have errors, import only the valid rows and skip the invalid
ones.
• Display a success message upon successful import and highlight skipped
rows.
Backend Requirements
1. File Validation:
• Process the uploaded .xlsx file on the backend using a library like xlsx or
exceljs.
• Validate the file based on the following rules:
1. The sheet must contain the following columns: Name, Amount, Date,
and Verified (Yes or No).
2. Validation rules:
a. Name, Amount, and Date are mandatory.
b. The Date must be valid and fall within the current month.
c. The Amount must be numeric and greater than zero.
3. If validation fails, return a detailed error response with:
a. Sheet name.
b. Row number.
c. Description of the error.
2. Support for Future Extensions:
• Code the backend to support new sheets with diZerent column names
and validation rules without duplicating code.
a. For example:
1. Some sheets may allow previous-month dates also.
2. Some sheets may have more than one columns of same type. For
example, there may be Invoice date and receipt date.
3. Certain columns might not require mandatory fields.
4. Some columns may allow zero as a valid value.
• Use a separate configuration file to map:
a. Excel sheet column names to database field names.
b. Validation rules for each sheet.
3. Database Interaction:
• Use MongoDB Atlas (free tier) to store the imported data.
• Ensure the backend can handle thousands of rows eZiciently.
Tech Stack
1. Frontend:
• [Link] with Tailwind CSS for styling.
• Use pagination to handle large datasets in the table.
• TypeScript is optional, but its use is encouraged.
2. Backend:
• [Link] with [Link] for API development.
• Use Mongoose for database interactions with MongoDB.
3. Database
• Use MongoDB Atlas (free version) for data storage.
• Ensure the database is not restricted to any specific IP for easier testing.
Additional Points to Consider:
Repository:
• Create a public GitHub repository and share the link.
• Ensure logical and meaningful commit messages.
Scalability:
• Both the frontend and backend must be optimized to handle large .xlsx
files with thousands of rows without performance degradation.
Error Handling:
• Implement robust error handling on both frontend and backend to
manage unexpected scenarios (e.g., corrupted files, server issues, or
database errors).
Testing:
• Ensure the solution works with a variety of test cases, including:
o Empty files.
o Files missing mandatory columns.
o Files with invalid data.
Evaluation Criteria
Code Quality:
• Clean, modular, and reusable code.
• Proper use of comments and documentation.
Scalability:
• EZicient handling of large files on both the frontend and backend.
Frontend Usability:
• Intuitive UI for file upload, error handling, and data preview.
Backend Robustness:
• Comprehensive validation and support for future extensions.
• EZicient database interactions.
Additional Features:
• Bonus points for additional features such as:
o Exporting validated data back to .xlsx.
o Real-time progress indicators during file upload and processing.