Load *.htm file saved as .xls (starting from row number 5) Using Power Query

Question

I have to import an .xls file which is saved as .*htm, .*html. I attached a link which provides a sample file of that format.

The Actual first row of the table starts from row number 5. But there are data above it.

The file looks as below,

The sample file.

But please make sure to include some rows on top of it with some test values and make it look like the screenshot above.

if there are no rows above it, then the following M Code provided by Alexis Olson works

let
    Source = Folder.Files("C:\Users\aolson\Downloads\example-html.xls"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
    #"C:\Users\aolson\Downloads\example-html xls\_example-html xls" = #"Filtered Rows"{[#"Folder Path"="C:\Users\aolson\Downloads\example-html.xls\",Name="example-html.xls"]}[Content],
    #"Imported Excel" = Web.Page(#"C:\Users\aolson\Downloads\example-html xls\_example-html xls"){0}[Data]
in
    #"Imported Excel"

When I add rows on top of the sample and click on save in excel - it gives me a warning whether, I want to continue with the same format then I click on "YES".

I tried to play with the children table on the Query Editor. But it is not taking me anywhere.

Source = Table cannot be found inside it at all.

When I added those extra rows, the Excel.Workbook (instead of Web.Page) worked for me until I tried adding text to cell B3 too, in which case it broke. — Alexis Olson
Oh God..Excel is so weird. Don't add any rows. don't add new data. just open the file and click on save and click on "yes" to continue with the same format. It fails with Web.Page function. @AlexisOlson — Siddharth Thanga Mariappan
Yeah. It really is weird. What I was saying though is if I add that extra bit neither option works and it's probably replicating the behavior of your actual non-sample files. — Alexis Olson
@AlexisOlson Yes I am thinking the same. That's why, those files didn't work for me when I tried to read it as HTML. So, fixing this should fix that. — Siddharth Thanga Mariappan
I've also noticed that if I open it up in Notepad, I can't find any of the cell data anywhere in the text. It's just a bunch of functions and variables. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-05T15:26:43

For whatever reason, the HTML in the sample file has unmatched tags that the XML parser doesn't like. You can get at the data though with some work if you load it as text and remove or fix any parts that the parser has trouble with.

Consider this M code:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\aolson\Downloads\example-html.xls\example-html.xls"))}),
    #"Kept Range of Rows" = Table.Range(Source,60,22),
    Column1 = Text.Combine(#"Kept Range of Rows"[Column1]),
    #"Parsed XML" = Xml.Tables(Column1),
    Table = #"Parsed XML"{0}[Table],
    #"Expanded td" = Table.ExpandTableColumn(Table, "td", {"i", "b", "span", "Element:Text"}, {"td.i", "td.b", "td.span", "td.Element:Text"}),
    #"Expanded td.span" = Table.ExpandTableColumn(#"Expanded td", "td.span", {"Element:Text", "Attribute:style"}, {"td.span.Element:Text", "td.span.Attribute:style"})
in
    #"Expanded td.span"

The steps here are roughly:

Load the file as text
Select just the <tbody> section.
Concatenate those rows into a single text value.
Parse that text as XML.
Expand any tables that are found.

When I initially did this I noticed the <b> tag wasn't closed so I added a </b> in my source file.

The results are a bit ugly, but I suspect if your actual data files don't include much formatting or inconsistent table structure, then you can get something along these lines working passably well, especially if you only have a single column to deal with.

Load *.htm file saved as .xls (starting from row number 5) Using Power Query

1 Answers