Forum Discussion
Excel Rich data types
Five years ago it was the case that Power Query could not access Excel Rich Data Types (eg Stock, Geography) directly.
Reading a table would result in "DataFormat.Error: Invalid cell value '#VALUE!'." in the column containing those values.
There is a workaround whereby the data from that column is extracted into another column. It will appear as text and could be read directly by Power Query.
While I can add the column in Excel, for appearance sake I'd rather not, but is there any method to allow Power Query to access that column directly?
Sample Data showing "Ticker" column in Excel as a stock data type
Reading table into PQ showing Error for that column
Yes, it is very complicated, and I don't think I can explain it to you in English (even with the help of translation software). It probably indexes the values in the following order:
- In the file xl/worksheets/sheet1.xml, find the vm attribute value of the c tag and go to the next step.
- In the file xl/metadata.xml, the rc tag under valueMetadata>bk has attributes t and v. According to t attribute value, go to the metadataTypes tag to determine the metadata type (find the name attribute value). Find the futureMetadata tag corresponding to the previous name, and then find the i attribute value of the xlrd:rvb tag under this tag.
- Each rv tag index under the file xl/richData/rdrichvalue.xml corresponds to the previous i attribute value. Each v tag in the rv tag just displays the value, and you also need to find the corresponding s tag under the file xl/richData/rdrichvaluestructure.xml through the s attribute value of the rv tag. Each k tag here corresponds to the v tag mentioned above.
It should be noted that when searching, some indexes start from 0 (such as the s attribute value under the rv tag), and some start from 1 (such as the vm attribute value).
I am very busy at this time. If I have time later, I think I will draw a more understandable diagram to explain this process.
4 Replies
This question is similar to reading hyperlinks from text in xlsx file, you need to read the original content of xlsx file.
If you plan to delve deeper, you need to read the ISO/IEC 29500 standard carefully.
ZhangKun wrote:
This question is similar to reading hyperlinks from text in xlsx file, you need to read the original content of xlsx file.
By that, do you mean getting directly into the zip archive which represents the excel file?
One of those zipped files is the only place I am seeing the relevant data.
Yes, it is very complicated, and I don't think I can explain it to you in English (even with the help of translation software). It probably indexes the values in the following order:
- In the file xl/worksheets/sheet1.xml, find the vm attribute value of the c tag and go to the next step.
- In the file xl/metadata.xml, the rc tag under valueMetadata>bk has attributes t and v. According to t attribute value, go to the metadataTypes tag to determine the metadata type (find the name attribute value). Find the futureMetadata tag corresponding to the previous name, and then find the i attribute value of the xlrd:rvb tag under this tag.
- Each rv tag index under the file xl/richData/rdrichvalue.xml corresponds to the previous i attribute value. Each v tag in the rv tag just displays the value, and you also need to find the corresponding s tag under the file xl/richData/rdrichvaluestructure.xml through the s attribute value of the rv tag. Each k tag here corresponds to the v tag mentioned above.
It should be noted that when searching, some indexes start from 0 (such as the s attribute value under the rv tag), and some start from 1 (such as the vm attribute value).
I am very busy at this time. If I have time later, I think I will draw a more understandable diagram to explain this process.
