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

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?

Cajuf

Copper Contributor

Jul 21, 2023

The question is how to get back to the XML after making changes to it.

peiyezhu

Bronze Contributor

Jul 21, 2023

Cajuf

 cli_add_html~<pre>;
select  udf_htmlentities('<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root><ExpenseItem>'||group_concat('<Date>'||f01||'</Date><Description>'||f02||'</Description><Amount>'||f03||'<Amount>','</ExpenseItem><ExpenseItem>'||char(10))||'</ExpenseItem></Root>') result from ConvertTableToXml;

result
<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <Root><ExpenseItem><Date>2001/1/1</Date><Description>Airfare</Description><Amount>500.34<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/1</Date><Description>Hotel</Description><Amount>200.0<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/1</Date><Description>Taxi Fare</Description><Amount>100.0<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/1</Date><Description>Long Distance Phone Charges</Description><Amount>57.89<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/1</Date><Description>Food</Description><Amount>82.19<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/2</Date><Description>Food</Description><Amount>17.89<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/2</Date><Description>Personal Items</Description><Amount>32.54<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/3</Date><Description>Taxi Fare</Description><Amount>75.0<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/3</Date><Description>Food</Description><Amount>36.45<Amount></ExpenseItem><ExpenseItem> <Date>2001/1/3</Date><Description>New Suit</Description><Amount>750.0<Amount></ExpenseItem><ExpenseItem> <Date>Jane Winston</Date><Description>2001/1/1</Description><Amount>1.0<Amount></ExpenseItem></Root>