Data::...eReader(3pm)

📦
version 0.021 · 3 versions
, in debian trixie, ubuntu resolute, ubuntu stonking

Data::TableReader 💖

okay bestie, this is the manual page for Data::TableReader — yes it's one of those User Contributed Perl Documentation (3pm) situations, and yes it's about yanking actual usable records out of chaotic tabular data. it's giving spreadsheet chaos → clean data pipeline. iykyk 🌸

✨ NAME ✨

Data::TableReader — Extract records from "dirty" tabular data sources 🧚

⭐ VERSION ⭐

version 0.021 💅

🦋 SYNOPSIS 🦋

here's the whole vibe in one shot: find the row in the Excel file that's holding the headers "address", "city", "state", "zip" — in any order, we're not picky — and then convert every row under that one into a hashref of those fields. slay. 🔥

# Find a row in the Excel file containing the headers
#   "address", "city", "state", "zip" (in any order)
# and then convert each row under that into a hashref of those fields.

my $records= Data::TableReader>new(
    input => 'path/to/file.xlsx',
    fields => [qw( address city state zip )],
  )
  ->iterator->all;

but bestie, there are so many options to choose from... look at this menu 👑

my $tr= Data::TableReader->new(
  # path or file handle
  # let it auto-detect the format (but can override that if we need)
  input => 'path/to/file.csv',

  # We want these fields to exist in the file (identified by headers)
  fields => [
    { name => 'address', header => qr/street|address/i },
    'city',
    'state',
    # can validate with Type::Tiny classes
    { name => 'zip', header => qr/zip\b|postal/i, type => US_Zipcode },
  ],

  # Our data provider is horrible; just ignore any nonsense we encounter
  on_blank_row => 'next',
  on_validation_error => 'next',

  # Capture warnings and show to user who uploaded file
  log => \(my @messages)
);

my $records= $tr->iterator->all;
...
$http_response->body( encode_json({ messages => \@messages }) );

💜 DESCRIPTION 💜

so basically this module exists to take "loose" or "dirty" tabular data sources — think Excel, CSV, TSV, or HTML 🌈 — the kind that may have been edited by non-technical humans 🙈, and extract the data out into sanitized records. and while it's doing that, it's also verifying that the data file contains roughly the schema you were expecting. two jobs, both ate. 💅

it's primarily intended for making automated imports of data from non-automated or unstable sources, and for providing human-readable feedback about the validity of the data file. so like, your users get real words instead of a stack trace. iconic behavior fr fr. 🫶

more of this page is coming, keep scrolling bestie 👀 🎀

✨ ATTRIBUTES ✨

okay bestie, buckle up, this is the good part — all the knobs and dials you can twist 💅

💖 input

So this one can be literally so many things: a file name, a Path::Class instance, a file handle, an arrayref, or a Spreadsheet::ParseExcel::Worksheet object. Iconic range, we love flexibility 🦋

If you hand it a file handle, heads up: it must be seekable so the module can auto-detect the file format. Or you can just specify the decoder directly and skip auto-detection entirely. Your call, bestie.

Arrayrefs get passed straight to the 'Mock' decoder, which is the chill one — it just returns the data as-is. No thoughts, just vibes 🌸

🔍 decoder

This can be any of these:

  • an instance of Data::TableReader::Decoder
  • a class name
  • a partial class name — which gets appended as Data::TableReader::Decoder::$name
  • an arrayref or hashref of arguments used to build the decoder

In an arrayref the first argument can be undef, and in a hashref the CLASS argument can be missing or undef. In those cases it gets detected from the input attribute, and any default arguments get combined with — and if necessary trumped by — the extra arguments in the arrayref or hashref. periodt 👑

Examples, because examples ate:

'CSV'
# becomes Data::TableReader::Decoder::CSV->new()

[ 'CSV', sep_char => "|" ]
# becomes Data::TableReader::Decoder::CSV->new(sep_char => "|")

{ CLASS => 'CSV', sep_char => "|" }
# becomes Data::TableReader::Decoder::CSV->new({ sep_char => "|" })

🌈 fields

An arrayref of Data::TableReader::Field objects — these are the things this module goes hunting for inside the tables (worksheets etc.) of input.

If an element of this array is a hashref or a string, it gets coerced into an instance of Data::TableReader::Field, and plain strings become the name attribute. Wanna know how names get auto-converted into that header-matching regex? Peep "header" in Data::TableReader::Field. ✨

There are some convenience accessors for the fields, and they are lowkey life-changing:

field_list

List access for "@{ $reader->fields }"

field_by_name

Map of "{ $field->name => $field }". If you've got multiple fields with the same name (allowed, but honestly not recommended, bestie), the value is the first one per the order of "field_list".

field_by_addr

Map of "{ refaddr($field) => $field }".

📦 record_class

Default is the special value 'HASH' for un-blessed hashref records. The special value 'ARRAY' gives you arrayrefs with fields in the same order they were specified in the fields spec. And if you set it to literally anything else, records get created with:

$record_class->new(\%fields);

📐 static_field_order

Boolean, bestie. Whether the fields must be found in columns in the exact order that they were specified. Default is false. Chill.

📍 header_row_at

Row number, or a range of row numbers, where the header has to be found. (Every row number in this module is 1-based, matching end-user expectations — we're not doing that zero-index nonsense here 🙈.) Default is "[1,10]", so header scanning stays limited to the first 10 rows.

Special case alert 🚨 — if you're reading a source that lacks headers and you trust it to deliver the columns in the right order, you can set this to undef, but only if you also set "static_field_order => 1".

🗺️ col_map

This is an arrayref, one element per column of input data, listing which field was detected to come from that column. If you specify it to the constructor, find_table will respect any defined element of the array, but it'll still search for matching headers in the undefined columns. After a successful find_table, col_map gets changed to refer to the same hash as "->table_search_results->{found}{col_map}".

(So if you wanted to re-run the search for the table, you need to both "clear_table_search_results" and reset col_map back to whatever you passed to the constructor. yeah, both. it's giving thorough 🎀)

For backward compatibility: if you didn't specify this attribute to the constructor and you try accessing it before calling find_table, it automatically calls find_table for you — and dies if it fails. no cap 💀

✅ has_col_map

Check whether col_map has been defined, so you can avoid lazy-building it.

🔎 table_search_results

This is the output of the most recent find_table operation:

{
  candidates => [
    { dataset_idx => $n,
      row => $n,
      col_map => [ $field_or_undef, $field_or_undef, ... ],
      missing_required => \@fields,
      ambiguous_columns => { $col_idx => \@fields, ... },
      ambiguous_fields => { $field_name => { $col_idx => $field }, ... },
      unmatched => \@col_idx,
      messages => [],
    },
    ...
  ],
  found => $ref_to_candidate, # undef if find_table failed
}

The fields describing problems ("missing_required", "ambiguous_columns", "ambiguous_fields", and "unmatched") are not present unless they actually contain data. All @fields are refs to the actual Field objects. "col_map" has one element per element of the header row.

Heads up 🫶 — if "missing_required" is populated, the analysis of ambiguity may be incomplete, because missing required columns abort the search for the header. And all "_idx" values are 0-based, but the errors in "messages" use 1-based descriptions. ikyky 😭

🧹 has_table_search_results, clear_table_search_results

Predicate and clearer for the lazy-built table_search_results. So helpful, so demure 💅

🎯 on_partial_match

on_partial_match    => 'next'    # keep searching for a better line of headers
on_partial_match    => 'last'    # return failure from ->find_table
on_partial_match    => sub {
  my ($reader, $candidate, $header_row)= @_;
  return $action; # one of the above values
}

During find_table, if a row gets found that matches at least one header but fails to match all the requirements (required columns, unknown or ambiguous columns if those are configured as an error), you can either keep searching for a better header row, or stop right there. Default is 'next' — keep searching — but ngl that can result in a lot of noise. The 'last' setting lets you stop after a likely header row, which is honestly the move sometimes.

If you supply a coderef, you receive the candidate info described in "table_search_results". 🦋

🌀 on_ambiguous_columns

on_ambiguous_columns => 'warn'    # warn, and omit from the match
on_ambiguous_columns => 'error'   # fail the header match for this row

During find_table, when matching a field's header pattern against the columns of a row, if the pattern could match more than one cell, it's an error. You might wanna handle it in various ways:

  • 'warn' — If a Field matches multiple columns (and isn't an array field), omit the field from the col_map entirely. If a column matches multiple fields, leave the col_map blank for this column. Both generate warnings, but the header match can still proceed to a successful result. slay 🌸
  • 'error' (default) — Any ambiguities (field matching multiple columns, multiple fields matching a column) cause the match of the header on this row to fail. Further attempts at finding the header depend on the "on_partial_headers" setting.

👻 on_unknown_columns

on_unknown_columns => 'warn'  # warn, and then accept these headers
on_unknown_columns => 'error' # fail the header match for this row
on_unknown_columns => sub {
  my ($reader, $col_headers)= @_;
  ...;
  return $opt; # one of the above values
}

This determines handling for columns that aren't associated with any field. The "required" columns must all be found before it even considers this setting, but once it's found everything it needs to make this a candidate, you might or might not care about the leftover columns. live your truth ✨

  • 'warn' (default) — You don't care if there are extra columns, just log warnings about them and proceed extracting from this table.
  • 'error' — Extra columns mean you didn't find the table you wanted. Log the near-miss, and keep searching additional rows or additional tables, according to "on_partial_headers".
  • "sub {}" — You can add your own logic to handle this. Inspect the headers however you like, and then return one of the above values. main character energy 👑

🕳️ on_blank_rows

on_blank_rows => 'next' # warn, and then skip the row(s)
on_blank_rows => 'last' # warn, and stop iterating the table
on_blank_rows => 'die'  # fatal error
on_blank_rows => 'use'  # actually try to return the blank rows as records
on_blank_rows => sub {
  my ($reader, $first_blank_rownum, $last_blank_rownum)= @_;
  ...;
  return $opt; # one of the above values
}

This determines what happens when you've found the table, you're extracting records, and you hit a series of blank rows (defined as a row with no printable characters in any field) followed by non-blank rows. If you use the callback, it suppresses the default warning, since you can generate your own. No double-warning drama 💖

The default is 'next'.

💔 on_validation_error

on_validation_error => 'next'  # warn, and then skip the record
on_validation_error => 'use'   # warn, and then use the record anyway
on_validation_error => 'die'   # fatal error
on_validation_error => sub {
  my ($tablereader, $failures, $record, $data_iterator)= @_;
  # $record is the assembled hashref (unblessed) or arrayref of fields
  # $data_iterator is the Decoder's row iterator, useful for context
  for (@$failures) {
    my ($field, $value_ref, $message, $path)= @$_;
    ...
    # $field is a Data::TableReader::Field
    # $$value_ref is the string that failed validation
    # $message is the error returned from the validation function
    # $path is the element (and maybe sub-element) of $record
    #   i.e.  $value_ref= \$record->{$path[0]}[$path[1]]
    # You may modify $$value_ref or $record to alter the output
  }
  # Clear the failures array to suppress warnings, if you actually corrected
  # the validation problems.
  @$failures= ();
  # return one of the above constants to tell the iterator what to do next
  return $opt;
}

This determines what happens when you've found the table, you're extracting records, and one row fails its validation. In addition to deciding an option, the callback gives you a chance to alter the record before 'use'ing it. So generous, honestly 🫶

The default is 'die'. yeah. dramatic. but safe 🔥

📝 log

If undefined (the default), all log messages above 'info' get emitted with "warn "$message\n"".

If set to an object, it should support an API of:

trace,  is_trace
debug,  is_debug
info,   is_info
warn,   is_warn
error,  is_error

— such as Log::Any and may other perl logging modules use.

You can also set it to a coderef, like:

my @messages;
sub { my ($level, $message)= @_;
  push @messages, [ $level, $message ]
    if grep { $level eq $_ } qw( info warn error );
};

which is a simple way to capture the messages without dragging a logging module into it. so cute, so lightweight 🌸

And for extra convenience, you can set it to an arrayref, which will receive any message that would otherwise have gone to 'warn' or 'error'. obsessed with the flexibility tbh 💜

✨ METHODS ✨

🔍 detect_input_format

my ($class, @args)= $tr->detect_input_format( $filename, $head_of_file );

Okay so this is lowkey the internal format detective, but you can call it manually if you want. The first argument is optional and it's a file name. The second argument is also optional and it's the first few hundred bytes of the file. Missing arguments will be pulled from input if possible. The return value is the best guess of module name and constructor arguments that should be used to parse the file. However, this doesn't guarantee such module actually exists or is installed; it might just echo the file extension back to you. 😭 Not me getting an extension echo instead of a module. 💀

🕵️ find_table

if ($tr->find_table) { ... }

This searches through the input for the beginning of the records, identified by a header row matching the various constraints defined in fields. If header_row_at is undef, then this does nothing and assumes success. It returns a boolean of whether it succeeded. Also, this method does not croak on failure like iterator does, on the assumption that you want to handle them gracefully. All diagnostics about the search are logged via log, but also reported in table_search_results. It's giving graceful failure, periodt. 🫶

🗺️ field_map

Builds a hashref of { $field_name => $col_idx_or_arrayref } for the current col_map. If the field is defined as an array field, the value will be an arrayref (even if only found in one column). Otherwise, the value is a simple scalar of the column index. Main character mapping behavior, no cap. ✨

🔁 iterator

my $iter= $tr->iterator;
while (my $rec= $iter->()) { ... }

Creates an iterator. If the table has not been located, then find it and croak if it can't be found. Depending on the decoder and input filehandle, you might only be able to have one instance of the iterator at a time. 👀

The iterator derives from Data::TableReader::Iterator but also has a method all which returns all records in an arrayref.

my $records= $tr->iterator->all;

💖 THANKS 💖

Portions of this software were funded by Ellis, Partners in Management Solutions <http://www.epmsonline.com/> and Candela Corporation <https://www.candelacorp.com/>. Obsessed with a funded software moment. 🫶

👑 AUTHOR 👑

Michael Conrad <mike@nrdvana.net>

🌸 CONTRIBUTOR 🌸

Christian Walde <walde.christian@gmail.com>

This software is copyright (c) 2024 by Michael Conrad.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

And that's the whole page, bestie. It's perl v5.38.2, dated 2024-06-21, and it documents Data::...eReader(3pm). Slay. 💅✨

📦 where this page comes from