How to populate leading 0's in excel sheet by using XML schema ?
@discussions.microsoft.com Pranay
basically use XML schema to pull data of XML file to Excel sheet. But I could
see in excel sheet leading 0' s are not getting populated in the excel sheet,
similarly if there large digit no..say 26 digit, the data that is populated
in sheet gets round off to nearest possible 26 digit. I have even tried to
make schema sheet as 'Text' but that doesn't help either. Please suggest any
solution. Thanks !!
Peter Jamieson
1. Leading zeroes
a. AFAIK Excel treats values that ilook like numbers as numbers, even
though in the Help it suggests that xsd:string type is treated as Text.
b. if your items with leading zeroes are fixed length (i.e. every item
in the column is going to have the same number of digits in it), then
you can format all the cells in the column using a Custom format that
just has the required number of zeroes - i.e. if the string lenght is 5,
use 00000
c. otherwise, you probably either have to be able to prepend each
value by a single quote before you do the import (and of course if that
increases the string length, it may mean that the value is not valid
according to the schema), or you have to import your data some other way
The trouble with the "single quote" approach is that Excel displays the
quote, which isn't helpful at all. Perhaps there's a way to get rid of
it if you need to.
2. truncation of long numbers.
If you need to use these numbers in calculations, you are stuck with
Excel's limitations on numeric precision etc.
If you just want to display them, I think you would need to import them
as text values, as in (1).
For (1), I assume you could use Excel VBA and MSXML to do the import a
different way where you have control of how each cell is populated, but
then you lose the simplicity of not haveing to do any coding.
Peter Jamieson