Read from a html file and paste part of content into a excel file

[srgunukula]

I have a html file(report.htm) which points to another html file(summary.html). the summary.html has some data and a table with multiple row entries.

I have to copy only the table (along with column names ) from summary.html and paste in the same order and column wise to an excel file.

The column size is fixed to 10 , but the number of rows some times may increase or decrease .
Column names are :
Transaction Name SLA Status Minimum Average Maximum Std. Deviation 90 Percent Pass Fail Stop


attaching report.htm and summary.html files here changing extension to .txt

No issue using python or shell or VB script. preferred is Python.

[Haluk]

You can use the built-in function of Excel to get the data from the local HTML file.

Data >> From Web

Just write the path of the local file as an URL...

Choose the table you want to fetch the data from and that's all...

.

[srgunukula]

Thanks Haluk.
This helps a lot. I have work to delete few rows ( not required), adjust few ROW values to Multiple row values and some needed appearance changes.

Is there a way to use python to call excel and and input a html file to get desired output excel file?

[srgunukula]

Is there a way to use python to call MS 2013 excel's Data->FromWeb and and input a html file to get desired output excel file?

[Haluk]

I haven't used python before, so I can not help you in that point.

But, you can use the below VBA code to get the contents of a specific table in local HTML file.

You can ammend the code to format the table, delete unwanted rows/columns to suit your needs by using VBA also.

I am not sure if you used VBA before but, open an Excel file and hit Alt+F11 to reach the VB editor. Insert a new module and paste the following code.

Then, run the macro below to select your summary.html file. The code will fetch the data from the table which its index is 10. Table indexes are zero based in HTML files.

I also attached a workbook for you where the below code is ready for you to use. Just open the Excel file and hit the button on the sheet to select your summary.html file and get the datas.

Please Login or Register  to view this content.

[Haluk]

Also ADO can be used to query the tables from a HTML file. In this case, you can get the columns that you choose from any of the tables and/or use some SQL statements to get some specific datas according to your query. This is an effective solution...

I tried this method as well but, couldn't get it work for your summary.html file given in message #1. Perhaps, the structure of the HTML file is not suitable for this to work. But, if you can somehow simplify your html file, this method will work.

.

[Haluk]

Well, after spending a few hours I managed to use ADO for your HTML file.

What I've done is; I removed the last table (given in the attached RemovedPart.txt) from the source code of your summary.html which was at the end of the file and renamed as summary2.html not to confuse the files.

So, the final Excel file and your HTML file are attached. In the Excel file, ADO is used for some sample queries where the alternatives are; to list all the datas in Table-6, get only some of the columns from the table and get data for a given criteria.

Rename the attached *.txt file as *.html, open the Excel file and hit the button on the sheet. The files should be in the same folder.

.

[buran]

You can post your python related question here:
https://www.python-forum.io/
Note that the forum culture there is different - you would be expected to put effort. No one will do everything for you like it often happens here.
It's definitely possible to do this with python.

As to the Excel/VBA solution - the easiest is to import the desired table with Data->From Web (the process can be manual or automated with VBA)