Why do I get "Data at the root level is invalid. Line 1, position 1" for this file? about epplus

[swmal]

Hi @nmg196,

an xlsx file is just a zipped library of xml-files - to view the internal files of any *.xlsx file you can just rename the file from *.xlsx to *zip and open it.

The reason for the "Data at the root level is invalid" error is that the xml files inside this particular xlsx-file are encoded with UNICODE whereas EPPlus uses UTF-8 when reading them.

Since EPPlus assumes that the xml-files are encoded in UTF-8 the result of the file becomes like this:

��<\0?\0x\0m\0l\0 \0v\0e\0r\0s\0i\0o\0n\0=\0\"\01\0.\00\0\"\0?\0>\0\r\0\n\0 \0<\0T\0y\0p\0e\0s\0 \0x\0m\0l\0n\0s\0=\0\"\0h\0t\0t\0p\0:\0/\0/\0s\0c\0h\0e\0m\0a\0s\0.\0o\0p\0e\0n\0x\0m\0l\0f\0o\0r\0m\0a\0t\0s\0.\0o\0r\0g\0/\0p\0a\0c\0k\0a\0g\0e\0...

...and the "Data at the root level..." exception is thrown when EPPlus tries to load the xml-string into an XmlDocument.

The reason that it works when you have resaved the file with Excel is most likely converts the files to UTF-8.

I don't think that we have had this reported before, but we will have a look at it and see if we can handle this internally in EPPlus and provide a fix in a coming version.

With the current version EPPlus will unfortunately not be able to read these files.

[swmal]

@nmg196 - May I ask how your xlsx-files were created? By Excel or another tool? If Excel, do you know which version of it?

[nmg196]

The files are output by a widely used trading platform called MetaTrader 5 (https://www.metatrader5.com/). If this is an edge case, then perhaps I can extract and repack the files if I detect this error. However that said, Excel is able to open them OK which indicates this is a supported file encoding for XML/xlsx files.

[swmal]

I did some testing and we can add support for this in EPPlus by checking the BOM of the files and then process the internal files according to the format. I will add this as a feature to implement in one of the coming versions.

[nmg196]

That would be great :)