Forum Discussion

Copper Contributor

Jul 20, 2023

Solved

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

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 -> Expo...

developer

excel

LeonPavesic
Jul 20, 2023
Hi Cajuf,

thanks for your answer and the update.

Even if you map the table columns to an XML schema, Excel might still have trouble exporting it back as XML. You might see the "Cannot save or export XML data. The XML maps in this workbook are not exportable" error pop up. Basically, Excel has some limitations when dealing with certain XML structures, and it might not play nice during the export process.

Map XML elements to cells in an XML Map - Microsoft Support

Using a valid XML schema to map the table columns helps define the XML's structure and data types, but it doesn't guarantee smooth sailing with Excel's export. If the XML file is a bit too complex for Excel's taste, you could run into this error.

So, to be safe and keep the original XML intact, it's best to use a special XML editor or a text editor with full XML support.
Excel can still be handy for analyzing and visualizing data, but for serious XML work, it's better to use XML dedicated editors.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic

LeonPavesic

Silver Contributor

Jul 20, 2023

Hi Cajuf,

When you import an XML file into Excel and then export it back without making any changes, Excel can modify the XML structure during the import process. This can make differences between the original file and the exported one, even if you didn't edit anything.

Excel's XML export feature is designed to handle specific types of structured XML data. If the XML file contains complex structures or features that Excel doesn't fully support, it might not be exportable using Excel.

In your case, the XML file you provided has a simple hierarchical structure, which should be manageable for Excel during import and export. But due to Excel's limitations in handling certain XML structures, the exported XML may not exactly match the original file.

To avoid any unintended changes to the XML structure, especially if you frequently work with XML files, it's best to use a dedicated XML editor or a text editor that fully supports XML. These specialized tools will preserve the integrity of the XML file and ensure that the structure remains unchanged during editing and saving (popular XML editors include XMLSpy, Notepad++, and Visual Studio Code with XML extensions.).

Overview of XML in Excel - Microsoft Support

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic

Cajuf

Copper Contributor

Jul 20, 2023

Thanks for your answer. The limitations you mentioned hold even if the table columns are mapped to a XML schema? The help page also provides the schema for that XML. Even so, I wan't able to export it.

peiyezhu

Bronze Contributor

Jul 20, 2023

recover the original XML file after importing it to Excel,

which kind of Excel do you want？
How you map the fields?
EmployeeInfo and ExpenseItem are different types.
why do you need to import to Excel.and export to xml.again?

Cajuf

Copper Contributor

Jul 20, 2023

Need to edit values for several elements. The new values are in a workbook. So being able to edit it using excel formulas and export again to XML would be the most user friendly way of doing this. But another user made it clear that isn't possible or safe do to so.

peiyezhu

Bronze Contributor

Jul 21, 2023

grp detail

8	2001-01-01	Airfare	500.34
13	2001-01-01	Hotel	200
18	2001-01-01	Taxi Fare	100.00
23	2001-01-01	Long Distance Phone Charges	57.89
28	2001-01-01	Food	82.19
33	2001-01-02	Food	17.89
38	2001-01-02	Personal Items	32.54
43	2001-01-03	Taxi Fare	75.00
48	2001-01-03	Food	36.45
53	2001-01-03	New Suit	750.00
	Jane Winston	2001-01-01	0001

 create temp table aa as 
select fillna(iif(f01 like '%<ExpenseItem>%',RowID,'')) grp,iif(f01 like '%<ExpenseItem>%',RowID,'') grp2,regexp2('>([^>]+)<\/',f01,1) val,udf_htmlentities(f01)  from ConvertXMLToTable;
select grp,group_concat(val,'</td><td>') detail from aa where val!='' group by grp;
//where grp2!='';

How about using sql to convert XML to Sheet?

LeonPavesic
Silver Contributor
Jul 20, 2023
Hi Cajuf,

thanks for your answer and the update.

Even if you map the table columns to an XML schema, Excel might still have trouble exporting it back as XML. You might see the "Cannot save or export XML data. The XML maps in this workbook are not exportable" error pop up. Basically, Excel has some limitations when dealing with certain XML structures, and it might not play nice during the export process.

Map XML elements to cells in an XML Map - Microsoft Support

Using a valid XML schema to map the table columns helps define the XML's structure and data types, but it doesn't guarantee smooth sailing with Excel's export. If the XML file is a bit too complex for Excel's taste, you could run into this error.

So, to be safe and keep the original XML intact, it's best to use a special XML editor or a text editor with full XML support.
Excel can still be handy for analyzing and visualizing data, but for serious XML work, it's better to use XML dedicated editors.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic