Creating XML Files in Excel

The tip for the night of May 28, 2015 was to use XML files as a standard load file for databases. XML stands for Extensible Markup Language, which provides rules for encoding documents. If you have data for a load file in an Excel spreadsheet or a .csv file, you can get a XML file with the data simply by importing it into Access. Once this is done, simply right click on the table and choose Export . . . XML file. You can also use an Add-in for Excel which is available for download here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=3108

See the instructions for using the add-in on this page. [The add-in will not work with the 64 bit version of Excel.] On the Add-ins tab, you'll see a menu entitled, XML Tools which includes the option, 'Convert a Range to an XML List'. This will allow you to select data that you've entered on a worksheet for the various columns which compromise a load file. [If you're using the 32 bit version of Excel 2010, you will need to delete the references to '50' in the string 'msxml2.DOMDocument50' in the VBA editor].

Next, click on the Developer tab and in the XML section, click on Source. You'll see the outline of an XML map. See Fig. 1. Choose Export from the XML section and you can create a XML file which look like the one in Figure 2 below when opened in IE.

However note that these schemas differ from those used by the EDRM which can be found on this page.