How to extract data from html table in shell script?

Question

I am trying to create a BASH script what would extract the data from HTML table. Below is the example of table from where I need to extract data:

<table border=1>
<tr>
<td><b>Component</b></td>
<td><b>Status</b></td>
<td><b>Time / Error</b></td>
</tr>
<tr><td>SAVE_DOCUMENT</td><td>OK</td><td>0.406 s</td></tr>
<tr><td>GET_DOCUMENT</td><td>OK</td><td>0.332 s</td></tr>
<tr><td>DVK_SEND</td><td>OK</td><td>0.001 s</td></tr>
<tr><td>DVK_RECEIVE</td><td>OK</td><td>0.001 s</td></tr>
<tr><td>GET_USER_INFO</td><td>OK</td><td>0.143 s</td></tr>
<tr><td>NOTIFICATIONS</td><td>OK</td><td>0.001 s</td></tr>
<tr><td>ERROR_LOG</td><td>OK</td><td>0.001 s</td></tr>
<tr><td>SUMMARY_STATUS</td><td>OK</td><td>0.888 s</td></tr>
</table>

And I want the BASH script to output it like so:

SAVE_DOCUMENT OK 0.475 s
GET_DOCUMENT OK 0.345 s
DVK_SEND OK 0.002 s
DVK_RECEIVE OK 0.001 s
GET_USER_INFO OK 4.465 s
NOTIFICATIONS OK 0.001 s
ERROR_LOG OK 0.002 s
SUMMARY_STATUS OK 5.294 s

How to do it?

So far I have tried using the sed, but I don't know how to use it quite well. The header of the table(Component, Status, Time/Error) I excluded with grep using grep "<tr><td>, so only lines starting with <tr><td> will be selected for next parsing (sed). This is what I used: sed 's@<$[^<>][^<>]*$>$[^<>]*$</\1>@\2@g' But then <tr> tags still remain and also it wont separate the strings. In other words the result of this script is:

<tr>SAVE_DOCUMENTOK0.406 s</tr>

The full command of the script I'm working on is:

cat $FILENAME | grep "<tr><td>" | sed 's@<\([^<>][^<>]*\)>\([^<>]*\)</\1>@\2@g'

Does it have to be a bash script? If you are on Linux, perhaps a Perl script would be an easier option. You could then use the [HTML::Parser](http://search.cpan.org/~gaas/HTML-Parser-3.68/Parser.pm) module, or similar. — Mike, Jul 28 '11 at 06:16

score 17 · Accepted Answer · edited May 02 '15 at 16:22

17

Go with (g)awk, it's capable :-), here is a solution, but please note: it's only working with the exact html table format you had posted.

 awk -F "</*td>|</*tr>" '/<\/*t[rd]>.*[A-Z][A-Z]/ {print $3, $5, $7 }' FILE

Here you can see it in action: https://ideone.com/zGfLe

Some explanation:

-F sets the input field separator to a regexp (any of tr's or td's opening or closing tag
then works only on lines that matches those tags AND at least two upercasse fields
then prints the needed fields.

HTH

edited May 02 '15 at 16:22

Roman Byshko

8,591
7
35
57

answered Jul 28 '11 at 06:20

Zsolt Botykai

50,406
14
85
110

Superb explanation! Thank you a lot! – Marko Jul 28 '11 at 06:31
How to edit output more? For example if I want the output to be like this: SAVE_DOCUMENT="OK 0.475 s" GET_DOCUMENT="OK 0.345 s" DVK_SEND="OK 0.002 s" – Marko Jul 28 '11 at 07:53
Just replace the `{print $3, $5, $7 }` part with `{print $3 "=\"" $5, $7 "\"" }` HTH – Zsolt Botykai Jul 28 '11 at 08:09
Yeah, it works! One more question, how to remove space and "s" behind the numbers, so the output would be: DVK_SEND="OK 0.002" – Marko Jul 28 '11 at 11:47
It's a very little bit trickier: use `{print $3 "=\"" $5, gensub(" *s$","","g",$7) "\"" }` HTH – Zsolt Botykai Jul 28 '11 at 20:32

score 12 · Answer 2 · answered Jul 28 '11 at 07:32

12

You can use bash xpath (XML::XPath perl module) to accomplish that task very easily:

xpath -e '//tr[position()>1]' test_input1.xml 2> /dev/null | sed -e 's/<\/*tr>//g' -e 's/<td>//g' -e 's/<\/td>/ /g'

answered Jul 28 '11 at 07:32

Emiliano Poggi

24,390
8
55
67

score 9 · Answer 3 · edited Apr 13 '17 at 12:51

You may use html2text command and format the columns via column, e.g.:

$ html2text table.html | column -ts'|'

Component                                      Status  Time / Error
SAVE_DOCUMENT                                           OK            0.406 s     
GET_DOCUMENT                                            OK            0.332 s     
DVK_SEND                                                OK            0.001 s     
DVK_RECEIVE                                             OK            0.001 s     
GET_USER_INFO                                           OK            0.143 s     
NOTIFICATIONS                                           OK            0.001 s     
ERROR_LOG                                               OK            0.001 s     
SUMMARY_STATUS                                          OK            0.888 s

then parse it further from there (e.g. cut, awk, ex).

In case you'd like to sort it first, you can use ex, see the example here or here.

Unfortunately, `html2text` peppers its output with backspace characters by default, so even though your output _looks_ OK, it its full of hidden `\b` (backspace) sequences. Safely removing them requires more work (using `-nobs` is not an option, because it surfaces hidden `_` instances used for padding that are then hard to distinguish from `_` instances in the _data_). — mklement0, Jul 11 '17 at 19:46

score 5 · Answer 4 · answered Jul 28 '11 at 06:19

There are a lot of ways of doing this but here's one:

grep '^<tr><td>' < $FILENAME \
| sed \
    -e 's:<tr>::g'  \
    -e 's:</tr>::g' \
    -e 's:</td>::g' \
    -e 's:<td>: :g' \
| cut -c2-

You could use more sed(1) (-e 's:^ ::') instead of the cut -c2- to remove the leading space but cut(1) doesn't get as much love as it deserves. And the backslashes are just there for formatting, you can remove them to get a one liner or leave them in and make sure that they're immediately followed by a newline.

The basic strategy is to slowly pull the HTML apart piece by piece rather than trying to do it all at once with a single incomprehensible pile of regex syntax.

Parsing HTML with a shell pipeline isn't the best idea ever but you can do it if the HTML is known to come in a very specific format. If there will be variation then you'd be better with with a real HTML parser in Perl, Ruby, Python, or even C.

Thank you too! Also very good explanation, but Zsolt Botykai solution looks cooler:) — Marko, Jul 28 '11 at 06:32
@Marko: Yeah, awk is a better tool for this sort of thing, more consistent regex handling than sed too. — mu is too short, Jul 28 '11 at 06:35

mklement0 · Answer 5 · 2022-05-08T12:54:11.107

A solution based on multi-platform web-scraping CLI xidel and XPath:
^{Tip of the hat to Reino for providing the simpler XPath equivalent to the original XQuery solution.[1]}

xidel -s -e '//tr[position() > 1]/join(td)' file

With the sample input, this yields:

SAVE_DOCUMENT OK 0.406 s
GET_DOCUMENT OK 0.332 s
DVK_SEND OK 0.001 s
DVK_RECEIVE OK 0.001 s
GET_USER_INFO OK 0.143 s
NOTIFICATIONS OK 0.001 s
ERROR_LOG OK 0.001 s
SUMMARY_STATUS OK 0.888 s

Explanation:

//tr[position() > 1] matches the tr elements starting with the 2nd one, so as to skip the header row), and join(td) joins the values of the matching elements' child td elements with an implied single space as the separator.
-s makes xidel silent (suppresses output of status information).

While html2text is convenient for display of the extracted data, providing machine-parseable output is non-trivial, unfortunately:

html2text file | awk -F' *\\|' 'NR>2 {gsub(/^\||.\b/, ""); $1=$1; print}'

The Awk command removes the hidden \b-based (backspace-based) sequences that html2text outputs by default, and parses the lines into fields by |, and then outputs them with a space as the separator (a space is Awk's default output field separator; to change it to a tab, for instance, use -v OFS='\t').

^{Note: Use of -nobs to suppress backspace sequences at the source is not an option, because you then won't be able to distinguish between the hidden-by-default _ instances used for padding and actual _ characters in the data.}

Note: Given that html2text seemingly invariably uses | as the column separator, the above will only work robustly if the are no | instances in the data being extracted.

^{[1] xidel -s --xquery 'for $tr in //tr[position()>1] return join($tr/td, " ")' file}

That's a great simplification, @Reino, thanks - I've added it to the answer. — mklement0, May 08 '22 at 12:57

score 0 · Answer 6 · answered Sep 15 '17 at 22:06

You can parse the file using Ex editor (part of Vim) by removing HTML tags, e.g.:

$ ex -s +'%s/<[^>]\+>/ /g' +'v/0/d' +'wq! /dev/stdout' table.html 
  SAVE_DOCUMENT  OK  0.406 s  
  GET_DOCUMENT  OK  0.332 s  
  DVK_SEND  OK  0.001 s  
  DVK_RECEIVE  OK  0.001 s  
  GET_USER_INFO  OK  0.143 s  
  NOTIFICATIONS  OK  0.001 s  
  ERROR_LOG  OK  0.001 s  
  SUMMARY_STATUS  OK  0.888 s

Here is shorter version by printing the whole file without HTML tags:

$ ex +'%s/<[^>]\+>/ /g|%p' -scq! table.html

Explanation:

%s/<[^>]\+>/ /g - Substitute all HTML tags into empty space.
v/0/d - Deletes all lines without 0.
wq! /dev/stdout - Quits editor and writes the buffer to the standard output.

score 0 · Answer 7 · answered May 07 '22 at 20:44

For the sake of completeness, pandoc does a good job when you have extracted the HTML table. For example,

pandoc --from html --to plain table.txt
  ---------------- -------- --------------
  Component        Status   Time / Error
  SAVE_DOCUMENT    OK       0.406 s
  GET_DOCUMENT     OK       0.332 s
  DVK_SEND         OK       0.001 s
  DVK_RECEIVE      OK       0.001 s
  GET_USER_INFO    OK       0.143 s
  NOTIFICATIONS    OK       0.001 s
  ERROR_LOG        OK       0.001 s
  SUMMARY_STATUS   OK       0.888 s
  ---------------- -------- --------------

How to extract data from html table in shell script?

7 Answers7

Linked

Related