Opening XML using EXCEL (values wrongly imported)

Question

Hello,

I am experiencing that XML files opened with Excel, values are differently display

For example when I open XML file using Excel value in XBLNR column is ending with "0"

but when I open same file using Notepad from Microsoft then the same value ends with "1".

This is picture from Excel and as You can see the value is "2960036700122050"

This is screenshot from Notepad and here the value ends with "1" not "0" "2960036700122051"

My question is, why is Excel importing modified values.

Some people told me that it is bug in Excel.

Answer 1

Dear Zvonimir,

Thanks for your reply.

The share link is not an anyone link so we couldn't access the file. If you could share it with anyone link will be great help.

Sincerely,

Sean Cai | Microsoft Community Moderator

Answer 2

Hello,

I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2206 Build 16.0.15330.20260) 32-bit.

You can download test file on this link download link

Thank you,

Zvonimir

#PII Masked by forum moderator#

Answer 3

Dear Zvonimir,

I apologize for misunderstanding your description, may I confirm the Excel version? You could check it through File>Account.

Also, if you could share an XML file(you could make a simple test file) will be great help, we could test on our side and see if we could repro this behavior.

Thanks again.

Sincerely,

Sean Cai | Microsoft Community Moderator

Answer 4

Dear Sean,

its is not the problem in number formating.

If I would change from General to Number format, the XBLNR value will still be incorrect ( with "0" as last digit ).

Correct value for XBLNR is "2960036700122051"

Problem is that Excel is changing base value from XML file.

In original XML file, when I open it with Notepad or with any online XML viewer, XBLNR value is "2960036700122051"

but when I open that same file using Excel, Excel for some reason "replaces" that last digit which is number "1" from XBLNR

with "0".

And because of that "replace" original XBLNR value is changed (different) so this invalidates the accuracy of the data.

PLUS I forgot to mention, XBLNR is string type data.

Have a good day.

Answer 5

Dear Zvonimir,

Thanks for posting in Microsoft community.

You could try to set this option from General to Number. For large numbers, Excel will change it to scientific notation.

If this method doesn't work in your scenario, please post back and I will help you.

Thanks for your cooperation, look forward to your reply.

Have a good day.

Sincerely,

Sean Cai | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***