Alternatives to Writing XML

Page Index

Introduction

It is possible to create an XML version of your data without actually writing XML. Some software, such as FIELD, Shoebox, Excel and Access, will export XML with user-defined tags. If these tags are used properly, the XML document produced will be of best-practice quality. It is critical to understand the limitations of the software you are using. Excel, for example, uses column and row headers to define XML tags, so care must be taken in managing the spreadsheet to optimize XML output. For more about software that exports XML, Ferrara and Moran's 'Review of DBMS for Linguistic Purposes,' overviews database management systems and examines XML import and export.

Excel to XML: How-to

This write-up was generated with Excel 2002. If you do not have 2002, then your steps may differ slightly, but the gist should be the same.

1. Put together spreadsheet data in Excel. This should include headers for your columns, as these will be your XML tags when we are finished. The headers cannot include a blank space or begin with a number; thus, "Zapotec_stem" and "Pers3" are acceptable, but "Zapotec stem" and "3P" are not. It is important to keep data with the same basic heading (say, morphological meaning and morpheme) together, so that they can nest properly. In order to do this, you will need to begin each header with a slash ( / ), and use slashes to separate fields within the header. For example, let's say that each row in your spreadsheet has a verb, followed by the verb stem, then by a suffix, then person number, then S or P for singular or plural, then by an English gloss, as follows:

hablamos habl amos 2 P we talk

You could create the following column headers in order to nest the entire row together, then nest the stem and suffix together:

/Word/Spanish /Word/Morpheme/stem /Word/Morpheme/suffix /Word/Num /Word/SgPl /Word/Eng

This would create XML in the following format:


  <Word>
    <Spanish>hablamos</Spanish>
    <Morpheme>
      <stem>habl</stem>
      <suffix>amos</suffix>
    </Morpheme>
    <Num>2</Num>
    <SgPl>2</SgPl>
    <Eng>we talk</Eng>
  </Word>

2. Figure out the ranges of your data. Include the headings in your estimate, for instance (A:1-F:2283).

3. Download the GenerateXML utility/module from our site. Keep this handy, preferably on your Desktop, as it is vital for our next step. To download the module, right click on the file name below and select the appropriate Save option ("Save Link" in Firefox, "Save Target" in Internet Explorer or "Save Link Target" in Netscape). Use control-click instead of right click on a Mac.

Download GenerateXML

4. Go to Tools->Macro->Visual Basic Editor This opens a window with an editor for the computer language Visual Basic. Within this are panes that deal with the files we need to use for the next step.

5. You will see a small pane on the top left titled Project - VBA Project; within, you will see a list of Microsoft Excel Objects. Right-click once below the Microsoft Excel Objects folder, then click on Import File. Find your downloaded module (GenerateXML.bas) under Desktop (or wherever you saved it), and click Open to import it. In the Project - VBA Project pane, you will now see a new folder named Modules. Open this folder, double-click on GenerateXML, and the macro will open in the main pane. (If the Modules folder already exists, open it and right-click within it, then follow the instructions as given above.)

6. You will need to modify your module to make your own root tag. To do this, you need to look in the macro in the main pane in VB Editor, to find the line Sub Generate XML Macro(). The next line will be:

GenerateXML Range("A1:B3"), "languages", "languages.xml"

Change A1:B3 to the range you estimated in Step 2 above. This is followed by the XML root tag to be generated; you can leave this as "languages" or replace it with whatever tag you prefer (again, the tag must not include blanks and may not begin with a number). The final item in that line, "languages.xml", is the default save name for your file. Change this to whatever you wish, keeping the .xml extension. Then save the macro (as you would save any file) and close VB Editor.

7. In order to import your macro into your spreadsheet, you need to make a button. This is as easy as selecting View->Toolbars->Forms, bringing up the Forms menu and clicking on the button icon (the plain little horizontal rectangle, which will display "Button" when you mouse-over it). Double-click, drag onto the spreadsheet, then click again; this will place the button on the sheet and open the Assign Macro box. You can then click and drag it onto your form to create a button. Double clicking on this will bring up an Assign box with which you can Assign the GenerateXML macro. Re-label the button anything you like. We suggest "Generate XML". Save your document.

8. Click your new Excel to XML button and save your XML file. You may display this in a browser, if you wish.

9. The button should work fine the first time you run it, but if you close the spreadsheet and reopen it later, you might get a security warning, depending on your Macro Security settings for Excel. The Macro Security settings are designed to help you avoid viruses. If yours are set to Low, it will allow you to run the macro without any warnings; if set to High, it will say something like, "Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified as safe. To run the macros, you can either have them signed or change your security level. Click Help for more information." To run this macro, we recommend that you change your Macro Security setting to Medium; then, each time you open the spreadsheet, it will warn you that it contains a macro, and will let you choose to enable or disable the macro. To change your Macro Security setting, click on Tools, then Options, click on the Security tab, then click the Macro Security button, then the Security Level tab, then choose Medium.


User Contributed Notes
XML Alternatives
+ Add a comment
  + View comments

Back to top Credits | Glossary | Help | Navigation | Site Map | Site Search