Importing XML files into excel with numbers of over 15 digits

Question

I have an xml file which I am trying to import into excel but the most important column being imported has 18 digits and excel only allows up to 15 digits.

I have tried setting the column to text after importing but the last 3 digits are just 000. i can't work with these numbers as every digit is important as i'm dealing with barcodes.

I'm trying to think of a way I can preset the column to be text so when I'm importing it will be text immediately. Any ideas or suggestions on what I could do?

thanks.

Answer 1

Say you have an XML doc like this

<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <number>12345678901234567890</number>

Create an XSD doc like this

<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="number" type="xs:string" /> </xs:schema>

You've defined an element with the name of "number" and a string data type. Now any element in your XML file that's in "number" tags will be string formatted.

In a workbook, go to the Developer tab, XML group, and click Source. This shows the XML task pane. Here you'll add the xsd file. Click XML Maps then Add and load your xsd file.

Now your task pane will show your map - we only have one element, so it's pretty simple

Next, drag the number element to a cell in your workbook. There won't be any data yet because we haven't imported the xml.

Last, go back to Developer - XML and click Import. Import your xml file. Excel will map all the elements to the cells where you defined elements. Even Excel now recognizes that it's treating a number as test. But no truncation.

Answer 2

Alternative solution without having to create your own XSD Doc

1. Import XML File via Developer Tools and let Excel automatically generate the Schema as you did before XML Import
2. Save your file as an XML Spreadsheet Save as XML Spreadsheet and close it afterwards
3. Open this XML spreadsheet with an editor, e.g. Notepad / Notepad++ and search for "MapInfo" (should be towards the end of the file) Search XML Spreadsheet
4. Change the "XSDType" of the field you want to change to "string" Change XSD Type and save
5. Open the changed XML Spreadsheet with Excel and save it as .xlsx Save XML as XLSX
6. Open your newly saved .XLSX and import your XML file again Result