Tip: Convert Excel data to XML

Extract data from the popular spreadsheet program

Believe it or not, not every document is originally written in XML. In this tip, Benoit Marchal explains how to unlock data from Excel files to process them in XML, and examines the pros and cons of different solutions.

Share:

Benoit Marchal (bmarchal@pineapplesoft.com), Consultant, Pineapplesoft

Benoit MarchalBenoit Marchal is a Belgian consultant. He is the author of XML by Example, Second Edition and other XML books. Benoit is available to help you with XML projects. You can contact him at bmarchal@pineapplesoft.com or through his personal site at marchal.com.



05 November 2003

Contrary to what you might think, not every document is initially written in XML. Actually, most documents are prepared with some other tool and later converted to XML. Many documents originate from relational databases such as DB2, or from Microsoft Office applications such as Word or Excel.

I have found that many businesses use Excel to edit and prepare data. It's simple to use, widely available, and its tabular format is well-suited to all kinds of information, like lists of products, lists of names, financial data, or statistical data. Excel spreadsheets are often e-mailed to users who are asked to fill in the blanks.

Retrieve your data

One of the issues that arises when collecting and preparing data in a proprietary application is recovering the data. Fortunately, this is not an issue with Excel. Although the specifications for the Excel file format are not publicly available, a number of options can extract XML information from a spreadsheet. This tip reviews your options and highlights each solution's pros and cons.

XMLSS

The latest versions of Excel -- Excel 2002 and Excel XP -- can export a spreadsheet to XML. While you don't have any say over the choice of tags, you do get a valid XML document that you can post-process with any XML tool, including an XSLT stylesheet. The format is known as XMLSS.

This is the simplest solution if:

  1. Your users have the latest version of Excel, and
  2. You're processing XML data on a workstation.

The first condition is obvious, the second one might need more explanation.

Suppose you set up a server to collect and process statistical data. Your users prepare the data with Excel and later upload their spreadsheets to the server for further processing. The first step is to convert the spreadsheet to XML. One solution is to open the spreadsheet in Excel and export it in the XMLSS format. I have seen companies implement such a solution and, while it can run, it's important to keep in mind that Excel was designed for workstations, not servers.

Although functional, this solution might not be as stable as you would like. Among other issues, the spreadsheet conversion may become a bottleneck because it may be difficult to multithread the conversion. In the worst case, every request is queued into a single copy of Excel. It also limits your hosting options: Excel is only available on Windows and MacOS.

CSV files

The first alternative is to work not with actual spreadsheet files but with comma-separated value (CSV) files instead. CSV is a popular file format for exchanging spreadsheets. Any worthy spreadsheet can export to and import from CSV files.

Furthermore, many third-party products that work with spreadsheets also support CSV. For example, most accounting packages can work with CSV files. Although CSV files are not XML, it is simple to convert them into XML files with tools such as XI (see Resources). The TopXML site also includes algorithms for a pure XSLT solution (see Resources).

Using CSV files is much more attractive for servers. For one thing, you are no longer limited to certain platforms. Instead, you are broadening the options for your users (who can now work with Lotus 1-2-3 and other spreadsheets as well as many third-party tools that also recognize the CSV format).

About the only downside with this solution is that your users must take the extra step of saving their data in CSV. In practice I have found that this is seldom a problem, but your mileage may vary.

Plain conversion

Last but not least, you can turn to special libraries to read Excel documents. The Excel file format is not officially documented, but third-parties have been reverse-engineering it for some time. They have also produced libraries to decode Excel files (see Resources). Here's a list of some of the available APIs:

  • Java Excel API is one of my favourite solutions because it is portable, does a fair job of reading XML documents, and includes a conversion to XML.
  • XML::Excel is a Perl API for converting Excel documents into XML.
  • OpenOffice includes C++ libraries for reading Excel documents. You can use these libraries as a basis for converting from Excel to XML.
  • Apache POI supports reading Excel files in Java code. Again, you can use it as a basis for an Excel-to-XML converter.

This list is a representative sample only. Other tools are available for different platforms.

Converting Excel files has three benefits:

  • The files will run on any platform.
  • The libraries are easy to integrate with a server.
  • Users don't need to remember to export the data.

The main danger with this solution is that no library is as good as the real thing and you will find that some spreadsheets do not convert well. You will need to test the best library for your project.


Get going

Excel is a popular tool for preparing all kinds of data that you may want to process in XML. Fortunately, as this tip demonstrates, you have many options for retrieving data in XML. The best option will depend on the specifics of your project.

No matter how you convert the spreadsheet, you will find that the resulting XML document is never quite what you were expecting. Maybe it includes cells that you don't need or maybe the XML vocabulary is not quite what you were expecting. Don't worry -- in most cases, it is simple to prepare an XSLT stylesheet to filter out unwanted data or rename tags.

Resources

  • Participate in the discussion forum for Benoit Marchal's Working XML column.
  • For a quick training guide to XSLT, read Don Day's "Hands-on XSL" (developerWorks, March 2000). It introduces XSLT, the best tool for post-processing documents after a format conversion.
  • Read "Importing text as XML with XI" (developerWorks, April 2002) by Benoit Marchal, which introduces a tool to convert CSV files into XML.
  • Download the Java Excel API, a great tool for converting Excel files to XML.
  • Try the XML::Excel Perl module, a simple tool for Excel processing with XML.
  • Check out OpenOffice, which includes libraries for parsing Excel files.
  • Apache's Jakarta POI is a Java API for Excel files. Use this as a basis for your own Excel-to-XML converter. Cocoon uses Jakarta POI to convert from XML to Excel.
  • Find numerous useful XSLT algorithms, including methods for processing non-XML documents, at TopXML's XSLT Stylesheets page.
  • Find more XML resources on the developerWorks XML zone. For a complete list of XML tips to date, check out the tips summary page.
  • Find out how you can become an IBM Certified Developer in XML and related technologies.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML
ArticleID=12337
ArticleTitle=Tip: Convert Excel data to XML
publish-date=11052003