Forum Discussion
Importing XML gives all data in single column
Hi,
I'm attempting to import data from an XML file we get exported from one of our systems. I have little experience with XML, so the error might be obvious. Excel opens it just fine and returns the following table, as expected with a bunch more columns (it exports info in Norwegian, sorry about that but not important for my issue)
When i attempt to import the same XML-file into PowerBI I run into trouble with all the information coming in a single column after expanding some columns.
The XML file looks like this (and keeps going)
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> -<Workbook xmlns:html="http(this part is secret)" xmlns:ss="(and this)" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:user="urn:my-scripts" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> -<Styles> -<Style ss:ID="s21"> <NumberFormat ss:Format="Fixed"/> </Style> </Styles> -<Worksheet ss:Name="Ark1"> -<Table ss:ExpandedRowCount="1724" ss:ExpandedColumnCount="33"> <Column ss:Width="70" ss:AutoFitWidth="0"/> <Column ss:Width="180" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="60" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="150" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="150" ss:AutoFitWidth="0"/> <Column ss:Width="70" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="90" ss:AutoFitWidth="0"/> <Column ss:Width="90" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> +<Row> +<Row> +<Row> +<Row> +<Row> +<Row> -<Row> -<Cell> <Data ss:Type="String">03</Data> </Cell> -<Cell> <Data ss:Type="String">BRAVIDA NORGE AS</Data> </Cell> -<Cell> <Data ss:Type="String">34940</Data> </Cell> -<Cell ss:StyleID="s21"> <Data ss:Type="Number">220000.00</Data>
Can anyone see where I'm going wrong? Does anyone have a good idea how to fix this? Tempted to say the XML is broken, but since excel handles it I can't really use that as en excuse either.
Any help would be much appreciated.
Regards
H
8 Replies
HaraldHBub,
Could you please upload the xml file to OneDrive and post shared link of the file here? The above XML file you post contains emoji.
Regards,
LydiaThere you go. This is a dummy XML as the other contains sensitive information. This is exported from the same system and should contain the same structure, just a much smaller dataset.
When I try to import and expand all my data comes in the Row query, Cell.Data.Element:Text column
Thank you!
Regards
Harald
HaraldHBub,
I am unable to access the XML file, please share it in OneDrive and post shared link of it so that I can download the file from my side.
Regards,
Lydia
