Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.


Extract the tables from html


Login or Register to Reply

 
Thread Tools Search this Thread
# 8  
Quote:
Originally Posted by deepti01
i am just learning to do it . ...
Doesn't really answer Neo's question. Please explicitly tell us HOW and WHERE you learn to do it. What is your motivation to do so?



I see you incorporated but commented out my recent proposal to extract the second table from the HTML file. What keeps you from adapting it to your needs?

And, why don't you go for doing all of your task in one single script, e.g. awk?

RavinderSingh13 asked you to post "samples of input and expected output". If done, you could get hints on how to do it optimally.
# 9  
Hi,

The end use if this task is to dump the data into the database through Informatica.
So for that I am converting the HTML files to .csv file.

Sample input : i have already placed the html code . that is my sample file (.html)

Sample output should be like:

Code:
No    TABLE NAME    Exported Rows    Imported Rows    Diff Rows
1    NAME_DATA    24760        Not exist on Imp
2    WHITE_LIST_MIG    12912        Not exist on Imp
1    ADDRESS_DATA    13753    13753    0
2    ADDRESS_NAME_LINK    68715    68715    0
3    AGREEMENT    0    0    0
4    AGREEMENT_RESOURCE    29979    29979    0
5    AGR_RES_HISTORY    0    0    0
 6    AR1_ACCOUNT    12912    12912    0





Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 03-14-2019 at 06:10 AM.. Reason: Added CODE tags.
# 10  
Please note that awk is NOT an adequate tool (nor is shell) to analyse / extract html data; there are way more apt tools to do this.
Please note as well that none of your files is valid html.
And, "csv" means "comma separated values", so I used commas for the extracted datas' separator.

Howsoever, for this - and exactly this - simple case, try


Code:
awk '
/<TABLE/        {P = 1
                }
/<\/TABLE/      {P = 0
                }
!P              {next
                }
/<[Tt][Rr]>/    {printf ORS
                 DL = ""
                }
/<td/           {gsub (/<[^>]*>/, "")
                 gsub (/^ *| *$/, "")
                 printf "%s%s", DL, $0
                 DL = ","
                }
' file

No,ExpSchemae,ExportTables,ImpSchema,ImportTables,Diff
1,FVT4,54,PRDCUSTO,54,
1,FVT4,56,AllImpSchema,54,2
No,TABLENAME,ExportedRows,ImportedRows,DiffRows
1,NAME_DATA,24760,,NotexistonImp
2,WHITE_LIST_MIG,12912,,NotexistonImp
No,TABLENAME,ExportedRows,ImportedRows,DiffRows
1,ADDRESS_DATA,13753,13753,0
2,ADDRESS_NAME_LINK,68715,68715,0
3,AGREEMENT,0,0,0
4,AGREEMENT_RESOURCE,29979,29979,0
5,AGR_RES_HISTORY,0,0,0
6,AR1_ACCOUNT,12912,12912,0
.
.
.


Last edited by RudiC; 03-14-2019 at 07:12 AM..
This User Gave Thanks to RudiC For This Post:
# 11  
Hi.

There are some codes that understand HTML, such as lynx, elinks. For example, here is elinks extracting all the stuff in the sample HTML, and then, step-by-step, tables are refined, with sed, grep, etc., into a CSV file:
Code:
#!/usr/bin/env bash

# @(#) s1       Demonstrate table extraction from HTML.

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
LC_ALL=C ; LANG=C ; export LC_ALL LANG
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
em() { pe "$*" >&2 ; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C specimen elinks grep dixf

FILE=${1-data1}

# Utility functions: print-as-echo, print-line-with-visual-space.
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }

pl " Input data file $FILE:"
specimen 5:5:5 $FILE

pl " Results:"
elinks -dump $FILE |
tee t1 |
grep -v '^  *[|+-]-' |
tee t2 |
grep '^  *[|]' |
tee t3 |
sed -e 's/^  *[|]//' -e 's/[|]$//' |
tee t4 |
sed 's/  *[|]/,/g' |
tee t5 |
specimen 5:5:5

pl " Samples of intermediate output:"
head -5 t[1-5]

pl " Some details for codes:"
dixf elinks specimen grep

exit 0

producing:
Code:
$ ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.16.0-7-amd64, x86_64
Distribution        : Debian 8.11 (jessie) 
bash GNU bash 4.3.30
specimen (local) 1.17
ELinks 0.12pre6
grep (GNU grep) 2.20
dixf (local) 1.57

-----
 Input data file data1:
Edges: 5:5:5 of 295 lines in file "data1"
<html>
<body>
<b><br>Running Date: </b>11-JAN-2019 03:07</br>
<h2> Schema mapping and info    </h2>
<BR><TABLE  width="100%" class="x1h" cellpadding="1" cellspacing="0" border="5">
   ---
<b><td class="x3w" bgcolor="#BDD7EE">15 </TD>
<b><td class="x3w" bgcolor="#BDD7EE">AR1_JGL_CONTROL  </TD>
<b><td class="x3w" bgcolor="#BDD7EE">0 </TD>
<b><td class="x3w" bgcolor="#BDD7EE">0 </TD>
<b><td class="x3w" bgcolor="#BDD7EE">0 </TD></TR>
   ---
<b><td class="x3w" bgcolor="#BDD7EE">BL9_PROVINCIAL_PCP  </TD>
<b><td class="x3w" bgcolor="#BDD7EE">18259 </TD>
<b><td class="x3w" bgcolor="#BDD7EE">18259 </TD>
<b><td class="x3w" bgcolor="#BDD7EE">0 </TD></TR>
<tr>

-----
 Results:
Edges: 5:5:5 of 46 lines in file "-"
No,Exp Schema e,Export Tables,Imp Schema,Import Tables,Diff
1,FVT4,54,PRDCUSTO,54,    
1,FVT4,56,All Imp Schema,54,2   
No,TABLE NAME,Exported Rows,Imported Rows,Diff Rows        
1,NAME_DATA,24760,,Not exist on Imp 
   ---
15,AR1_JGL_CONTROL,0,0,0             
16,AR1_PAYMENT,8439,8439,0             
17,AR1_PAYMENT_DETAILS,8439,8439,0             
18,AR1_PAY_CHANNEL,12912,12912,0             
19,AR1_PROOF_AND_BALANCE,4,4,0             
   ---
35,BL1_PAY_CHANNEL,12912,12912,0             
36,BL1_RC_RATES,30928,30928,0             
37,BL1_TAX,0,0,0             
38,BL1_TAX_ITEM,0,0,0             
39,BL9_PROVINCIAL_PCP,18259,18259,0             

-----
 Samples of intermediate output:
==> t1 <==
   Running Date: 11-JAN-2019 03:07

Schema mapping and info

   +------------------------------------------------------------------------+

==> t2 <==
   Running Date: 11-JAN-2019 03:07

Schema mapping and info

   |No |Exp Schema e   |Export Tables  |Imp Schema     |Import Tables  |Diff|

==> t3 <==
   |No |Exp Schema e   |Export Tables  |Imp Schema     |Import Tables  |Diff|
   |1  |FVT4           |54             |PRDCUSTO       |54             |    |
   |1  |FVT4           |56             |All Imp Schema |54             |2   |
   |No  |TABLE NAME      |Exported Rows   |Imported Rows  |Diff Rows        |
   |1   |NAME_DATA       |24760           |               |Not exist on Imp |

==> t4 <==
No |Exp Schema e   |Export Tables  |Imp Schema     |Import Tables  |Diff
1  |FVT4           |54             |PRDCUSTO       |54             |    
1  |FVT4           |56             |All Imp Schema |54             |2   
No  |TABLE NAME      |Exported Rows   |Imported Rows  |Diff Rows        
1   |NAME_DATA       |24760           |               |Not exist on Imp 

==> t5 <==
No,Exp Schema e,Export Tables,Imp Schema,Import Tables,Diff
1,FVT4,54,PRDCUSTO,54,    
1,FVT4,56,All Imp Schema,54,2   
No,TABLE NAME,Exported Rows,Imported Rows,Diff Rows        
1,NAME_DATA,24760,,Not exist on Imp 

-----
 Some details for codes:

elinks  lynx-like alternative character mode WWW browser (man)
Path    : /usr/bin/elinks
Version : 0.12pre6
Type    : ELF 64-bit LSB shared object, x86-64, version 1 ( ...)
Repo    : Debian 8.11 (jessie) 
Home    : http://elinks.cz/ (pm)

specimen        Extract, display sections, sample, provide taste of file. (what)
Path    : ~/bin/specimen
Version : 1.17
Length  : 334 lines
Type    : Perl script, ASCII text executable
Shebang : #!/usr/bin/env perl
Modules : (for perl codes)
 warnings       1.23
 strict 1.08
 English        1.09
 Carp   1.3301
 Data::Dumper   2.151_01
 Getopt::Long   2.42
 feature        1.36_01
 Regexp::Common 2013031301
 Algorithms::Numerical::Sample  1.1

grep    print lines matching a pattern (man)
Path    : /bin/grep
Version : 2.20
Type    : ELF 64-bit LSB shared object, x86-64, version 1 ( ...)
Repo    : Debian 8.11 (jessie) 
Home    : http://www.gnu.org/software/grep/ (pm)

The intermediate sample show the transformations that are done, and could, of course, probably be collected into an awk or perl script.

Best wishes ... cheers, drl
This User Gave Thanks to drl For This Post:
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. HP-UX

Unable to send attachment with html tables in UNIX shell script

Heyy, any help would be grateful.... LOOKING FOR THE WAYS TO SEND AN EMAIL WITH ATTACHMENT & HTML TABLES IN BODY THROUGH SHELL SCRIPT (LINUX)..NOT SURE, IF WE HAVE ANY INBUILT HTML TAG OR UNIX COMMAND TO SEND THE ATTACHMENTS. KINDLY HELP below is small script posted for our understanding..... (2 Replies)
Discussion started by: Harsha Vardhan
2 Replies

2. Shell Programming and Scripting

Splitting csv into 3 tables in html file

I have the data in csv in 3 tables. how can I output the same into 3 tables in html.also how can I set the width. tried multiple options . attached is the format. #!/bin/ksh awk 'BEGIN{ FS="," print "<HTML><BODY><TABLE border = '1' cellpadding=10 width=100>" print... (7 Replies)
Discussion started by: archana25
7 Replies

3. Shell Programming and Scripting

Awk/sed HTML extract

I'm extracting text between table tags in HTML <th><a href="/wiki/Buick_LeSabre" title="Buick LeSabre">Buick LeSabre</a></th> using this: awk -F "</*th>" '/<\/*th>/ {print $2}' auto2 > auto3 then this (text between a href): sed -e 's/\(<*>\)//g' auto3 > auto4 How to shorten this into one... (8 Replies)
Discussion started by: p1ne
8 Replies

4. UNIX for Dummies Questions & Answers

Extract table from an HTML file

I want to extract a table from an HTML file. the table starts with <table class="tableinfo" and ends with next closing table tag </table> how can I do this with awk/sed... ---------- Post updated at 04:34 PM ---------- Previous update was at 04:28 PM ---------- also I want to... (4 Replies)
Discussion started by: koutroul
4 Replies

5. Shell Programming and Scripting

awk to create two HTML Tables

I am working on awk script to generate an HTML format output. With input file as below I am able to generate a HTML file however I want to saperate spare devices in a different table than rest of the devices and which has only Bunch ID, RAW Size and "Bunch Spare" status columns. INPUT File : ... (2 Replies)
Discussion started by: dynamax
2 Replies

6. Shell Programming and Scripting

How to extract url from html page?

for example, I have an html file, contain <a href="http://awebsite" id="awebsite" class="first">website</a>and sometime a line contains more then one link, for example <a href="http://awebsite" id="awebsite" class="first">website</a><a href="http://bwebsite" id="bwebsite"... (36 Replies)
Discussion started by: 14th
36 Replies

7. AIX

Extract data from DB2 tables and FTP it to outside company's firewall

Please help me in creating the script in AIX. requirement is; The new componentís main function is to extract the data from DB2 tables and company's firewall directly. The component function needs to check the timestamp in the DB2 tables ((CREDAT and CRETIM) with the requested timestamp and... (1 Reply)
Discussion started by: priyanka3006
1 Replies

8. UNIX for Advanced & Expert Users

sed to extract HTML content

Hiya, I am trying to extract a news article from a web page. The sed I have written brings back a lot of Javascript code and sometimes advertisments too. Can anyone please help with this one ??? I need to fix this sed so it picks up the article ONLY (don't worry about the title or date .. i got... (2 Replies)
Discussion started by: stargazerr
2 Replies

9. UNIX for Dummies Questions & Answers

extract data from html tables

hi i need to use unix to extract data from several rows of a table coded in html. I know that rows within a table have the tags <tr> </tr> and so i thought that my first step should be to to delete all of the other html code which is not contained within these tags. i could then use this method... (8 Replies)
Discussion started by: Streetrcr
8 Replies

10. UNIX for Dummies Questions & Answers

How do I extract text only from html file without HTML tag

I have a html file called myfile. If I simply put "cat myfile.html" in UNIX, it shows all the html tags like <a href=r/26><img src="http://www>. But I want to extract only text part. Same problem happens in "type" command in MS-DOS. I know you can do it by opening it in Internet Explorer,... (4 Replies)
Discussion started by: los111
4 Replies

Featured Tech Videos