Data Testing for Massive CSV/XML files

Question

Some background:

I am automating for continuous deployment a massive amount of massive files that are in different data structures that gather different data. I know this is vague but sadly, that is the best I can really explain it.

One of the primary goals of the automation is to verify that the data is as accurate as possible. There is approximately 200 different Data Structures all containing different pieces and parts of information designed with different styles, XML, CSV, Pipe Delimited etc etc.

I was wondering if anyone had any experience with such a project, and if so, any tools or methods used in order to complete such a task.

In order to avoid generalizing and just looking for ideas:

Is there a tool that can be used that will assist in parsing multiple different types of data structures (listed above) and be able to associate them with data from another source, preferably on the fly?

Answer 1

using your favorite script language would probably be the best solution, Peter suggested Perl and I'll add Python to the list, both have excellent modules to parse and analyze CSVs and XMLs and a lot of capabilities to help you in your tasks.

Answer 2

Instead of testing with csv / xml level. You can load the data into a database and run queries and obtain accurate results

• Create a Results DB with Run_Id (ex-ResultMMDDYYY_1)
• Load the source and destination data into the DB
• Run Queries for each source / destination tables and create a results table
• Fetch the data from results table and publish results
• You can extract rows with data differences or refer it to the particular table / row

Comparing through code would be a overhead. 100% comparison for all fields would be difficult to begin with

• Validate priority cases
• Design Generic Schema

This approach would reduce data comparison / strings / date formats / data types if the schema and data loading is done correctly.

Hope it Helps!

Answer 3

My current solution:

Using an XmlStreamer I parse in some of the XML and verify that it is Xml. If it is, it processes the Xml file, uses the file name in order to receive instructions in how to build the comparison data.

If it is not Xml, it parses the file to look for delimiters, checking line by line until only one delimiter is contained on the line while keeping a total count of each delimiter. If after 50 lines the lines all have contained more than 1 of the delimiter it uses the counts of each line and the one with the most is determined to be the delimiter (currently comma, pipe and tab). Following that it again grabs the name of the file in order to look for instructions in order to build comparison data.

At the end I am going to have it log more to a database with the raw comparison data and results just for data collections efforts and potentially to use long term to expedite the process.

Answer 4

It really depends on the type of checks you want to perform.

If this is about checking each file individually, then for each format of the file you will find a corresponding query language:

• XML file can be queried with XPath or XQuery expressions.
• CSV files are tabular data so they can be queried with SQL without parsing them into RDB, e.g., with csvq command line tool.
• Pipe delimited files are actually tabular data, so they can be queried same way as CSV files.

Each programming language provides tons of query engines implementations for those query languagies. The advantage over implementing custom parsers is that you can focus in the queries instead of low-level technical details. Query engines vary in a way they execute queries: some are working on data loaded fully into memory or persisent database, other working on streams. The choice will be a trade-off between the size of files they can process, time to load/index data and query execution time.

If you need to correlate data from different files of different formats, i.e., perform query joins over two files (e.g. check if same data appear in both), then mapping both formats and schemata to the common model is a good option. For instance, definining SQL schema and parsing both CSV and XML files into this model. It might be hard to correlate data on the fly (i.e., using stream parsers), if you know that data in one file and matching data in another are living in different places.