cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HaraldHBub
Frequent Visitor

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)

Skjermbilde.JPG

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.

Skjermbilde1.JPGSkjermbilde2.JPG

 

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 8
Highlighted
Microsoft
Microsoft

Re: Importing XML gives all data in single column

@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,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HaraldHBub
Frequent Visitor

Re: Importing XML gives all data in single column

One Drive link to XML

 

There 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

Microsoft
Microsoft

Re: Importing XML gives all data in single column

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HaraldHBub
Frequent Visitor

Re: Importing XML gives all data in single column

Strange.

 

See if this works

 

One drive link

Microsoft
Microsoft

Re: Importing XML gives all data in single column

@HaraldHBub,

I get the following result when using Data->From Other Sources->From XML Data Import option in Excel. When I using Data->New Query-> From file-> From XML option Excel  to import the data, I get same result as that in Power BI Desktop.

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HaraldHBub
Frequent Visitor

Re: Importing XML gives all data in single column

I get the same result as you using the data -> from other sources -> XML option, but i get the following result using the file -> open option, which is possible to work with and what i was hoping to get to in Power BI

Skjermbilde3.JPG

 

 

 

Microsoft
Microsoft

Re: Importing XML gives all data in single column

@HaraldHBub,

I am not sure that how Excel parse the above result, and I haven't found solutions to get same result after importing XML file in Power BI Desktop.

In your scenario, I would recommend you save the xml file as .xlsx file after you open it in Excel,  then directly connect to the Excel file in Power BI Desktop.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HaraldHBub
Frequent Visitor

Re: Importing XML gives all data in single column

@v-yuezhe-msft

 

Yeah, that is the workaround I had to use, but it does give some issues with doing several exports and automating the import though. However thank you for trying 🙂

 

Regards

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors