How to split imported table data from web page to Headers in first row, and linked data in second row

Question

When I use =transpose(concatenate(IMPORTXML(A1,F21))) all the table data headers & data) is imported into one cell.
Note: A1 is url:
https://wallmine.com/asx/a2m, and F21 is xPath: //div[@data-controller='companies--properties'].
How do I transpose this imported table data in cell C8, into two separate rows:

(A) Headers into row 1, across columns for the length of the data,
(B) Linked data into row 2 across columns for the length of the data.

I tried to transpose header row and data for one stock using the following formula:
=query(importxml(concatenate("https:/wallmine.com/asx/a2m","//div[@data-controller='companies--properties']"),"//td/tr[1]"),"Select Col1, Col2, Col3, Col4, Col 5, Col 6 LIMIT 1"), but it displays N/a, "Could not fetch URL: https:/wallmine.com/asx/a2m//div[@data-controller='companies--properties']".

I also attempted to get only the data row using:
=transpose(importxml(concatenate("https:/wallmine.com/asx/"&A2&"/'companies-properties'"),"//tr//td[2]")). But received the same error. note cell a2 has the ticker a2m. Both these two above attempts appear to start loading the data, then fails with the error. So what am I doing wrong here ?

I have also used the following function on the combined table data in cell C8=split(flatten(C8)," ,"), it places the headers and data in one row, but headers are placed in multiple columns in error, where a header has a space between header names. (i.e. Market cap has Market displayed in Col1 and cap in Col2. So this does not help me either.

Screen print of merged table date in cell C8, and list of data wanted: i.e. 6 columns by 21 rows.

Answer 1

You are using IMPORTXML to extra data from a website and the results are being aggregated in a single cell.

The reason for this is that the source is not an XML reference, it is HTML. The solution is to use IMPORTHTML. This returns the respective titles and values in adjacent columns.

Helper Columns

The table numbers for your site range from 1 to 17 inclusive (seventeen in total). I recommend using helper columns to "stack" each of the seventeen IMPORTHTML" formula. Then use two TRANSPOSE` formula to display the "header" column and the value column.

In the examples shown below, in the IMPORTHTML formula, the "table" index in Column A is referenced in the formula. For example:

• =importhtml("https://wallmine.com/asx/a2m","table",A2): Cell A2 has a value of 1.
• =importhtml("https://wallmine.com/asx/a2m","table",A4): Cell A4 has a value of 2.

---

---

The result of the helper columns looks like this; the title and value are in adjacent columns.

---

Full helper column data

---

Row-wise results

The row-wise values are obtained by using TRANSPOSE on the title and value columns respectively.

The result looks like this. There are 62 title/value combinations; this could be structured in a variety of ways to make the results more "readable".

---

Answer 2

Thanks for your reply. To reduce the the number of importhtml calls I would have make (ie 17 per stock, and create additional to tabs for stocks to stay under the 50 call limit, is it possible convert the html code for table to xml, then import the table data, with a Google sheet script, to enable the data to be imported, headers row 1 and data row 2.

For example could we use something like what is mentioned via this link: https://stackoverflow.com/questions/15179866/how-to-convert-this-html-table-to-xml ?