How to [constantly] read the last line of a file?

Question

I have client filtering out data from an live MQTT stream which writes the data into a myfile.csv file, here are the last four lines:

1426134425,m,NWRL,MSV,001,d,SVlts,139,1840343,26089,28529,15987
1426134444,m,NWRL,MSV,001,d,status,139,1859000,23911,-33.836465,151.051189
1426134834,m,gf,TMX6BP,075,d,SVlts,216,1243746,27209,27409,17106
1426134845,m,gf,TMX6BP,075,d,status,216,1254000,179583,-33.836465,151.051189

This file constantly gets updated. So the last line of the csv file would constantly be changing however it would still be in the same format.

Q: How can I read the last line where the 7th value is 'status' not 'SVlts' of the myfile.csv file with bash? Once read how can I assign the 4th, 5th and 10th value to a 3 different variables? I will be pushing these values out to update a MySQL table.

So the output can be something as follows:

variable_1=TMX6BP
variable_2=075
variable_3=179583

As mentioned these three variables will be pushed/sent out to update a MySQL table (which needs these three variables in order to update itself) until the next line is printed in to the myfile.csv. And this will happen over and over again.

Answer 1

This captures the information that you want from the last line of file:

$ IFS=, read -r a b c var_1 var_2 d e f h var_3 extra < <(tail -n1 file)
$ echo $var_1  $var_2  $var_3
TMX6BP 075 179583

How it works

• IFS=,

  This temporarily sets the field separator to a comma.

• read -r a b c var_1 var_2 d e f h var_3 extra

  This reads in the fields to the listed variables. You can choose any names you like.

  In the above, the first field is assigned to the shell variable a, the second to b, etc. Everything left over after var_3 is assigned to the shell variable extra.

• < <(tail -n1 file)

  This provides the last line of file as standard input to the read command. The first < is the shell symbol for redirection. The construct <(...) is called process substitution. The space between the first and second < is essential.

Answer 2

Short answer:

   # tail -F: output appended data as the file grows
   #      Capital F to follow even if the file is reset to zero, recreated, etc.
   # awk -F, sets FS - Field Separator to a comma
   #   Inside awk: See if field 7 == "status" and print fields 4, 5 and 10
tail -F myfile.csv | awk -F, '$7 == "status" { print $4, $5, $10 }'

You are not writing how you will update your database so I'll write up something which might get you started.

It's also my understanding that you will NOT edit the myfile.csv file, but use the 3 mentioned variables as input to an update script/job. Error checking, etc, I'll leave to you.

First, create the script which updates your MySQL database. I'll just mock it here.

#!/bin/bash
# update-MySQL-table.sh
    # redirecting stderr to stdout so awk will see it.
    # Not much use with "echo", but ...
echo $0 $1 $2 $3 2>&1

Then you have another script which reads and parses your CSV file, and starts the database update script.

#!/bin/bash
# parse-my-CSV-file.sh
csvfile="myfile.csv"

tail -n0 -F $csvfile | \
    awk -F, '
        $7 == "status" {
            cmd=sprintf("./update-MySQL-table.sh %s %s %s",$4,$5,$10)
            cmd | getline output
            print output
    }'

You then start the parse-script, and you should see how new lines added to myfile.csv with "status" in the 7th field are printed to stdout.

Answer 3

I'd do something like:

tail -n1 -f file | 
  perl -MDBI -F, -lane 'BEGIN{$d = DBI->connect(...)}
    if ($F[6] eq "status") {
      $d->do("INSERT INTO foo VALUES (?,?,?)", @F[3,4,9]);
    }'