+ Reply to Thread
Results 1 to 8 of 8

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

  1. 07-15-2019, 05:21 AM #1
    srgunukula
    srgunukula is offline
    Registered User
    Join Date
    07-15-2019
    Location
    Bangalore
    MS-Off Ver
    2013
    Posts
    3

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

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-15-2019, 11:33 AM #2
    Haluk
    Haluk is offline
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,147

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

    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...

    .
    Last edited by Haluk; 07-15-2019 at 11:38 AM.
    Reply With QuoteRegister To Reply
  3. 07-16-2019, 01:08 AM #3
    srgunukula
    srgunukula is offline
    Registered User
    Join Date
    07-15-2019
    Location
    Bangalore
    MS-Off Ver
    2013
    Posts
    3

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

    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?
    Reply With QuoteRegister To Reply
  4. 07-16-2019, 02:48 AM #4
    srgunukula
    srgunukula is offline
    Registered User
    Join Date
    07-15-2019
    Location
    Bangalore
    MS-Off Ver
    2013
    Posts
    3

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

    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?
    Reply With QuoteRegister To Reply
  5. 07-16-2019, 03:14 AM #5
    Haluk
    Haluk is offline
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,147

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

    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.
    Attached Files Attached Files
    Last edited by Haluk; 07-16-2019 at 03:30 AM.
    Reply With QuoteRegister To Reply
  6. 07-16-2019, 04:01 PM #6
    Haluk
    Haluk is offline
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,147

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

    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.

    .
    Reply With QuoteRegister To Reply
  7. 07-16-2019, 05:32 PM #7
    Haluk
    Haluk is offline
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,147

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

    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.

    .
    Attached Files Attached Files
    Last edited by Haluk; 07-16-2019 at 05:49 PM.
    Reply With QuoteRegister To Reply
  8. 07-19-2019, 06:30 AM #8
    buran
    buran is offline
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

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

    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)
    Last edited by buran; 07-19-2019 at 06:33 AM.
    If you are pleased with a member's answer then use the Star icon to rate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Save as a temporary file and paste automatically once destination file is not read only
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2019, 03:35 AM
  2. Macro for paste from html or txt file into excel
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2018, 12:38 AM
  3. Read Embedded text file content through Excel VBA
    By mrleokarthik in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-13-2016, 02:35 PM
  4. .xls extenstion not working where file content is HTML
    By stevie_harvey in forum Excel General
    Replies: 0
    Last Post: 07-27-2016, 06:49 AM
  5. macro to copy used range and paste in the body tag of a html file/text file
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2014, 08:34 PM
  6. Macro to copy the range of values from Excel and paste in html file
    By harishpeddi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2014, 10:50 AM
  7. Parse Part of File name and paste into excel
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-15-2010, 06:12 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1