Spreadsheet::Compare::Single - Module for comparing two spreadsheet datasets
use Spreadsheet::Compare::Single; my $single = Spreadsheet::Compare::Single->new(%args); my $result = $single->compare();
Spreadsheet::Compare::Single analyses differences between two similar record sets according to a defined configuration set.
All attributes return the object on setting and the current value if called without parameter.
$single->attr($value); my $value = $single->attr;
They will usually be set by Spreadsheet::Compare, after reading the values from a config file.
possible values: 0|1 default: 0
Try to match identical records even when a unique identity cannot be constructed. This can significantly increase compare times on large datasets.
Normally differences that are inside configured limits will still be counted as differences (only marked visually as low priority). Setting below_limit_is_equal to a true value will result in the record counted as equal.
Convert content that is treated as a numerical value to an actual numeric value (by simply adding 0). This is e.g. handy for having numerical data in Excel output instead of strings that look like numbers. This will not affect the optional 'All' report that can be created with the report_all_data option.
possible values: <string> default: '.'
Decimal separator for numerical values
possible values: <list of column names> default: []
Report the relative instead of the absolute difference if "report_diff_row" in Spreadsheet::Compare::Reporter is set to a true value.
Example (as YAML config):
diff_relative: [2,3,4]
or
diff_relative: - Price - Quantity
possible values: <string> default: ','
Digital grouping symbol for numerical values
possible values: <integer> default: 1000
When "is_sorted" is set, "fetch_size" determines the number of records fetched into memory at a time.
possible values: <integer> default: 0
When "is_sorted" is set, "fetch_limit" determines the number of fetches (of size "fetch_size") before the comparison stops. This is useful during setup with large datasets where you may have columns that are different for every row and that you better add to the ignore list. Just remember to unset this value once you are done.
possible values: <list of columns> default: empty list
Columns to ignore while comparing data. If "header" in Spreadsheet::Compare::Reader is set the column names have to be used. Else use the zero based column number.
Only compare numerical data. This skips comparisons where both sides are not considered to be numerical values. This depends on the setting for "decimal_separator" and "digital_grouping_symbol"
Assume data is sorted by identity. This is needed for fetching data in smaller batches (see "fetch_size") to use less memory.
possible values: <string> default: 'left'
Name for the input on the left side of the comparison. Used for reporting.
possible values: <number or key/value pairs> default: 0
Single value or one entry per column for specifying absolute tolorance intervals. Differences inside the tolerance interval will be counted and reported separately from differences outside of it. The default value of 0 means no tolerance limit, the value 'none' skips the limit check with the side effect that the deviation will not be considered in statistics output (column with highest absolute deviation). The special key '__default__' can be used to set a default for all (numerical) columns, and subsequently setting a different limit on selected columns.
limit_abs: 0.01 or limit_abs: __default__: 0.01 Price: 0.0001 Quantity: 1 Size: none
possible values: <number or keys/values> default: undef
Single value or one entry per column for specifying relative tolerance intervals (decimal value, not a percentage). Differences inside the tolerance interval will be counted and reported separately from differences outside of it. The default value of 0 means no tolerance limit, the value 'none' skips the limit check with the side effect that the deviation will not be considered in statistics output (column with highest relative deviation). The special key '__default__' can be used to set a default for all (numerical) columns, and subsequently setting a different limit on selected columns.
limit_rel: 0.01
limit_rel: __default__: 0.1 Price: 0.01 Quantity: 1 Size: none
This attribute cannot be set from a config file.
possible values: <list of exactly 2 Reader objects> default: []
The readers have to be two objects of Spreadsheet::Compare::Reader subclasses representing the left and the right side of the comparison.
Add an additional output stream containing all data from both sides of the comparison. This will slow down reporting on large datasets.
possible values: <string> default: 'right'
Name for the input on the right side of the comparison. Used for reporting.
possible values: <string> default: ''
A title for the comparison
my $single = Spreadsheet::Compare::Single->new(%attributes); my $single = Spreadsheet::Compare::Single->new(\%attributes);
my $single = Spreadsheet::Compare::Single->new ->title('Regression Test 1') ->readers([$r_left, $r_right]);
Construct a new Spreadsheet::Compare::Single object. All comparison attributes can be given to the constructor or set individualy via their chainable set methods.
Run all configured tests of the run configuration. Return a hashref with counters:
{ add => <number of additional records on the right>, diff => <number of found differences>, dup => <number of duplicate rows (maximum of left and right)>, left => <number of records on the left>, limit => <number of record with differences below set ste limits>, miss => <number of records missing on the right>, right => <number of records on the right>, same => <number of identical records>, }
Before running compare the readers have to be set.
Spreadsheet::Compare::Single is a Mojo::EventEmitter.
The reporting events correspond to the methods that are implemented by sublasses of Spreadsheet::Compare::Reporter. Spreadsheet::Compare will subscribe to the events and call the methods.
Spreadsheet::Compare::Single emits the following events
$single->on(add_stream => sub ($obj, $name) { say "new stream $name for ", $obj->title; });
Reporting event. Signaling that a new reporting stream should be created and will be later referenced for reporting data lines.
The possible stream names are 'Differences', 'Missing', 'Additional', 'Duplicates' and 'All'.
$single->on(after_fetch => sub ($obj) { say "next fetch for ", $obj->title; });
Emitted directly after a fetch from the readers.
require Data::Dumper; $single->on(counters => sub ($obj, $counters) { say "next fetch for ", $obj->title, ":", Dumper($counters); });
Emitted for every handled record. Don't rely on the numbers of calls, but on the content of the %$counters hash if you want to know how many lines where actually read from the readers. This can be used for progress reporting.
require Data::Dumper; $single->on(final_counters => sub ($obj, $counters) { say "next fetch for $title:", Dumper($counters); });
Emitted after completing a single comparison.
$single->on(mark_header => sub ($obj, 'Differences', $mask) { # mark columns });
Reporting event. Emitted after completing a single comparison with a mask describing which columns had differences (key:column_index, value:false/true)
$single->on(write_fmt_row => sub ($obj, 'Differences', $record) { # write record to stream; });
Reporting event. Write a formatted record to the 'Differences' output stream The record is an Spreadsheet::Compare::Record and will contain information about the differences found (see "limit_mask" in Spreadsheet::Compare::Record).
$single->on(write_header => sub ($obj, $stream) { # write header for stream; });
Reporting event. Write the header for an output stream.
$single->on(write_row => sub ($obj, $stream, $record) { # write record to stream; });
Reporting event. Write a default formatted record to an output stream. The record is an Spreadsheet::Compare::Record
To install Spreadsheet::Compare, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::Compare
CPAN shell
perl -MCPAN -e shell install Spreadsheet::Compare
For more information on module installation, please visit the detailed CPAN module installation guide.