Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yuezhe-msft
Employee
Employee

@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.

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

@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.

Strange.

 

See if this works

 

One drive link

@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.

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

 

 

 

@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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors