Excel (Unformatted) export - unintended parsing of leading zeroes

[Wei Wang]

For the newer Excel (Unformatted) export option, Apliqo automatically parses the leading zeroes. For example if we have a product dimension with an “000” element, “0” is exported. Perhaps this was the intention of the “unformatted” export but I imagine that treating metadata elements names as text when exporting would be a more universally useful behavior.

If the text/number distinction is part of the “formatting” that slows down the export of larger views (the use case for this unformatted export) I can see why this is working the way it is currently but please confirm. Thanks!

‌

‌

[Scott Wiltshire]

Hi Wei,

I'm 99.9% sure that this is coming from Excel trying to be helpful and converting cells which could contain numbers into numeric values (much like if you had a string in a cell "2024-05-15" then Excel would helpfully convert this into a value 45427 if the cell was unformatted.) And that is the issue here, Apliqo is exporting without any formatting set on any of the cells, including the filters, header rows and header columns. So "000" is in fact exported but Excel converts this to 0 on opening the file. If you had date strings in any cells you would also get the same behaviour.

The solution would be to modify the "unformatted" export to actually not be completely unformatted but to either

• format all filter, row header and column header cells as Text
• prefix the cell value of all filter, row header and column header cells with single quote ' during the export as this also instructs Excel to treat the cell contents as a string

‌

[Wei Wang]

yes I agree Scott that its probably Excel doing the auto-conversion and treating the export like a CSV. +1 to your proposed solution of having “light” formatting by applying “Text” format to the metadata