Editting a XML file in Excel, then exporting it back as XML

Question

Editting a XML file in Excel, then exporting it back as XML

Franca, Rafael 0

Is it possible to recover the original XML file after importing it to Excel, even if no changes are made to it?

If I right click the table generated after importing it and then go to XML -> Export... I get the error "Cannot save or export XML data. The XML maps in this workbook are not exportable".

Some Google search suggests that some valid XML files can't be exported by Excel. I may be missing something, since I wasn't able to export even this sample XML below, which I've got from Excel internal help page.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root>
  <EmployeeInfo>
    <Name>Jane Winston</Name>
    <Date>2001-01-01</Date>
    <Code>0001</Code>
  </EmployeeInfo>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Airfare</Description>
    <Amount>500.34</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Hotel</Description>
    <Amount>200</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Taxi Fare</Description>
    <Amount>100.00</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Long Distance Phone Charges</Description>
    <Amount>57.89</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Food</Description>
    <Amount>82.19</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Food</Description>
    <Amount>17.89</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Personal Items</Description>
    <Amount>32.54</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Taxi Fare</Description>
    <Amount>75.00</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Food</Description>
    <Amount>36.45</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>New Suit</Description>
    <Amount>750.00</Amount>
  </ExpenseItem>
</Root>

B santhiswaroop naik 405 Reputation points

2023-07-19T17:50:19.22+00:00

In general, when you import an XML file into Microsoft Excel, it creates a structured table for the XML data. By default, when you save or export this workbook after importing the XML data, Excel saves it in the XLSX format, which doesn't preserve the original XML structure.

When you try to export the imported XML data from Excel, you might encounter the error "Cannot save or export XML data. The XML maps in this workbook are not exportable." This error occurs because Excel cannot preserve the original XML structure, and the exported XML would not be the same as the original XML file.

If you want to preserve the original XML structure without any changes, it's better to keep the original XML file separate from Excel. You can store it in a folder on your computer or a document management system.

Alternatively, if you need to import XML data into Excel for analysis and then export it back to the original XML structure, you will need to use Excel VBA or a custom script to achieve that. By using VBA or a script, you can programmatically read the data from the Excel table and generate an XML file with the original structure.

Remember that when working with XML data in Excel, it's essential to handle it carefully to avoid unintended changes and to ensure that you can recover the original XML structure if needed.

--please don't forget to upvote and Accept as answer if the reply is helpful--
Franca, Rafael 0 Reputation points

2023-07-19T18:03:03.28+00:00

While I understand these limitations do exist, in what conditions does the Export button under Developer -> XML work? If I click it, I get the same error mentioned above.

Can that button export back to XML the sample in the question?
B santhiswaroop naik 405 Reputation points

2023-07-19T18:07:31.2133333+00:00

The "Export" button under Developer -> XML in Microsoft Excel is used to export XML data to an XML data file. This feature is designed to export XML data that has been imported into Excel using the "Developer -> XML" functionality or other XML data import methods. It allows users to save the data in an XML format that can be used for further processing or sharing with other applications.

However, the "Export" button is not meant to export any XML data that was not previously imported into Excel. It does not provide a generic way to export arbitrary XML data to an XML file. Therefore, trying to export XML data that was manually entered or copied into Excel will likely result in an error, as you mentioned.

In the specific case of the sample XML you provided, it was not imported into Excel using the XML import functionality. It was manually entered as text in the worksheet. Therefore, attempting to export it using the "Export" button will not work, and you will encounter the error.

If you want to export the sample XML data to an XML file, you will need to use other methods outside of Excel, such as saving the sample XML data directly to a text file with a ".xml" extension using a text editor or a specialized XML editor.

To summarize, the "Export" button in Excel is intended for exporting previously imported XML data, and it won't work for arbitrary XML data entered manually. If you need to save XML data that was not imported into Excel, you'll have to use other methods for exporting it to an XML file.

--please don't forget to upvote and Accept as answer if the reply is helpful--
Franca, Rafael 0 Reputation points

2023-07-19T18:12:38.0066667+00:00

That's precisely the problem. I saved the sample XML to a XML file, imported it into excel using the XML import functionality under the Developer tab, and even so I get the error I've mentioned. I didn't enter it as text in the worksheet.

Any ideas?

1 answer

Your answer

B santhiswaroop naik 405 Reputation points

2023-07-19T17:50:19.22+00:00

In general, when you import an XML file into Microsoft Excel, it creates a structured table for the XML data. By default, when you save or export this workbook after importing the XML data, Excel saves it in the XLSX format, which doesn't preserve the original XML structure.

When you try to export the imported XML data from Excel, you might encounter the error "Cannot save or export XML data. The XML maps in this workbook are not exportable." This error occurs because Excel cannot preserve the original XML structure, and the exported XML would not be the same as the original XML file.

If you want to preserve the original XML structure without any changes, it's better to keep the original XML file separate from Excel. You can store it in a folder on your computer or a document management system.

Alternatively, if you need to import XML data into Excel for analysis and then export it back to the original XML structure, you will need to use Excel VBA or a custom script to achieve that. By using VBA or a script, you can programmatically read the data from the Excel table and generate an XML file with the original structure.

Remember that when working with XML data in Excel, it's essential to handle it carefully to avoid unintended changes and to ensure that you can recover the original XML structure if needed.

--please don't forget to upvote and Accept as answer if the reply is helpful--
Franca, Rafael 0 Reputation points

2023-07-19T18:03:03.28+00:00

While I understand these limitations do exist, in what conditions does the Export button under Developer -> XML work? If I click it, I get the same error mentioned above.

Can that button export back to XML the sample in the question?
B santhiswaroop naik 405 Reputation points

2023-07-19T18:07:31.2133333+00:00

The "Export" button under Developer -> XML in Microsoft Excel is used to export XML data to an XML data file. This feature is designed to export XML data that has been imported into Excel using the "Developer -> XML" functionality or other XML data import methods. It allows users to save the data in an XML format that can be used for further processing or sharing with other applications.

However, the "Export" button is not meant to export any XML data that was not previously imported into Excel. It does not provide a generic way to export arbitrary XML data to an XML file. Therefore, trying to export XML data that was manually entered or copied into Excel will likely result in an error, as you mentioned.

In the specific case of the sample XML you provided, it was not imported into Excel using the XML import functionality. It was manually entered as text in the worksheet. Therefore, attempting to export it using the "Export" button will not work, and you will encounter the error.

If you want to export the sample XML data to an XML file, you will need to use other methods outside of Excel, such as saving the sample XML data directly to a text file with a ".xml" extension using a text editor or a specialized XML editor.

To summarize, the "Export" button in Excel is intended for exporting previously imported XML data, and it won't work for arbitrary XML data entered manually. If you need to save XML data that was not imported into Excel, you'll have to use other methods for exporting it to an XML file.

--please don't forget to upvote and Accept as answer if the reply is helpful--
Franca, Rafael 0 Reputation points

2023-07-19T18:12:38.0066667+00:00

That's precisely the problem. I saved the sample XML to a XML file, imported it into excel using the XML import functionality under the Developer tab, and even so I get the error I've mentioned. I didn't enter it as text in the worksheet.

Any ideas?

Answer 1

B santhiswaroop naik 405

The "Export" button under Developer -> XML in Microsoft Excel is used to export XML data to an XML data file. This feature is designed to export XML data that has been imported into Excel using the "Developer -> XML" functionality or other XML data import methods. It allows users to save the data in an XML format that can be used for further processing or sharing with other applications.

However, the "Export" button is not meant to export any XML data that was not previously imported into Excel. It does not provide a generic way to export arbitrary XML data to an XML file. Therefore, trying to export XML data that was manually entered or copied into Excel will likely result in an error, as you mentioned.

In the specific case of the sample XML you provided, it was not imported into Excel using the XML import functionality. It was manually entered as text in the worksheet. Therefore, attempting to export it using the "Export" button will not work, and you will encounter the error.

If you want to export the sample XML data to an XML file, you will need to use other methods outside of Excel, such as saving the sample XML data directly to a text file with a ".xml" extension using a text editor or a specialized XML editor.

To summarize, the "Export" button in Excel is intended for exporting previously imported XML data, and it won't work for arbitrary XML data entered manually. If you need to save XML data that was not imported into Excel, you'll have to use other methods for exporting it to an XML file.

--please don't forget to upvote and Accept as answer if the reply is helpful--

B santhiswaroop naik 405 Reputation points

2023-07-19T18:15:24.05+00:00

@Franca, Rafael,

If you are unable to export the XML data back to its original format using the Export button under Developer -> XML in Excel, it is likely due to certain limitations with Excel's XML handling capabilities. Excel's XML import functionality is primarily designed for importing XML data into a structured tabular format, but it may not fully support exporting the data back to the original XML format, especially if the original XML file contains complex structures or features that Excel cannot represent in its standard XML format.

Here are some potential reasons and workarounds:

Unsupported XML Structure: If the original XML file contains complex structures, namespaces, or other elements that are not supported by Excel's XML format, the Export button may not work. In such cases, Excel may only allow you to export the data in its simplified tabular form.

Schema Limitations: Excel's XML import/export functionality may be limited by the XML schema used in the original file. If the XML file uses a custom schema or a complex schema, Excel might not handle the export correctly.

Custom Export Solution: If you need to export the data back to the original XML format, you may need to build a custom solution using a programming language or script that can handle the specific XML structure and generate the XML file accordingly.

Third-Party Tools: There are third-party XML editing and manipulation tools available that provide more advanced functionality for working with XML data. Consider using such tools if you need to perform more complex XML operations.

XML Editor or IDE: For full control over the XML data, consider using specialized XML editors or integrated development environments (IDEs) that support XML editing and manipulation. These tools provide more advanced features for handling complex XML structures.

In summary, Excel's XML import/export functionality is primarily designed for tabular data representation, and exporting the data back to the original XML format may not be fully supported for complex XML structures. If you need to work extensively with XML data, consider using specialized XML editors or custom scripting solutions to achieve your specific requirements.
Franca, Rafael 0 Reputation points

2023-07-19T18:15:50.2166667+00:00

That's precisely the problem. I saved the sample XML to a XML file, imported it into excel using the XML import functionality under the Developer tab, and even so I get the error I've mentioned. I didn't enter it as text in the worksheet.

Any ideas?

Share via

Editting a XML file in Excel, then exporting it back as XML

1 answer

Your answer