csv » compareExtended(var,profile,expected,actual)

Description

This command provides a more comprehensive way of comparing CSV content.  For simple comparison, check out compare(expected,actual,failFast).

The main purpose of this command is to provide flexibility and insights into the comparison of 2 CSV content with the following strategies:

• Ability to compare 2 fields with different name, and possibly different position in the respective files
• Ability to skip certain fields, but still have them as part of output
• Ability to compare files with disparate sort order
• Ability to specify multiple columns as “identity” columns for fast comparisons
• Ability to generate different format of output, such as CSV and HTML
• Ability to control the fields to display as part of output
• Ability to create reusable configuration for comparison

This command takes 4 parameters:

• var - the variable to reference the comparison result (see below for possible data extraction)
• profile - the reference to the configuration of this comparison
• expected - the file or CSV content that represents the expected content
• actual - the file or CSV content that represents the actual content

It is important to note that the expected is treated as the “baseline” - meaning, the subject to compare against.  In comparison, the actual is the “variant”, so as speak. 

The profile refers to a set of data variables that are used as configuration for a given comparison.  This profile may be reused for multiple comparisons, thus could be a time saver.  The general structure of the comparison configuration is as follows:

[profile].compareExt.configuration_key | value

Here are the list of possible configuration (assuming the profile is MyFiles):

configuration	value	notes
MyFiles.compareExt.expected.identity	"ID" column name(s) of the expected file.	REQUIRED The "ID" columns are used in 2 ways: Fast matching - if the "ID" columns of an expected row is not matching that of the actual row, then the comparison for that row is considered as FAILED and Nexial will move to compare the next row. Pre-Comparison Sort - Nexial uses the content of the "ID" column(s) to sort the CSV content prior to comparison. That way 2 CSV files with disparate sort order can be compared.
MyFiles.compareExt.actual.identity	"ID" column name(s) of the actual file.	REQUIRED see above.
MyFiles.compareExt.identity.delim	Default: ^	The delimiter to use when multiple identity columns are specified. Default is ^.
MyFiles.compareExt.match.[EXPECTED COLUMN]	The corresponding column name of the actual file.	Specifying the columns to match between the expected and actual file. For example: One can specify all the columns to ensure proper matching. If the MyFiles.compareExt.match.[FIELD]  configuration is used then those not specified will be omitted for comparison. If both files contains the exact same headers, such configuration can be omitted entirely.
MyFiles.compareExt.ignore	The column(s) to ignore for comparison.	Specifying the column(s) to ignore during field-by-field matching. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file. If a "ignore" column is mapped to a different column on the actual field (via the ...compareExt.match.... configuration), then the mapped column of the actual field will be ignored as well. If the "ignore" column is not mapped, then Nexial assumes that the same column on both the expected and actual files. Note that ignoring one or more columns for matching does not preclude them to be used as part of the comparison result. This means that one can specify a column to be ignored for comparison, but use the same column in the comparison report (see compareExtended result below) via the [profile].compareExt.output.display configuration (see below).
MyFiles.compareExt.matchAsNumber	The column(s) to compare number.	Specifying the column(s) that should be compared as numbers. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file. Note that if the value in the specified fields - either the ones in the expected file or the corresponding ones in the actual files - cannot be parsed as number, then the default text-based comparison will be used instead.
MyFiles.compareExt.matchCaseInsensitive	The column(s) to compare case-insensitively.	Specifying the column(s) that should be compared as text case-insensitively. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file. Note that one may combine this configuration with ...compareExt.matchAutoTrim so that text comparison can be conducted after the field values are trimmed AND compared case-insensitively.
MyFiles.compareExt.matchAutoTrim	The column(s) to trim before comparison.	Specifying the column(s) that should be trimmed (both beginning and end of field value) before comparison. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file. Note that one may combine this configuration with ...compareExt.matchCaseInsensitive so that text comparison can be conducted after the field values are trimmed AND compared case-insensitively.
MyFiles.compareExt.matchAsOrderedList	The column(s) to be compared as order-significant list (aka array)	Specifying the column(s) that should be compared as order-significant list. "Order-significant" means that the order of the list items must be observed and matched between the corresponding EXPECTED and ACTUAL columns. This means that the corresponding column content for both EXPECTED and ACTUAL files must match both in terms of list items and their respective order. Further customization is possible via ...compareExt.matchAutoTrim where one can request for each list item to be trimmed prior to comparison so that Apple,Orange,Banana ... would be considered the same as Apple, Orange , Banana Also, one can invoke case insensitive comparison over "list" columns via ...compareExt.matchCaseInsensitive. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file.
MyFiles.compareExt.matchAsUnorderedList	The column(s) to be compared as order-insignificant list (aka array)	Specifying the column(s) that should be compared as order-insignificant list. "Order-insignificant" means that the order of the list items is not considered as part of the comparison. In such case, only the presence of the list items is considered for comparision. This means that Apple,Orange,Banana ... would be considered the same as Apple,Banana,Orange Further customization is possible via ...compareExt.matchAutoTrim where one can request for each list item to be trimmed prior to comparison so that Apple,Orange,Banana ... would be considered the same as Apple, Banana , Orange Also, one can invoke case insensitive comparison over "list" columns via ...compareExt.matchCaseInsensitive. Multiple columns are separated by ${nexial.textDelim}. The columns are based on those specified in the expected file.
MyFiles.compareExt.list.delim	The list item delimiter to use when comparing between "list" columns	Note that this configuration is only applicable for the columns targeted for ...compareExt.matchAsOrderedList or ...compareExt.matchAsUnorderedList. The delimiter specified here will be used to parse the "list" columns prior to comparison.
MyFiles.compareExt.output.display	The column(s) - the expected file's perspective - to display as part of output.	REQUIRED Use this configuration to include or omit certain fields. Not all fields are used for matching. If the  MyFiles.compareExt.match.[FIELD] configuration is used then those not specified will be omitted for comparison. However such omitted fields can still be used for output purpose - via this configuration. Furthermore, it is possible to alter the order of these columns in the output.
MyFiles.compareExt.output.MISMATCHED	Field name of the "mismatched" field.	This command provides an additional field to specify where the mismatched is found. This configuration specify what such field should be called. By default it is MISMATCHED FIELD.
MyFiles.compareExt.output.EXPECTED	Field name of the "mismatched" value from the expected file.	The field name to use for displaying the value from the expected file for the "mismatched" field (see above).
MyFiles.compareExt.output.ACTUAL	Field name of the "mismatched" value from the actual file.	The field name to use for displaying the value from the actual file for the "mismatched" field (see above).
MyFiles.compareExt.expected.readAsIs	true to read the expected as is; false if otherwise.	Determine if the expected should be read as is without any token replacement, end-of-line or character set conversion. This has direct impact to memory footprint (improvement, that is) and is the preferred choice if the expected content does not contain any data variables (i.e. ${...}). The default value is false.
MyFiles.compareExt.actual.readAsIs	true to read the actual as is; false if otherwise.	Determine if the actual should be read as is without any token replacement, end-of-line or character set conversion. This has direct impact to memory footprint (improvement, that is) and is the preferred choice if the actual content does not contain any data variables (i.e. ${...}). The default value is false.

Here’s an example of the comparison configuration (in this case, the profile is AppOutput):

At times one might be working with a unusually long (or wide) CSV file, where the number of expected columns are longer than the default “max” of 512, or column width longer than the default “max” of 4096 character. In such case, one may want to set the following System variables to indicate the need thereof:

1. nexial.csv.maxColumns: configure the maximum number of columns to expect. The default is 512.
2. nexial.csv.maxColumnWidth: configure the maximum number of character per column. The default is 4096 (characters).

---

compareExtended result

For output, there are various data elements that are available.  Below is a depiction of what one can retrieve from the comparison result (referenced by the specified var variable):

For example, assuming that the var is specified as result:

• To retrieve the number of mismatched rows - ${result}.failCount

• To find the rate of matches - [NUMBER(${result}.successRate) -> multiple(100) roundTo(00.00)]%
  • Note that we are using Nexial Expression in the example above to convert a rate of 0 to 1 to a percentage value.

• To list the identities of the mismatched records - ${result}.failIdentities

• To generate a report, in CSV format, of the mismatched records - ${result}.reportAsCSV
  • Note that MISMATCHED FIELD is controlled via the profile.compareExt.output.MISMATCHED configuration (see above).
  • Note that FROM_DATABASE represents the mismatched field from the expected file, and is controlled via the profile.compareExt.output.EXPECTED configuration (see above).
  • Note that FROM_EXTERNAL represents the mismatched field from the actual file, and is controlled via the profile.compareExt.output.ACTUAL configuration (see above).

• To generate a comparison report in HTML format, use ${result}.reportAsHTML
  
  

  The same HTML can be better rendered via external stylesheet.  The table DOM has a style class of  compare-extended-result-table like so:

  <table class="compare-extended-result-table">
  <thead>
    <tr>
      <th>KEY_COLUMN</th>
      <th>SECTION_ID</th>
      ... ...
    </tr>
  </thead>
  <tbody>
    <tr>
      ... ... ...
  </tr>
  </tbody>
  </table>
  

  As such, one can include a stylesheet to create a more appealing presentation for the same HTML report. For example, with the following stylesheet:

  table.compare-extended-result-table {
      font-size:     10pt;
      color:         #333;
      font-family:   monospace;
      padding:       5px;
      border:        1px solid #888;
      border-radius: 5px;
      box-shadow:    5px 1px 10px #555;
  }
    
  table.compare-extended-result-table thead tr th {
      font-weight: bold;
      font-family: sans-serif;
      text-align:  left;
      text-shadow: 1px 1px 3px #888;
      color:       #358;
      padding:     0 5px;
  }
    
  table.compare-extended-result-table tbody tr td {
      border-bottom: 1px solid #ddd;
      padding:       2px 5px;
      margin-right:  2px;
  }
  

  One can possibly render the same HTML as:
  

  The above stylesheet is available here - https://nexiality.github.io/documentation/assets/report/csv-compareExtended-report.css One can embedded this stylesheet (or another one) with the generated HTML comparison report like this:

  <html>
  <head>
  <link rel="stylesheet" href="https://nexiality.github.io/documentation/assets/report/csv-compareExtended-report.css"/>
  <title>CompareExtended Report - powered by Nexial Automation</title>
  <style>
  /* add additional stylesheet here, as per your liking */
  body { font-family: Calibri, serif; font-size: 10pt; background-color: #fff; }
  </style>
  </head>
  <body>
  ... ...
    *** INSERT THE GENERATED HTML COMPARISION REPORT HERE ***
  ... ...
  </body>
  </html>
  

Note: to generate the HTML (like the one above) dynamically, one can consider using the io » writeFile(file,content,append) command. By setting append as true, the HTML content can be “build up” over multiple commands.

Parameters

see above for parameter details

Example

Script:

Data file:

Output:

See Also

• compare(expected,actual,failFast)