A further investigation into transforming spreadsheet data using XML : a dissertation submitted in partial fulfilment of the requirements for the degree of Bachelor of Applied Computing with Honours at Lincoln University, 2004

Abstract

Spreadsheets are often the first choice for storing data however when complex or large data sets are concerned spreadsheets are soon outgrown. The next logical step is to move the data into a database. When moving data from a spreadsheet to a database, problems arise when the data in the spreadsheet is held in a single "flat" table and the database design calls for the data to be stored in multiple tables. While there are many tools available to manage this transfer process, at present there is no one tool that can manage all the required transformations of the data. We investigate using Extensible Markup Language as the basis for creating a transformation tool that will suit all purposes. As a large number of recently developed office suite applications have been created to allow close integration with XML specifications, it has great potential as a universal medium for communication. The Microsoft.NET Framework is one of the applications that have been developed to support XML and is used in this research to create the data transfer application. There are two main ways to use this Framework to manage the transformation process. The first uses the XML classes inherent in the Framework to directly manipulate an XML file, the second uses ADO.NET. ADO.NET is a data access tool used to connect directly to a data source, enabling files other than those in XML format (e.g.: a file in xls format) to be used as the data source. While both alternatives are discussed in detail with coding examples, ADO.NET provides the greatest flexibility. The final application to perform the data transformation is created using the functionality provided by ADO.NET.