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

@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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors