2

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?

  • That would be one of the primary use cases of Perl. – Peter Sep 5 '14 at 15:27
  • I had not considered Perl previously and I am shocked since it is one of my favorite scripting languages and I am always wondering why I don't use it. Sadly though I really need to do it in C# if I possibly can since that is what would be maintainable by others. – Paul Muir Sep 8 '14 at 12:48
  • I'm sure C# also has good parsers for XML and CSV – Rsf Sep 8 '14 at 13:22
2

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.

1

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!

  • 2
    loading text files (XML and CSV are basically text files) into a DB would slow the test to a halt – Rsf Sep 8 '14 at 9:33
  • 1
    you have features like bcp which can be leveraged to load csv huge files. This is bulk data import feature. Programming approach you need to take care of data type comparison's. Both approach has pros / cons. – Siva Sep 8 '14 at 12:01
  • 1
    I have attempted with this approach but part of the issue is that it is approximately 1000 different data sources going to 200 different file generators and attempting to compose objects, schemas and such for every single item has been very time consuming. The DB inserts and selects though have actually been doing very well with performance (I am using Stored Procedures with table objects to optimize). – Paul Muir Sep 8 '14 at 12:38
  • Couple of more options, Parallel Data Inserts, Identify the priority cases. Instead of performing data validations. Do a blind loading to a temp table of all tables. From the temp table choose the columns. I never do data validation while loading. Instead when done on a temp table its much faster. Probably if we discuss in details we can arrive at options. Please drop a note we can check other alternatives. – Siva Sep 8 '14 at 13:17
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.