Trying to take an input that looks like this:

08/22/2019 12:00:58
Name Cans Bucks Puns
Clyde 12 2 79
Sheila 32 16 42
Elmo 44 18 21

08/23/2019 19:00:22
Name Cans Bucks Puns
Clyde 18 21 46
Sheila 37 2 11
Elmo 41 3 10

into output like this:

name=Clyde cans=12 bucks=2 puns=79 ts=1566475258
name=Sheila cans=32 bucks=16 puns=42 ts=1566475258
name=Elmo cans=44 bucks=18 puns=21 ts=1566475258
name=Clyde cans=18 bucks=21 puns=46 ts=1566586822
name=Sheila cans=37 bucks=2 puns=11 ts=1566586822
name=Elmo cans=41 bucks=3 puns=10 ts=1566586822

I've tried fruitlessly to achieve this with awk (minus the time conversion which has me completely stumped).

Closest I've gotten get is:

ts=08/22/2019 12:00:58
name=Clyde cans=12 bucks=2 puns=79
name=Sheila cans=32 bucks=16 puns=42
name=Elmo cans=44 bucks=18 puns=21
ts=08/23/2019 19:00:22
name=Clyde cans=18 bucks=21 puns=46
name=Sheila cans=37 bucks=2 puns=11
name=Elmo cans=41 bucks=3 puns=10

I'm not even sure that awk is the best tool for this.

awk -F'[/: ]' '{
  if (NF==6){
    ts=mktime($3" "$1" "$2" "$4" "$5" "$6)
  else if (NF==0 || skipheader){
  else {
    print "name="$1,"cans="$2,"bucks="$3,"puns="$4,"ts="ts
}' file
  • Split fields on /, : and space character to get the individual date and time parts.
  • If the number of fields is 6, create timestamp ts and set a flag to skip the next header line.
  • If the number of fields is zero or the skipheader flag is set, reset the skipheader flag.
  • Else, print the data.


name=Clyde cans=12 bucks=2 puns=79 ts=1566468058
name=Sheila cans=32 bucks=16 puns=42 ts=1566468058
name=Elmo cans=44 bucks=18 puns=21 ts=1566468058
name=Clyde cans=18 bucks=21 puns=46 ts=1566579622
name=Sheila cans=37 bucks=2 puns=11 ts=1566579622
name=Elmo cans=41 bucks=3 puns=10 ts=1566579622
| improve this answer | |

The following perl script will work with any number of fields, with any field names, in the input.

It requires the Time::Date and List::MoreUtils library modules. Both of these are probably already packaged for your distro (e.g. on debian sudo apt-get install libtimedate-perl liblist-moreutils-perl). The script could be written so that it didn't need those modules, but there's no call to re-invent the wheel when there is existing re-usable library code that does exactly the job you need.

The script assumes that the input lines are delimited by any amount of white space (i.e. one or more spaces, tabs, etc). If the input is tab-separated instead, change the lines with split; to split /\t/;. Tab separators would be a good idea if any of the names in the first field contained spaces (e.g. Firstname Surname), or if any of the field names contained spaces.

If you need to hard-code a specific timezone, e.g. GMT, change the following line:

    $ts = str2time($_);

to, e.g. (note the space inside the quotes before GMT):

    $ts = str2time($_ . ' GMT');

use strict;
use Date::Parse;
use List::MoreUtils qw(pairwise);

my @columns;
my $ts='';

while(<>) {
  s/^\s*|\s*$//g;    #/ strip leading and trailing spaces
  next if (/^$/);    #/ skip empty lines

  # line begins with two digits and a slash? it's a date.
  if (m/^\d\d\//) {
    # get the date and parse it so that we have seconds since the epoch
    $ts = str2time($_);

    # get the next line and split it into column headers
    $_ = readline;
    @columns = split;

  } else {
    # split the current line into @row array
    my @row=split;
    # use pairwise() function from List::MoreUtils module to merge the
    # @columns and @row arrays.
    print join(" ", (pairwise { "$a=$b" } @columns, @row), "ts=$ts"), "\n";


Sample output:

$ ./reformat.pl input.txt 
Name=Clyde Cans=12 Bucks=2 Puns=79 ts=1566439258
Name=Sheila Cans=32 Bucks=16 Puns=42 ts=1566439258
Name=Elmo Cans=44 Bucks=18 Puns=21 ts=1566439258
Name=Clyde Cans=18 Bucks=21 Puns=46 ts=1566550822
Name=Sheila Cans=37 Bucks=2 Puns=11 ts=1566550822
Name=Elmo Cans=41 Bucks=3 Puns=10 ts=1566550822

NOTE: This won't quite break if any of the data lines have more or less columns than the number of column headers, but it will produce unusual output. For missing fields, it will just print the field names with an = symbol and no value (e.g. if an input line had only 2 fields, it would output Puns=), and if there were more fields, it would print just the value prefixed with an = (e.g. if an input line had an extra field with value 20, it would output =20).

e.g. if your sample input had a third block of data like this:

08/23/2019 23:30:01
Name Cans Bucks Puns
Clyde 18 21 46
Sheila 37 2 11
Elmo 41 3 10
Missing 41 3
Extra 41 3 10 20

That would result in this extra output:

Name=Clyde Cans=18 Bucks=21 Puns=46 ts=1566567001
Name=Sheila Cans=37 Bucks=2 Puns=11 ts=1566567001
Name=Elmo Cans=41 Bucks=3 Puns=10 ts=1566567001
Name=Missing Cans=41 Bucks=3 Puns= ts=1566567001
Name=Extra Cans=41 Bucks=3 Puns=10 =20 ts=1566567001
| improve this answer | |

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.