![MFV Tech Talk #3 - Microservice Architect | Hill](https://static-careers.moneyforward.vn/Blog/Hill-thumb%20(1).png)
![Tips to ensure the quality of CSV Import feature in Web Applications](/_ipx/w_1920,f_png/images/blog/detail-shape-heading.png)
Tips to ensure the quality of CSV Import feature in Web Applications
![Tips to ensure the quality of CSV Import feature in Web Applications](https://static-careers.moneyforward.vn/Tips to ensure the quality of CSV Import feature in Web Applications.png)
Introduction
When my team implemented this feature, it was the first time I encountered it, leaving me clueless about how to analyze the user story, define the test scope, and conduct thorough testing. Consequently, after releasing the feature to production, we received reports of issues from users. Many of these issues could have been prevented if I established a proper test plan, prepared more scenarios, more test cases, and utilized diverse test data to ensure more quality coverage during the testing phase. Thus, this tech blog emerged from the lessons learned through my team's experience and my own research efforts. My aim is to assist you in enhancing the quality of the CSV import feature within your product.
Understanding CSV and Its Import Feature
What is a CSV file?
A CSV (Comma-Separated Values) file is a plain text file format used to store tabular data. It serves as the backbone of data exchange between various software applications, databases, and systems due to its simplicity, lightweight nature, and widely supported. In a CSV file, each line represents a single row of data, with individual values within each row separated by commas (or other delimiters such as semicolons or tabs).
What is a CSV import feature?
Allowing users to upload CSV files containing structured data for importing purposes has become a very common feature in web applications because it empowers users to upload bulk data swiftly, saving time and effort. Once uploaded, the application processes the CSV file and extracts the data, usually populating it into the appropriate fields or tables within the application's database or data storage system. However, this feature is prone to potential errors, so we need to test it cautiously to ensure its accuracy and security.
Test Approach for CSV Import feature in Web Application
This main section explores some methodical approaches to testing the CSV import feature. Detailed examples in this section will present various scenarios for assessing quality characteristics such as correctness, completeness, appropriateness, usability, or security. Let's delve into each aspect to ensure that our CSV import feature is robust and reliable for our users!
1. File Format Compatibility
We need to validate the CSV import feature with various file formats and variations, including CSV files with different encodings, extensions, etc. Verify that the application accurately detects and handles these variations.
Example 1: Upload an unsupported file extension (e.g., .txt instead of .csv) to validate error handling
- Scenario: Suppose you have a web application with a CSV import feature for importing customer data, including fields for "Name", "Email", and "Phone Number".
- Example CSV File Formats:
- Purpose: To verify that the application detects the invalid file format and provides an appropriate error message to the user.
- Prevent action:
- Prepare Test Data:
- The 1st file, "customer_data.csv," is marked as valid because it has a supported CSV file extension.
- The 2nd file, "unsupported_data.txt," is marked as invalid because it has an unsupported file extension.
- Potential Scenarios:
- Prepare Test Data:
Scenarios | Potential issues | Impact |
User attempts to upload a text file with a .txt extension instead of a .csv file | System may let .txt files pass unchecked, leading to unpredictable issues later. | Users may face data processing issues or errors with the incorrectly imported file |
User attempts to upload a file with an unsupported extension, such as .xlsx (Excel file) or .xls (older Excel format) | The system may ignore unsupported formats, assuming all .csv files are importable | Users may unknowingly upload incompatible files, leading to data corruption or processing errors |
User renames a non-CSV file with a .csv extension and attempts to upload it | System may misidentify the modified file as a valid CSV based on its extension alone | Malicious users may exploit this loophole to upload unauthorized files, risking system security or integrity |
- Risk Mitigation:
- Character Encoding Standardization: Ensure consistent use of character encoding (e.g., UTF-8) across the application to handle special characters effectively.
- Data Validation: Implement robust data validation mechanisms to validate and sanitize user input, preventing validation errors triggered by special characters.
- Database Configuration: Configure the database to support Unicode characters and collations for correct storage and retrieval of special characters.
- Unit Testing: Write comprehensive unit tests to validate CSV parsing and import functions, including scenarios involving special characters, to cover edge cases and boundary conditions.
- User Guidance: Provide clear instructions to users on proper CSV file formatting, including handling of special characters, to prevent issues during import.
- Error Logging and Monitoring: Implement logging mechanisms to capture errors or exceptions related to special characters during CSV file import. Regularly monitor logs to promptly address any issues that arise.
- Risk Mitigation:
Example 2: Upload a CSV file with different delimiters (e.g., comma, semi colon) to ensure proper parsing
- Scenario: Suppose you have a web application with a CSV import feature for importing product data, including fields for "Product Name", "Price", and "Category".
- Example CSV File Formats:
- Semi colon (;)
- Comma (,)
- Purpose: To verify that the application correctly parses and imports the data from the CSV file, recognizing comma delimiters or supporting different delimiter configurations.
- Prevent actions:
- Prepare Test Data:
- The 1st file, "customer_data_comma.csv," is marked as valid because it uses the expected comma delimiter.
- The 2nd file, "customer_data_semicolon.csv," is also marked as valid because it uses a semicolon delimiter, which is properly handled by the application.
- The 3rd file, "customer_data_wrongdelimiter.csv" is marked as invalid because it uses an incorrect delimiter that the application does not support.
- Potential Scenarios:
- Prepare Test Data:
Scenarios | Potential issues | Impact |
User uploads a CSV file where fields are separated by commas | System may mishandle commas within data fields, resulting in parsing errors | Comma-containing data fields may split incorrectly, causing inconsistency or loss |
User uploads a CSV file where fields are separated by semi-colons | System may default to assuming comma delimiters, failing to recognize semi-colons as expected | CSV file parsing may result in misaligned columns or failed imports |
User uploads a CSV file with a custom delimiter, such as the pipe symbol (|), instead of the standard comma or semi-colon | System may mishandle custom delimiters, leading to parsing errors | Data fields may split or merge inaccurately, causing integrity issues or failed imports |
- Risk Mitigation:
- Automated Testing: Use automated tests to check if the app can handle different delimiters in CSV files. This helps find any issues early on.
- Input Validation: Check what people type in and show clear messages if they use a delimiter that the app doesn't support or if they use it wrong.
- User Guidance: Tell users what delimiter to use when they're uploading CSV files. Give them tips on how to use different delimiters so they can do it right.
- Error Logging and Monitoring: Keep track of any mistakes that happen when the app tries to read CSV files. This helps catch any problems with delimiters quickly, so they can be fixed fast.
- Risk Mitigation:
2. Data Validation
We need to validate the import feature with CSV files containing both valid and invalid data. Check for proper validation of data types, field lengths, numeric ranges, date formats, and compliance with any business rules or constraints. Ensure that error messages are displayed appropriately for invalid data.
Example 1: Upload a csv file contain special characters such as accents or non-ASCII characters
- Scenario: Suppose you have a web application with a CSV import feature for importing product data, including fields for "Product Name", "Price", and "Description".
- Example CSV File Formats:
- Purpose: To verify that the application detects the invalid format and provides an appropriate error message to the user.
- Prevent action:
- Prepare Test Data:
- The 1st file, "valid.csv," is marked as valid because it uses valid input format like this
- Prepare Test Data:
- The 2nd file, "invalid.csv," is marked as invalid because it uses input value contains special characters like this
- Potential Scenarios:
- Potential Scenarios:
Scenarios | Potential issues | Impact |
User uploads a CSV file that includes accented characters in data fields | System may mishandle accented characters, causing encoding errors or data corruption | Accented characters may be replaced incorrectly, impacting data integrity and usability |
User uploads a CSV file that includes non-ASCII characters, such as special symbols or characters from different languages (e.g. Japanese, Chinese, Thai) | System may struggle with non-ASCII characters, leading to garbled text or display problems | Non-ASCII characters may display incorrectly or be omitted from imported data, causing confusion or loss |
User uploads a CSV file containing a mix of accented characters, non-ASCII characters, and other special symbols | System may struggle with special characters in the CSV file, causing parsing errors or display issues | Special characters, like accents and non-ASCII symbols, may vanish during import, affecting data accuracy and usability |
- Risk Mitigation:
- Character Encoding Standardization: Ensure consistent use of character encoding (like UTF-8) across the application and database to handle special characters effectively.
Data Validation: Implement robust validation mechanisms to sanitize user input, ensuring special characters are handled correctly and don't cause validation errors.
Database Configuration: Configure the database to support Unicode characters and collations, enabling proper storage and retrieval of special characters.
Unit Testing: Write unit tests to validate CSV parsing and import functions, covering special characters and various edge cases.
User Guidance: Provide instructions to guide users on formatting CSV files correctly, including special characters, to prevent import issues.
Error Logging and Monitoring: Implement logging mechanisms to capture errors related to special characters during CSV file import and regularly monitor logs to address issues promptly.
- Risk Mitigation:
Example 2: Upload an empty CSV file or a CSV file without headers
- Scenario: Suppose you have a web application with a CSV import feature for importing employee data, including fields for "Name", "Email", and "Department".
- Example CSV File Formats:
- Purpose: To verify that the application correctly parses and imports the data from the CSV file, recognizing comma delimiters or supporting different delimiter configurations.
- Prevent action:
- Prepare Test Data:
- The 1st file, "empty_customer_data.csv," is marked as invalid because it is empty and does not contain any data to import.
- The 2nd file, "no_header_customer_data.csv," is also marked as invalid because it lacks headers, making it impossible to interpret the data correctly.
- Potential Scenarios:
- Prepare Test Data:
Scenarios | Potential issues | Impact |
User attempts to upload a CSV file that contains no data (an empty file) | System may mishandle empty CSV files, causing unexpected errors during import | Users may encounter unclear error messages or system crashes importing empty files, causing frustration |
User uploads CSV without headers, missing column names. | System may have trouble with CSV files lacking headers, causing data misalignment or wrong assumptions about column names | CSV files without headers may lead to incorrect parsing, causing data integrity issues or processing errors downstream |
User uploads CSV with mixed header rows, causing data inconsistency | System may handle mixed-content CSV files inconsistently, leading to parsing errors or data inconsistency | Users may face data discrepancies or errors when importing CSV files with mixed header and non-header rows, impacting data accuracy or causing processing errors |
- Risk Mitigation:
- Client-Side Validation: Implement validation on the client-side to detect empty CSV files or files without headers before initiating the upload. Provide immediate feedback to users if they attempt to upload invalid files.
- Server-Side Validation: Perform validation on the server-side to confirm the presence of data and headers in the uploaded CSV file before processing the import request. Reject the request and notify the user if the file is empty or lacks headers.
- User Guidance: Provide clear instructions to users on preparing CSV files with proper headers and valid data before attempting to upload them, ensuring compliance with the application's requirements.
- Error Handling: Develop robust error handling mechanisms to gracefully manage scenarios where users upload invalid CSV files. Display informative error messages to guide users on rectifying the issue and proceeding with the import process smoothly.
- Logging and Monitoring: Implement logging mechanisms to capture errors or exceptions related to empty CSV files or files without headers during the import process. Regularly monitor logs to promptly identify and address any issues that arise.
- Risk Mitigation:
3. Data Integrity
We need to validate the accuracy, consistency, and reliability of data from the imported CSV file during the import process and its integration into the application's database. This validation safeguards against data discrepancies or errors that could compromise the integrity of the system.
Example 1: Upload a CSV file contains duplicate records
- Scenario: Suppose you have a web application with a CSV import feature for importing customer data, including fields for "Name", "Email", and "Phone Number".
- Example CSV File Formats:
- Purpose: To verify that the application detects and handles duplication during the import process, ensuring only unique records are inserted into the database. Additionally, it aims to prevent data duplication and provide informative messages if necessary.
- Prevent action:
- Preparing Test Data:
- The 1st file, "noduplication.csv," is marked as valid because it uses unique input value like this
- Preparing Test Data:
- The 2nd file, "duplicated.csv," is marked as invalid because it uses duplicated input value like this
- Potential Scenarios:
- Potential Scenarios:
Scenarios | Potential issues | Impact |
User uploads a CSV file that contains rows with identical data | System may not properly identify duplicate records, leading to the unintentional import of duplicate data | Duplicate records may lead to data redundancy, impacting accuracy and analysis |
User uploads CSV with duplicate primary keys, violating uniqueness | System may fail to enforce primary key constraints, allowing the import of CSV files containing duplicate primary key values | Duplicate primary keys may cause database integrity issues or errors in related data queries |
User uploads CSV with potential duplicates, requiring system deduplication | System may incorrectly identify or handle potential duplicate records, leading to incorrect data deduplication or missed duplicate entries | Incorrect deduplication may remove valid data or retain duplicates, impacting data quality and analysis |
- Risk Mitigation:
- Data Integrity: Detect and handle duplicate records during import to maintain data integrity.
- Feedback: Provide clear feedback to users about duplicate records and system actions.
- Performance Optimization: Optimize import process to efficiently handle duplicates, especially for large datasets. Consider batch processing or asynchronous handling to prevent performance degradation.
- Risk Mitigation:
Example 2: Upload a CSV file where the validity of data in one field depends on the value of another field (e.g., start date cannot be after end date)
- Scenario: Suppose you have a web application with a CSV import feature for importing employee data, including fields for "Employee ID", "Start date", and "End date".
- Example CSV File Formats:
- Purpose: To verify that the application correctly handles dependencies between fields and provides informative error messages if inconsistencies are detected.
- Prevent action:
- Preparing Test Data:
- The 1st file, "validdate.csv," is marked as valid because its input value follows the date rules (end date cannot be before start date) value like this
- Preparing Test Data:
- The 2nd file, "invaliddate.csv," is marked as invalid because its input value does not follow the date rules (end date cannot be before start date) value like this
- Potential Scenarios:
- Potential Scenarios:
Scenarios | Potential issues | Impact |
User uploads CSV with start date after end date, violating validity | System may miss date range inconsistencies, importing records with conflicting dates | Inconsistent date ranges may cause data interpretation errors, affecting analysis accuracy |
User uploads CSV with incomplete or invalid date formats | System may not validate date formats, allowing incomplete or invalid dates to be imported | Incorrect or missing date values may cause data inconsistencies, impacting integrity and usability |
User uploads CSV with date values beyond set future date limit | System may not enforce the maximum date range, allowing records with dates beyond it to be imported | Dates beyond the maximum range may lead to inaccurate representation or calculation errors |
- Risk Mitigation:
- Data Validation: Implement checks during import to ensure start date precedes or equals end date. Provide clear error messages for violations.
- Unit Testing: Write tests covering dependencies between fields. Regularly run these tests to catch and rectify date validation bugs.
- User Guidance: Offer clear instructions for CSV formatting, especially regarding date ranges, through tooltips or documentation.
- Error Logging and Monitoring: Implement logging to catch import errors like invalid date ranges. Regularly monitor logs to maintain data integrity and system reliability.
- Risk Mitigation:
4. User Interface Testing
We need to evaluate the user interface elements related to the import feature, including file upload functionality, data mapping interfaces, and feedback messages. Verify that the UI offers clear instructions, error handling, and progress indicators throughout the import process.
Example: Check the CSV file upload button or drag-and-drop area and review all provided error messages from the user story.
Purpose: To verify that the button and drag & drop area are functional and responsive, indicating successful file selection. Additionally, confirm that clear and informative error messages are displayed if users encounter upload failures, guiding them on resolution steps.
- Prevent action:
- Preparing Test Data:
- Prepare a valid CSV file: customer_data.csv
- Prepare an invalid file type: invalid_file.txt
- Prepare an incorrect format file: incorrect_format.csv (without headers)
- Potential Scenarios:
Scenarios | Potential issues | Impact |
User attempts to upload a CSV file using the designated file upload button | File upload button may not respond when clicked, preventing users from selecting a CSV file for upload | Users can't start upload, causing frustration and feature unavailability |
User attempts to upload a CSV file by dragging and dropping it onto the designated drop area | The drag-and-drop area may not accept dropped files, failing to trigger the upload process | Users can't drag-and-drop upload, limiting options and causing confusion |
User interacts with the CSV file upload button or drag-and-drop area and intentionally triggers various error conditions | Error messages may be generic or unclear, failing to effectively communicate the nature of the error to users | Users may struggle with failed uploads, leading to frustration and reduced usability |
- Risk Mitigation:
Input Validation: Display clear error messages to users when invalid files are detected, guiding them on how to correct the issue.
File Type Validation: Provide informative error messages if unsupported file types are uploaded, informing users of the accepted file formats.
User Guidance: Offer documentation or tooltips explaining the supported file formats and requirements for CSV file upload.
- Risk Mitigation:
5. Performance Testing
Evaluate the performance of the CSV import feature by testing it with files of different sizes, ranging from small to large. Measure the time taken to import each file size and ensure that the application remains responsive throughout the import process.
Example: Upload various csv file size from small to large
Purpose: To verify the upload time for CSV files of different sizes, ensuring that the application remains responsive during the upload process and that there are no significant delays or performance issues.
- Prevent action:
- Preparing Test Data: create CSV files of varying sizes (small, medium, large) containing sample data for testing.
- Potential Scenarios:
- Preparing Test Data: create CSV files of varying sizes (small, medium, large) containing sample data for testing.
Scenarios | Potential issues | Impact |
User attempts to upload a small CSV file with a minimal number of rows and columns | System may mishandle small CSVs, leading to parsing errors or inaccurate imports | Users may encounter data issues with small CSV imports, affecting accuracy |
User attempts to upload a medium-sized CSV file with a moderate number of rows and columns | System may slow down with medium CSVs, causing delays or import timeouts | Users may get frustrated with medium CSV imports if the system slows down |
User attempts to upload a large CSV file with a substantial number of rows and columns | System may struggle with large CSVs, causing crashes or timeouts | Large CSV imports may cause severe performance issues and disrupt user workflow |
- Risk Mitigation:
- Performance Optimization: Optimize the CSV import feature to handle large files efficiently, such as implementing batch processing or asynchronous handling.
- Resource Monitoring: Monitor server resources (e.g., CPU, memory) during CSV file uploads to detect and address any resource consumption issues promptly.
- Error Handling: Implement error handling mechanisms to gracefully manage timeouts or server crashes during CSV file uploads, providing informative feedback to users.
- Risk Mitigation:
6. Concurrency and Scalability
Assess the application's capability to manage concurrent import requests from multiple users. Evaluate the system's scalability under increasing load and ensure data integrity is preserved during high concurrency scenarios.
Example: Simulate multiple users upload CSV files to import concurrently
Purpose: To verify the application's ability to manage multiple concurrent import requests without encountering slowdowns or errors.
- Prevent action:
- Preparing Test Data: create multiple CSV files with different data sets for concurrent upload testing.
- Potential Scenarios:
- Potential Scenarios:
Scenarios
Potential issues
Impact
Multiple users upload CSV files simultaneously Concurrent uploads may crash the system, disrupting all uploads
Users may experience delays in processing their files Multiple users upload CSV files containing duplicate or conflicting data Conflicting data may cause incorrect merges, leading to data errors Users may see incorrect data in their uploads Multiple users upload large CSV files simultaneously System performance may slow for all Long upload times may frustrate users - Risk Mitigation:
- Concurrency Control Mechanisms: Implement locking mechanisms or transaction management to ensure data integrity during concurrent uploads.
- Performance Optimization: Optimize the CSV import feature to handle concurrent user activity efficiently and prevent performance degradation.
- Load Testing: Conduct thorough load testing to identify potential issues related to concurrency and performance before deployment.
- Risk Mitigation:
7. Security Testing
Ensure that the CSV import feature doesn't introduce security vulnerabilities, such as injection attacks or file system manipulation. Test for security measures like input validation, authentication, and authorization to prevent unauthorized access to sensitive data.
Example 1: Upload a CSV file contains malicious content or scripts (e.g., formula injection such as "=SUM(1+1)"; script injection such as "<script>alert('XSS Attack!');</script>"; hyperlink injection such as "=HYPERLINK("http://malicious.com")"; SQL injection such as "'; DROP TABLE users;--" )
Purpose: To verify that the application rejects files with potentially harmful content and provides appropriate error messages to the user.
- Prevent action:
- Preparing Test Data: create a CSV file containing various types of malicious content or scripts for testing injection vulnerabilities.
- Potential Scenarios:
- Potential Scenarios:
Scenarios
Potential issues
Impact
Upload a CSV file containing formula injections Formula injections may lead to wrong data processing, impacting data accuracy Corrupt imported data, leading to potential loss of important information or system malfunction Upload a CSV file containing script injections (XSS attacks) Script injections may cause rendering issues or unexpected behavior in the UI, impacting usability and functionality Script injections disrupt user experience with pop-ups, redirects, or other unwanted actions, causing frustration and distrust in the app Upload a CSV file containing SQL injection attempts SQL injection risks data corruption, loss, or unauthorized access to confidential database information Disrupts app functionality, causing downtime, data loss, or service interruptions, hindering users' essential tasks - Risk Mitigation:
- Input Validation: Implement robust input validation mechanisms to filter out and sanitize input data, preventing injection attacks.
- Parameterized Queries: Use parameterized queries or prepared statements to prevent SQL injection attacks.
- Content Security Policy (CSP): Implement a strict Content Security Policy to mitigate the risk of script injection attacks.
- Regular Security Audits: Conduct regular security audits and penetration testing to identify and address potential vulnerabilities in the CSV import feature.
- Risk Mitigation:
Example 2: Login with various user roles and permissions to test the availability of CSV import feature
Purpose: To ensure that the application grants access only to authorized users. Additionally, confirm that sensitive data undergoes encryption during both transmission and storage, with access controls implemented to restrict entry to authorized users exclusively.
- Prevent action:
- Preparing Test Data: create user accounts with different roles (e.g., Admin, Manager, Regular User, View Only) to simulate various access scenarios.
- Potential Scenarios:
- Preparing Test Data: create user accounts with different roles (e.g., Admin, Manager, Regular User, View Only) to simulate various access scenarios.
Scenarios | Potential issues | Impact |
Login with an admin user role to test CSV import feature availability and functionality | Admins may lack CSV import access due to permission misconfiguration | Inability to perform tasks frustrates and reduces productivity for admins |
Login with a regular user role to test CSV import feature availability and restrictions | System may fail to notify users of CSV import feature access restrictions | Users frustrated or waste time on unusable features |
Login with an unauthorized user role to test CSV import feature access restrictions | Lack of access controls may let unauthorized users access CSV import | Unauthorized access to CSV import can cause data breaches or manipulation |
- Risk Mitigation:
- Role-Based Access Control (RBAC): Implement RBAC mechanisms to ensure that only users with appropriate roles and permissions can access the CSV import feature.
- Access Control Testing: Conduct regular access control testing to verify that user roles and permissions are properly enforced.
- User Role Documentation: Clearly document the roles and permissions associated with each user role to avoid confusion and misconfiguration.
- Risk Mitigation:
Conclusion
In our journey through testing the CSV import feature, I've learned a lot about making it strong and reliable. From the start, when I faced challenges, to the detailed testing methods I explored, I've gathered important lessons to improve the product quality.
Every part of testing, like checking file formats and making sure the system is secure, helps us catch problems and make things better. By trying out different situations, I've found ways to make the CSV import feature work smoothly and safely.
This tech blog is here to share what I've learned with you. I hope it helps you make your CSV import feature the best it can be. With these tips, you'll be well on your way to giving your users a great experience when they use your product's CSV import feature.
![MFV Tech Talk #3 - Microservice Architect | Hill](https://static-careers.moneyforward.vn/Blog/Hill-thumb%20(1).png)
![Khi QA Nữ Lên sóng MFV](https://static-careers.moneyforward.vn/Blog/QA.png)
Khi QA Nữ Lên sóng MFV
![Playwright tips and tricks](https://static-careers.moneyforward.vn/Screen%20Shot%202024-02-02%20at%2016.13.53.png)