SOLVED

Importing XML into ExCel with PowerQuery, encoding not working.

%3CLINGO-SUB%20id%3D%22lingo-sub-1876331%22%20slang%3D%22en-US%22%3EImporting%20XML%20into%20ExCel%20with%20PowerQuery%2C%20encoding%20not%20working.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1876331%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20pretty%20big%20XML%20file%2C%20in%20which%20the%20encoding%20is%20UTF-8.%3C%2FP%3E%3CP%3EThere's%20a%20lot%20of%20occurences%20of%20special%20characters%20(%C3%A6%2C%C3%B8%2C%C3%A5)%2C%20which%20after%20import%20only%20shows%20up%20as%20as%20questionmark%20%22boxes%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsually%2C%20when%20importing%20with%20the%20same%20process%2C%20just%20with%20CSV-%26gt%3BEXCEL%2C%20I%20get%20an%20option%20in%20the%20%22Navigator%22%20to%20select%20the%20encoding%20I%20want.%20However%2C%20with%20the%20XML%20files%20I%20do%20not%20get%20that%20option.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20convert%20the%20encoding%20after%20I've%20imported%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1876331%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877175%22%20slang%3D%22de-DE%22%3ESubject%3A%20Importing%20XML%20into%20ExCel%20with%20PowerQuery%2C%20encoding%20not%20working.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877175%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F865313%22%20target%3D%22_blank%22%3E%40andersbegde%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENot%20sure%2C%20since%20I'm%20not%20the%20big%20**bleep**%20in%20XML%20(to%20put%20it%20mildly).%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EBut%20I%20remembered%20that%20someone%20converted%20the%20csv%20file%20into%20text%20editor%20and%20then%20imported%20it%20into%20Excel%20and%20then%20it%20worked.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Ebut%20all%20of%20this%20without%20guarantee.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EAs%20I%20said%2C%20I%20know%20that%20I%20don't%20know%20anything.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

I have a pretty big XML file, in which the encoding is UTF-8.

There's a lot of occurences of special characters (æ,ø,å), which after import only shows up as as questionmark "boxes".

 

Usually, when importing with the same process, just with CSV->EXCEL, I get an option in the "Navigator" to select the encoding I want. However, with the XML files I do not get that option.

 

Is there any way to convert the encoding after I've imported?

 

Thanks

4 Replies

@andersbegde 

Not sure, since I'm not the big **bleep** in XML (to put it mildly). But I remembered that someone converted the csv file into text editor and then imported it into Excel and then it worked. but all of this without guarantee. As I said, I know that I don't know anything.

 

Hope I was able to help you.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

@andersbegde 

Both Xml.Tables() and Xml.Document() have optional encoding parameter (second and third accordingly), you may add it in formula bar.

https://docs.microsoft.com/en-us/powerquery-m/xml-tables

https://docs.microsoft.com/en-us/powerquery-m/xml-document

 

Best Response confirmed by andersbegde (New Contributor)
Solution

@andersbegde @Sergei Baklan @Nikolino 

 

I've tried @Nikolino 's solution and it works.

What I did step by step was:

 

1. Open the file in Notepad++

2. Hit the "Encoding" tab and select "Convert to UTF-8-BOM".

3. Save as separate file (doesn't matter I guess but I wanted to keep the other as backup).

4. Open excel and import it normally via Data->Import From File (XML).

 

Worked exactly as expected.

 

Thanks for the replies both @Nikolino and @Sergei Baklan 

@andersbegde 

Great to know you solved this. However, from my point of view to add one parameter in formula bar is more direct and straightforward way.