Pin Me

Convert Excel Data to XML

written by: George Garza•edited by: Linda Richter•updated: 6/22/2010

Extensible Markup Language (XML) is a language that behaves like HTML. It was designed to carry data, not to display it. XML tags are not predefined like HTML. XML data is usually created first from other applications. Two programs are reviewed here that allow you to input Excel data into XML.

  • slide 1 of 5

    Overview

    There are a variety of software programs available to convert data into XML format. Let us review what the main conditions have to be in order to be successful. All XML data has to be put into the form of a tag. A tag is like a set of quotation marks, a beginning and an end, "".

    Take this example of a simple text message:

    To Chloe

    From Jasper

    Re: Picnic Reminder

    Don't forget we will go to the Washington Park this weekend.

    A typical XML data representation would look like this:

    <?xml version="1.0"?>

    <note>

    <to>Chloe</to>

    <from>Jasper</from>

    <heading>Picnic Reminder</heading>

    <body>Don't forget we will go to the Washington Park this weekend</body>

    </note>

    Notice how <note> has an alternate </note>. These are the quotes "", mentioned earlier; actually they are the tags that will appear in doubles. Now the issue becomes, how can you input Excel data into XML? A manual method is not recommended.

    For more on XML see Introduction to XML Code.

  • slide 2 of 5

    Convert Excel Data to XML using Transformer-3

    As the XML format has certain specifications, one can see that doing the conversion by hand is laborious. The best way to make the transformation is with a software program.

    Softsilver Transformer is designed for the technical or nontechnical user that knows very little about XML. Transformer is easy to use, and you do not need to have any special programming knowledge to operate it. With a tabbed interface, it promotes a step-by-step approach. Transformer covers many of the technical issues involved in the conversion. It also provides many options to tailor the XML output to satisfy most requirements.

    Here are some screen shots that show the process of conversion, beginning with a small Excel spreadsheet.

    .Average Daily Ticket Sales By Region .

    After you install the program you will get the following screens.

    Transformer - Source .

    Transformer-Selection .

    Transformer-Target .

    Each one is a different tab focusing on a specific task.

    The XML data after conversion looks like this:

    Transformer-Output 

    .

    Source: Softsilver Transformer

  • slide 3 of 5

    Excel Converter

    Another program, Excel Converter, has a simpler interface. A plus for it is that the conversion is fairly fast. In this example, there is a spreadsheet that is used as a subnet exercise.

    0Excel-Subnet-Spreadsheet The operation will take the spreadsheet and convert it into an XML data file. The Excel Converter software has an opening home screen that looks like this:

    Convert-Excel-XML 

    The screen once the Excel file is loaded looks like this:

    Add Excel - Convert 

    One has to add the spreadsheet file in question, select the location where the XML file will be placed, and convert. Here is the result.

    3XML Data File 

    This conversion took place in less than two seconds. More sophisticated spreadsheets may take longer. But the file is now available for importing as XML data for any other program. The program can convert Excel to CSV, TXT, MHT, MHT, DBF, and XML. The program has a 30-day trial, with the license at $48.95

    Source: Excel-Tool

  • slide 4 of 5

    Summary

    Although converting Excel to XML data can be performed by hand, the process is tedious, time consuming, and prone to errors. It is preferable to do the conversion with a software tool. The two products reviewed here, Softsilver Transformer and Excel Converter, can do the job quickly and without errors.

    Other examples of data conversion can be found at Converting Data to XML.

  • slide 5 of 5

    Image Credits

    Microsoft Excel screenshots are credited to the author.

    Other screenshots are from Softsilver Transformer and Excel-Tool.