Liberating data from spreadmarts and Excel (aka OOXML)

Uli Bethke Flexter, XML

In this blog post we liberate data and metadata from the shackles of Excel. We convert all of the content of an Excel file to a relational database and then query the output to determine data lineage, formulas used, formatting used, tables and pivot tables inside Excel, errors in formulas and their dependencies etc.

Few people are aware that Excel workbooks are just a bunch of XML files packaged up in an archive. You can easily check for yourself. Right click on any Excel file (.xlsx) and extract it with a tool such as 7zip.

As you can see, there are various XML files inside the package. The files themselves are organised in a hierarchy and a list of all XML files in the package is available in the [Content_Types].xml file

Let’s open this file in a text editor.

Excel Relationships

The various XML files that make up an Excel package are linked through relationship files. The root relationship file _rels contains links to other XML files a level further down in the hierarchy, e.g. xl/workbook.xml. The relationships at each level are numbered with a unique and sequential ID, e.g. rId3.

The workbook.xml file can be found in the xl folder. Let’s have a look at its content.

As you can see, the workbook contains various references to sheets. These are linked via relationship Ids, e.g. rId2 to the XML sheet files. We can find out the file names of the sheet XML files by looking at the _rels file at this level in the hierarchy. It’s called workbook.xml.rels.

As you can see rId2 is linked to the file in worksheets/sheet2.xml. Let’s have a look at this file.

Making sense of the XML content

Let’s take a peek inside some of the other XML files in the package, e.g. let’s have a look at the sharedStrings.xml file.

The sharedStrings.xml contains a unique list of strings that occur anywhere in the cells of your workbook. These are instantiated in the cells of our worksheets when we open the workbook in Excel. Each unique string in an Excel sheet is only stored once to reduce the overall size of the Excel file on disk. In other words the strings are stored in a normalised format. In this particular file we can find a total number of 593 strings (count) and 428 (uniqueCount) of them are unique.

You might be wondering how I figured out this piece of information. As you can imagine there is extensive documentation on the various OOXML packages. You can download this information from the ECMA website.

Making sense of the XML content is a two step process. In section 12.3 Part Summary we get a brief overview on the various parts that make up the overall Excel package, e.g. 12.3.15 Shared String Table Part contains information about the sharedStrings.xml file. There we find:

An instance of this part type contains one occurrence of each unique string that occurs on all worksheets in a workbook. A package shall contain exactly one Shared String Table part, and that part shall be the target of an implicit relationship from the Workbook part.

If we require more detailed information on the various elements/attributes that can occur in sharedStrings.xml we need to look at section 18. SpreadsheetML Reference Material. There under 18.4 Shared String table we find further information, e.g. on count and uniqueCount

Ok, this is great, but how can we query and analyse all of this rich information? It’s quite simple. We can use Flexter the ETL tool for XML and JSON to convert the OOXML/Excel file to tables in a database and then use SQL to analyse the Excel data and metadata

Converting Excel data and metadata to a database

We have created this short video that shows you how you can convert any .xlsx Excel file to text files.

As part of the output from the conversion process we get an ER diagram that helps us to make sense of the data.

Here is an extract of the diagram, but you can also download the whole relational model of the OOXML model.

Analysing Connections, Tables, QueryTables

In this analysis we want to dive deeper into where the external data in our Excel workbook is coming from.

Connections

Let’s first have a look at the connections that have been defined for this Excel file.

In the Excel file itself we can see that we have defined a connection to another Excel workbook and an MS Access database.

We can see that the connection Query from Excel Files connects to another Excel workbook and selects a couple of data points.

As we can see from the ER diagram, the connection details can be found in tables connections and connection:

Let’s have a look at those tables.

We can find the connection information in our output table. Let’s have a look in the documentation what some of these attributes mean, e.g. savedata. This can be found in section 18.13.1 connection

So now we know that the data that we retrieve from the external Excel workbook will be saved together with the Excel sheet.

Let’s now have a look at how data is injected via a connection into our worksheet.

Tables

Worksheets contain tables, which are connected via so called QueryTables to Connections.

The relationship of worksheets to tables is defined in the sheet.xml.rels, e.g.

In the sheet2.xml.rels we reference table2.xml.

In the output from Flexter these hierarchical/recursive relationships are reflected in the tables relationships and relationship. We can create a database view to reflect this hierarchy.

 

We can see from the Flexter ER diagram that Excel workbook table information is stored in the following tables.

QueryTable

QueryTable information is contained in the following tables

Let’s bring all of this information together in one query.

 

… and have a look at the output

As you can see, there are two tables in our Excel workbook using the Excel and Access connection. We can also see the worksheets and the table cells where these tables have been created.

Let’s have a look at the connection information

Flexter for Excel. Governance for spreadsheets

Thanks to the power of Flexter, our ETL tool for XML and JSON, it’s quite straightforward to extract data and metadata from Excel workbooks. It is then easy to analyse the output, e.g. identify where the data in worksheets comes from, identify tables, find errors or inconsistencies in formulas, provide lineage for formulas etc.

Do you have any governance issues of spreadsheets? We are working on a set of spreadsheet governance features, e.g. Excel versioning, automated header detection, lineage for Excel formulas, detecting PII.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.