How can I validate CSV exports from my application?

Question

My application has a daily task which exports certain activities to CSV files. In the ideal world, admins would check those files daily to make sure they are sane and the application is behaving as expected. In the real world, that won't (always) happen, so I'd like to create some sort of automation framework where (ideally) business users can build validation rules to be applied against the CSV.

Example:

Scenario "Participants can only create messages with their own ID":
For file 'Messages_{Date}':
    Assert column 'User role' matches one of ['Admin', 'Participant']
    If 'User role' = 'Participant'
        Assert column 'User id' matches JSON.parse(message).user

I wonder if a BDD tool like Jasmine can help with making these test cases clear and reporting any issues to both developers and business (power-)users, and also perhaps handle some things like "This report was broken already yesterday so don't send a new email today".

Am I approaching this wrong? Is this kind of data-level validation a bad idea? What tools do people use for this kind of thing?

Answer 1

Data Validation is a very important part of any testing strategy for an application that is dependent on data. There are two things you should do:

1. Validate data in your test environment
   1. You will have to have a way to generate real world scenarios for your application, hopefully your functional testing will already have this covered.
   2. Based on the known data set generated by your testing, create data validation tests that make sense using your technology of choice. You need to test the CSV since it is a specific deliverable of the application, but you may also want to test data in the database.
2. Create something to inform your business users
   1. I would lean towards building reports using something like SSRS or Cognos than providing test case results.

I try to avoid testing data directly in production, and instead try to test any ETL processes thoroughly enough in a test environment to be confident that there will not be any data issues. This can be quite a challenge, depending on your application's design.

Answer 2

Two observations that might lead to a different solution:

1. "Certain Activities" sounds like a simple log file
2. CSV files are pretty much XLS spreadsheets

My suggestion: create a macro that grabs the file, creates a range, and then loops through each row in the 'User Role' column to verify the output. On a "failure" the system can send an email to an Admin.