CREOutsider

You are here: Home / Tech Guides / Creating an XML File from Excel for Your Listing Data

Creating an XML File from Excel for Your Listing Data

By

Share

Last week I showed you how to keep your listings in Excel. If you’ve done that, you can create an XML file. As I wrote, having an XML file gives you more ways to use your listing data.

The process isn’t that difficult as long as you’re aware of some of Excel’s quirks.  Basically, you’re going to map your listing data in Excel to data “names” in an XML schema file.   After mapping, you’ll upload the XML file to a public location so you can access it via a URL.

XML SCHEMA FILE
The schema file identifies the data elements (data fields) for the feed. All the data for your listing is organized into elements that are “wrapped” in a “container” – <listing> </listing> – and each element within a listing container is also tagged – e.g., <address> </address>. Your aggregator should be able to provide you with a schema file – or at least a list of fields that it uses.

  • A sample Rofo schema file is being used for this example. You can view it here – and copy it and paste it into Notepad if you want to save it.
  • No schema file or fields provided? Make your own using the schema file above as a start. Change elements as needed to match your data in Excel. Just make sure your schema file has two listing containers filled with elements and that each container match.
  • Use Notepad to edit or create your schema file then save as XXXschema.xml. Notepad will name the file XXXschema.xml.txt. Just remove the “.txt” from the file name (ignore any message from Windows).

LISTINGS IN EXCEL
Review how you keep your listing data first to see how it matches up with the schema elements. You may need to combine or divide data in some of your columns.  It’s likely you’ll have more fields than in the schema file – or visa versa. We’ll deal with that below.

SETTING UP EXCEL TO MAP
Open your listing data file in Excel.  Make sure the Developer Tab is available.  If it isn’t, right click on the ribbon and select Customize Ribbon then check the box next to Developer.

Because of glitch in Excel, you’ll need to Copy the data you want in your feed from the original sheet onto a new tab.

  • Add a new tab in your Listings workbook. Name it XML.
  • From your Listings tab, use the Filters to select the listings you want in your feed (e.g., only Active and/or Exclusives) then COPY ALL the remaining rows including the column names.
  • Put your cursor in the first cell in the first column of the new tab/workbook then use PASTE SPECIAL –> Values, to Paste what you Copied.

adding XML source file

  • Select the Developer tab and from the XML block, select SOURCE (#1 above). The XML Source window appears (at right on image above). Select XML Maps… (#2) to import the XML schema file. Browse to the location of the schema file (#3) and select it.
    • Excel will display an error saying that it’s not a schema file and it will convert it to one. Click OK.
    • If you want, rename the map and click OK/done.

You’ll see the elements listed in the XML SOURCE window.

IMPORTANT: Before starting, remove any columns on the XML tab you will not be mapping.

IMPORTANT: Map in the order of your Excel columns – do not follow the XML elements order.  If you skip any Excel columns while mapping, Excel gets confused.  So if you  miss a column you were supposed to remove, delete it before continuing to map.

Map your Excel columns to the elements in the XML SOURCE window by selecting one element at a time, then dragging the element to the column title it applies to.

mapping

  • You don’t have to map all the XML schema elements to Excel columns, but you MUST match all your Excel columns to a schema element.
  • Your column titles don’t have to match the XML element names. Unless you change the default settings, the XML names will appear as elements in your feed – not your column title names.

Once you’re done mapping, the sheet will look like this. Save your Excel file.

finished mapping

MAKING YOUR XML FILE ACCESSIBLE
Once you’re done mapping, EXPORT the file (Developer Tab –> XML block –> Export). Name the exported file and save it.

You’ll want to make the file publicly available so the aggregator can retrieve it. There are a few ways you can do that:

1) Upload the file to your web server and provide the URL to your aggregator. (You’ll need FTP software to do this/have your web developer do it). Your URL will be something like this:

http://creoutsider.com/Rofo_Listings_XML.xml

2) Save your file to a file storage service that has a FTP-like folder such as the old DropBox “Public” folder. (Note that it is still available to DropBox users who signed up prior to October 4, 2012 – and that only current paid users can create a Public folder if they want.)

https://dl.dropboxusercontent.com/u/12649545/Rofo_Listings_XML.xml

If you don’t have a way to provide a static URL, the aggregator may be able to download the file from a link from a cloud storage service, Evernote or any app that lets you share links to documents.

UPDATING
Open your Excel Listings file. Refresh or edit your listing data, sort/filter, then COPY the remaining rows including the column titles. Using PASTE SPECIAL –> VALUES, Paste to a blank tab in the Listings workbook (not the XML tab).

Once again, you’ll need to remove all the columns that you don’t include in your map. Use the XML tab for reference. And while you’re on the XML tab, remove all the listing data but keep the column titles in place.

Once you’re done removing columns from the blank tab, COPY all the rows – NOT including the column titles – then PASTE them onto the XML tab by placing your cursor in the first cell under the first column title. The map should still be intact.

Export again to an XML file to save the new data (use the same file name you used before/over-write the old one) and upload/save the new file to replace the old one.

Other Notes

Share

Comments