Nov 11 2020 05:09 AM
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
Nov 11 2020 08:35 AM
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
Nov 11 2020 12:01 PM
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
Nov 11 2020 12:14 PM
Solution@andersbegde @SergeiBaklan @NikolinoDE
I've tried @NikolinoDE '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 @NikolinoDE and @SergeiBaklan
Nov 11 2020 12:53 PM
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.
Feb 27 2024 07:28 AM
Hi,
while in the PowerQuery editor go to QuerySettings and click on the settings button in the first line "Source" (see picture below).
Select the File origin type that fits your file's code page.
Nov 11 2020 12:14 PM
Solution@andersbegde @SergeiBaklan @NikolinoDE
I've tried @NikolinoDE '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 @NikolinoDE and @SergeiBaklan