Everybody knows that Excel is a powerful spreadsheet, and that Google Sheets is the online cloud-based Excel wannabe – but do you know what XML is? XML stands for Extensible Markup Language and it’s a file format that’s becoming hugely popular because it is interoperable and compatible with many software packages. A file saved as XML should be openable in native mode by any software that supports the XML file type. In fact, office suites like Microsoft Office and LibreOffice now use XML files as their default save format, a huge improvement over the days of proprietary save files that you had to have a paid license to access. XML is an essentially text-based file format that you can edit with text editors, and it has a number of other advantages compared with other formats. For example, it’s a much more compact format than other alternatives, and it’s parent-child node structure makes the storage of structured information both highly efficient and easy to read.
One of the main drawbacks of Google Sheets is that despite its power and ease of use, it doesn’t include any built-in option with which you can export spreadsheets directly to XML format. If you click File > Download as in Google Sheets, you can download and save the spreadsheets with the formats shown in the snapshot directly below. The available formats include ODS, PDF, HTML and CSV, but not XML. The closest thing to XML there is Excel XLSX, which is Microsoft Office’s open XML format for spreadsheets.
Fortunately there are a number of things you can do to get your information into XML format.
One approach is to download the spreadsheet in one of those formats and then convert it to XML. There are a few software packages and web tools that enable you to convert PDFs to the XML format. PDF to XML OCR Converter, NitroPDF and PDF2XML are a few of the software packages you can convert PDFs to XML with. You can even use a web-based converter to avoid having to download any software! Click here to open the pdfx v1.9 web tool which converts PDFs to XML and HTML.
You can convert spreadsheet PDFs to XML with that tool by pressing the Choose file button. Then select the PDF spreadsheet you saved from Google Sheets. Press the Submit button to convert the PDF to XML. Then you can open the spreadsheet in XML or HTML formats. Click xml to open the file in XML format as shown in the snapshot below.
Now you can save the spreadsheet by right-clicking its XML page and selecting Save as. A window opens from which you can select XML Document on its Save as type drop-down menu. So select that format, enter a file title and press the Save button.
Then you can open the XML from the folder you saved it to with suitable text editor software, or Excel, or whatever other tool you prefer. You can open XMLs with text editors such as Notepad++, which is one of the best third-party Notepad alternatives covered in this Tech Junkie post. Alternatively, check out this page and press the Browse button there to open it with XML Viewer, which includes a tree view to display XML input as shown in the snapshot below.
Converting Spreadsheets to XML with the Export Sheet Data Add-on
Exporting and converting is a bit of a tedious process, especially if you have a dozen (or worse, a hundred) spreadsheets to convert. Google Sheets has a variety of add-ons that add new options and tools to it. Export Sheet Data is an add-on that enables users to export single sheets or entire spreadsheets to either XML or JSON formats, so you can export Google spreadsheets to XML with that add-on instead of downloading and converting them to the format.
First, open this page and press the Add button there to install the Export Sheet Data extension. Then open a spreadsheet in Google Sheets, and click Add-ons to open a menu that includes Export Sheet Data. Select Export Sheet Data > Open Sidebar to open the sidebar shown in the snapshot directly below.
Now you can click the Select Format menu to select to convert the spreadsheet to either XML or JSON formats. Click Select Sheet(s) to select to convert all sheets in the spreadsheet or just the current sheet. Or you can select Custom to choose more specific sheets to convert. If you scroll down the sidebar, you can select further XML options to adjust the formatting. Press the Visualize button at the bottom of the sidebar to open a preview of the XML file before exporting it.
Press the Export button to compile the spreadsheet to the XML format. Then an Export Complete window opens with a link to the spreadsheet XML. Click the link to open the spreadsheet XML in the browser as shown in the snapshot below. You can press the Download button there to save the spreadsheet XML to a hard drive folder.
Click the More actions button for further options. Then you can select to share the XML by clicking Share. Or select Add star so that you can access the spreadsheet XML more quickly in Google Drive.
With Export Sheet Data, Google Sheets becomes able to export sheets as XML transparently and easily. With that add-on, you can quickly export your Google spreadsheets to XML without saving and converting them to XML.