I am wanting to keep track of my bandwidth using the speedtest-cli tool. Every hour I am able to create a text file as such:

Hosted by ISP (City, ST) [141.56 km]: 3.549 ms
Download: 892.81 Mbit/s
Upload: 940.12 Mbit/s

My goal is to create a table of some type that will store this information for historical and graphing needs. I thought a CSV file might work well for this. How can I parse those 4 lines of text to extract specific data and redirect the output to a single line, with a comma delimiter, and append to an existing file? i.e:

Date, Response Time, Download (Mbit/s), Upload (Mbit/s)
2020-10-30-09:21:28, 3.549, 892.81, 940.12
  • 2
    Could there be a typo in the desired output example? The "Response Time" value (47.549)doesn't match the input file text (3.549).
    – AdminBee
    Oct 30 '20 at 15:25
  • 1
    Have you tried or searched something? stackoverflow.com/q/9605232 looks like a good start.
    – Quasímodo
    Oct 30 '20 at 15:33

Having the space after the comma is a bit atypical, but you could simply do:

{ awk '{printf "%s%s", NR==1 ? "": ", ", $(NF-1)}' input; echo; } >> output

Or, if you're okay with having a trailing comma:

awk '{print $(NF-1)} END{printf"\n"}' ORS=', ' input >> output

The first solution uses a printf to print the 2nd to last record of each line. This is a bit fragile, but works for your input sample. It also takes advantage of an awk quirk that treats $0 as the full line, so it prints the full line if there is only one record in a line. The NR==1 ?"":", " is just a ternary operator that prints a separator on all but the first line. Since this never prints a newline, we finish up with an echo to get a newline.

The second solution again print the 2nd to last record of each line (or the whole line of a line has only one field), but uses an END clause to print the final newline and uses the record separator to insert the commas. This causes the unwanted trailing newline. And that motivates a final, cleaner solution:

awk 'NR==4{ORS="\n"}{print $(NF-1)}' ORS=', ' input

Here, rather than slapping a newline at the end, we change the output record separator when reading the 4th row.

  • William, this worked exactly as needed. The trailing comma was no issue as I just wanted to view the data correctly in Excel. Thank you very much for this. I don't do alot of work with Linux or scripting for that matter. As I was doing my own research in an attempt to self-resolve my problem, I was running into "awk" a lot. If I said I understood all the formatting, it would be a lie. I will take what you gave me, as it works, and try to decipher it.
    – Brandon763
    Oct 30 '20 at 16:48
  • @Brandon763 I'll add an explanation, but it's pretty straightforward. Oct 31 '20 at 14:31

pcregrep is handy to extract and report data from multi-line input using regular expressions.


<input.txt pcregrep --om-separator=', ' -o1 -o2 -o3 -o4 -M \
  '^(.*)\n.*?(\S+) ms\n.*?(\S+) Mbit/s\n.*? (\S+) Mbit/s$'

Or the same with perl (the p in pcregrep):

perl -l -0777 -ne 'print join ", ", $1, $2, $3, $4 if 
  m{^(.*)\n.*?(\S+) ms\n.*?(\S+) Mbit/s\n.*? (\S+) Mbit/s$}'

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.