I will be describing things in MS Office Excel 2013, but it also applies to other similar apps. I was in need to create XML Data to be used on a website. The easiest way to type-in information is in app with tables, of course, but the conversion to XML Data has proven rather difficult. It required that I enable Developer tab in Options, create XML Map, than link Map to Column, check that my table has headings, and than export. After one hour of finding how to do this, the results were still not good, and I decided to rather try something else.
Why I needed this? Well this is one example, I needed to have some sort of database about movies. This would include various columns, name of the movie, imdb link, description, poster image… All this should be read within a html page using javascript. Instructions how to do this you have here. It reads row by row, columns are the same as the ones in .xml file.
<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
Ok, so how to get this type of structure with Excel 2013, where title, artist, country, company, price and year are table headers? I had table like this:
- First do “Save As”, and save as CSV (MS-DOS), that is Comma Separated Values separated only with commas.
- Go to http://xmlgrid.net/csvToXml.html
- Click on Open file, chose your CSV file, and click on OK.
- Chose that your table has headings.
- Select the code and do copy/paste to notepad,
- Save As in notepad, chose instead “Text documents” “All files”, enter file name with extension .xml, and OK.
- Change html/javascript code from here to match your .xml file. CD should be replaced by Row, and Artist, Country, etc., by your own table header names.
- That’s it! Place the html that reads that XML with .xml file in the same directory, and it will work.
- If you like, you can open .xml now into Excel and work with it from there. Now it will work just fine.
You can see the result from HERE.