0

In Ubuntu 16.04, I'm trying to find a way to append the Day of the Week to the end of each line in a text file given the date in field 4.

Sample data:

Server ID,Make,"Server Room",Datestamp,Timestamp,Distance,Ping,Download,Upload,Payload,"Src IP Address",Hour,DOW
x6883101,HP,"Server Room A",2019-07-14,04:50:02,26.444,11.521,49193480,41904833,,192.168.1.1,4, 
s3398577,Dell,"Server Room B",2019-09-21,10:50:02,56.574,37.608,48955461,45858381,,192.168.1.1,10, 
x6883551,Dell,"Server Room A",2019-08-16,02:00:04,26.444,17.921,86551957,88775986,,192.168.1.1,2, 
s1555023,HP,"Server Room C",2018-02-06,04:50:01,516.574,402.527,907658,608152,,192.168.1.1,4, 
s3398023,HP,"Server Room B",2019-01-17,10:50:01,56.574,40.233,48484827,45620028,,192.168.1.1,10, 
s1555098,IBM,"Server Room C",2018-11-18,02:00:03,516.514,404.671,819027,601233,,192.168.1.1,2, 
x6883582,Dell,"Server Room A",2019-05-19,04:50:02,26.444,12.506,88871436,84360552,,192.168.1.1,4,

For example, for data line #1 and #2:

x6883101,HP,"Server Room A",2019-07-14,04:50:02,26.444,11.521,49193480,41904833,,192.168.1.1,4,Sunday
s3398577,Dell,"Server Room B",2019-09-21,10:50:02,56.574,37.608,48955461,45858381,,192.168.1.1,10,Saturday

I've tried various SED and AWK with nothing. I've tried the DATE command, but it doesn't seem to like input. I've been able to isolate the actual date from

grep -w -o "20[0-9][0-9]-[0-9][0-9]-[0-9][0-9]*"

but nothing that I see is able to convert it and append the DOW at the end of the lines.

What am I missing that makes appending the Day of the Week to the end of each of the lines of data?? Also, I need to be able to do this from a CRONTAB job.

4

With GNU awk, you could do:

gawk -i inplace -F, -v OFS=, -v date_field=4 '
  (t = mktime(gensub("-", " ", "g", $date_field) " 0 0 0")) > 0 {
    $NF = strftime("%A", t)};1' your-file
  • -i inplace: enables the in-place editing mode of gawk, whereby the output is written into a new file destined to be the replacement of the input file.
  • -F, and -v OFS=, set the input and output field separators
  • mktime() is a GNU awk extension that parses a string in the year month day hour minute second format and returns the corresponding Unix epoch time. Here, we use gensub() (another gawk extension) to replace the - with spaces in the 4th field (YYYY-MM-DD) so as to pass a YYYY MM DD 0 0 0 time to mktime().
  • (t = mktime(...)) > 0 {...} and 1 are two condition {action} pairs which are run on every input record (here lines).

    • For the first one, the condition checks if the value returned by mktime() (assigned to t) is greater than 0 (mktime() returns -1 if it cannot parse the date specification), in which case the action is run. strftime() (another gawk extension) like its C equivalent is used to format a time (here the unix epoch time stored in t with format %A: the localised week day name). We assign the result to the NFth field ($NF), NF being the special variable that contains the number of fields in the current record and $ being an operator to retrieve fields contents (or the full record with $ 0).
    • the second one (1) is missing the action part which defaults to {print} (prints the current record), and the condition (1) is always true. That's the idiomatic short way to unconditionally print the current record, it you wanted to be more verbose, you could do:

      gawk -i inplace \
           -v FS=, \
           -v OFS=, \
           -v date_field=4 \
           -v current_record=0 \
           -v always=1 '
        {
          date_for_mktime = gensub("-", " ", "g", $date_field) " 0 0 0"
          unix_time = mktime(date_for_mktime)
        } 
        unix_time > 0 {
          $NF = strftime("%A", unix_time)
        }
        always {print $current_record}' your-file
      

If you want the week day names to always be in English regardless of the locale of the user, you can fix the locale to C (LC_ALL=C gawk...).

4
  • I stand corrected, it is possible in a one-liner after all. Perhaps you could add an explanation how it works, i.e. that you generated a "full" date-time specification that can be fed to awks internal strftime function (a point I struggled with, leading to my more lengthy answer).
    – AdminBee
    Nov 7 '19 at 13:07
  • @AdminBee, see edit Nov 7 '19 at 14:29
  • Great explanation, thank you!
    – AdminBee
    Nov 7 '19 at 14:39
  • @StéphaneChazelas Thank you so much! This helped immensely and I learned something! :) Nov 8 '19 at 1:56
1

With Miller (mlr):

mlr --csvlite put '
  $Hour = ""; $DOW = strftime(strptime($Datestamp,"%Y-%m-%d"),"%A")
' file.csv

Ex.

$ mlr --csvlite put '$Hour = ""; $DOW = strftime(strptime($Datestamp,"%Y-%m-%d"),"%A")' file.csv
Server ID,Make,"Server Room",Datestamp,Timestamp,Distance,Ping,Download,Upload,Payload,"Src IP Address",Hour,DOW
x6883101,HP,"Server Room A",2019-07-14,04:50:02,26.444,11.521,49193480,41904833,,192.168.1.1,,Sunday
s3398577,Dell,"Server Room B",2019-09-21,10:50:02,56.574,37.608,48955461,45858381,,192.168.1.1,,Saturday
x6883551,Dell,"Server Room A",2019-08-16,02:00:04,26.444,17.921,86551957,88775986,,192.168.1.1,,Friday
s1555023,HP,"Server Room C",2018-02-06,04:50:01,516.574,402.527,907658,608152,,192.168.1.1,,Tuesday
s3398023,HP,"Server Room B",2019-01-17,10:50:01,56.574,40.233,48484827,45620028,,192.168.1.1,,Thursday
s1555098,IBM,"Server Room C",2018-11-18,02:00:03,516.514,404.671,819027,601233,,192.168.1.1,,Sunday
x6883582,Dell,"Server Room A",2019-05-19,04:50:02,26.444,12.506,88871436,84360552,,192.168.1.1,,Sunday

Package miller is available from the Ubuntu 16.04 universe repository.

1

my first reaction was the same as Stephane's.

Perl would work too:

perl -MTime::Piece -F, -lape '
  if ($F[3] =~ /^[\d-]+$/) {
    $F[-1] = Time::Piece->strptime($F[3], "%Y-%m-%d")->strftime("%A");
    $_ = join ",", @F;
  }
' file
1
  • 1
    This also respects the LC_ALL setting. Nov 7 '19 at 15:15
1

It is probably not possible using a "one-liner" (Edit: it turnd out to be possible, after all), but you can do this using a shell script. Core is the use of the date utility to retrieve the weekday from the date you have already extracted.

To do this, look at the output of

user@host $ date +%A -d 2019-07-14
Sunday

Using this, you can write a script which reads the input file line-wise, extracts the week day for each line except the header, and writes the header unchanged and all other lines with weekday appended to a different file.

#!/bin/bash

# Check if input and output filenames were specified
if [[ "$#" != "2" ]]
then
    echo "Usage: $0 <input filename> <output filename>"
    exit 1
fi

if [[ ! -e "$1" ]]
then
    echo "Error, $1 doesn't exist!"
    exit 1
fi

if [[ "$1" == "$2" ]]
then
    echo "Error, input and output filenames must not be equal!"#
    exit 1
fi



# Perform the actual transformation
while read input
do
    if [[ -n "$(grep "^Server" <<< $input)" ]]
    then
    echo "$input" > $2
    else
    date=$(awk '{match($0,",(20[[:digit:]]{2}-[[:digit:]]{2}-[[:digit:]]{2})",a); print a[1]}' <<< $input)
    wday=$(date +%A -d $date)
    echo "$input$wday" >> $2
    fi
done < $1

To explain

The first part are sanity checks on the input, they could also be peformed otherwise.

The second part is the actual loop over the lines of your input file.

  • First, we check if the current line is the "header" by using grep to determine if it starts with the string "Server". If the result of grep is a non-empty string, write it to the output file "as is".
  • Then, use awk to find the pattern "20YY-MM-DD", enclosed by commas, and extract the actual value into the internal array a which is assigned to the bash variable $date.
  • This variable is now used in the call to date as specified above to return the day of week.
  • The input line, with appended weekday, is then written to the output file.

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.