Excel import of text with leading zero in XML data

[tonygallo10]

Excel is converting a text string to a number. How do I force it to treat it
as text?
"06.2030" should NOT be converted to 6.203 during a read of the XML data.

<PAW_Items xmlnsaw="urn:schemas-peachtree-com/paw8.02-datatypes"
xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2000/10/XMLSchema-datatypes">
<PAW_Item xsi:type="paw:item">
<ID xsi:type="paw:id">06.2030</ID>
<Description>20/30 Walnut Shell</Description>
<Description_for_Sales>20/30 Walnut Shell</Description_for_Sales>
<Sales_Prices>
<Sales_Price_Info Key="1">
<Sales_Price>0.70000</Sales_Price>
</Sales_Price_Info>
</Sales_Prices> <Type>Abrasive</Type>
<Weight>0.00000</Weight>
<Vendor_ID xsi:type="paw:id">06 Grand Northern</Vendor_ID>
<CustomFields>
<CustomField>
<Description>Category Name 2 </Description>
<Value Index="1">Blasting</Value>
</CustomField>
</CustomFields></PAW_Item>
</PAW_Items>

 

[Harlan Grove]

Excel is converting a text string to a number. How do I force it to treat it
as text?
"06.2030" should NOT be converted to 6.203 during a read of the XML data.

<PAW_Items xmlnsaw="urn:schemas-peachtree-com/paw8.02-datatypes"
xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2000/10/XMLSchema-datatypes">
<PAW_Item xsi:type="paw:item">
        <ID xsi:type="paw:id">06.2030</ID>

....

The proper way to handle this requires YOU to get the specs for
PeachTree's paw schema and find out whether it includes an XML tag
attribute to treat particular values as strings or text. Then you'd
either need to figure out how to make PeachTree add that to the XML
file or add it after the fact with a stream editor.

A crude hack that might work would be adding an HTML nonbreaking
spaces between the ID values and the closing </ID> tags. That is,
replace

<ID xsi:type="paw:id">06.2030</ID>

with

<ID xsi:type="paw:id">06.2030 </ID>

The downside is that you'll have this character in your ID field
values. You could handle that in Excel with a macro, running it on all
cells containing text constants in the used range, and for each such
cell changing the number format to Text and stripping off the ending
HTML nonbreaking space. Something like

Sub foo()
Dim c As Range

With ActiveSheet.UsedRange
For Each c In .SpecialCells(xlCellTypeConstants, xlTextValues)
If Right$(c.Value2, 1) = Chr(160) Then
c.NumberFormat = "@"
c.Value2 = Left$(c.Value2, Len(c.Value2) - 1)
End If
Next c
End With

End Sub