XML files are used as standard in the EPC industry. The XML schemas that all approved RdSAP and ECO software must be compatible with are created by Landmark Information Group.
A long standing client came to us with a problem. They had carried out a scheme of energy saving improvement installations to a large number of dwellings. The Local Authority that had instructed our client on the work used a software called CodeMan. In order to complete the work, the survey data needed to be updated to this software.
However, CodeMan was unable to read the industry standard XML schema. Instead it required a proprietary Excel spreadsheet to be populated which could then be imported into the software.
With over 280 fields that needed to be populated for over 1,800 properties, it looked likely that the scheme would have to be abandoned at great cost to our client. It was not practicable to manually input this data in the available time.
The Solution: CodeMan Conversion
I had already created a solution to parse XML data, interpret and store as a survey class for a routine to analyse data for my Management Information reporting. I could see the work needed to be able to automate conversion of LIG-16.1 XMLs to the proprietary spreadsheet would be:
- Map the survey class properties to the Excel spreadsheet columns
- Amend the properties where a non-standard format was used
- Loop through for all XMLs
Having estimated the amount of work involved I was able to give the client a quote for the work on a ‘per successful conversion’ rate, along with agreed timescales for completion.
Although we did not make a huge profit on this job, it covered all costs of development and we now have a product that can offer this service for other customers. As far as we know this is the only tool like this on the market. We also helped save a long standing client from considerable losses by providing a service that our competition would have neither the means nor inclination to provide.
Mapping properties to Excel columns
Across the 280+ columns on the Excel sheet I noticed some patterns.
E.g. Extension 0 Floor 0 Area, Extension 0 Floor 1 Area, Extension 0 Floor 2 Area
For these I used concatenation in Excel to speed up the mapping. Although still time consuming, this was much quicker than manually writing each line.
Dim MainAlternativeWallArea As String = Result.Extensions(0).AltWallArea.ToString Dim MainAlternativeWallInsulationThickness As String = converts.Check_WallThickness(Result.Extensions(0).AltWallInsulationThickness) Dim MainAlternativeWallUvalueknown As String = Result.Extensions(0).AltWallUValueKnown.ToString Dim MainAlternativeWallUvalue As String = Result.Extensions(0).AltWallUValue.ToString
The conversion process
The conversion process involves parsing the XML, passing it through a class to convert to a survey object and finally converting enumerations to readable text.
Dim XMLString As String Dim Converter Dim converts = New ConvertDetailsXML Dim Result = New StromaServices.Survey_Class dt.Rows.Add() If XMLarray(i).Extension = ".xml" Then Dim xmldoc As StreamReader xmldoc = File.OpenText(XMLarray(i).FullName) XMLString = xmldoc.ReadToEnd Else Continue For End If If XMLString.Contains("LIG-17.0") Then Converter = New XML_To_Survey992 Else Converter = New XML_To_Survey991 End If converts = New ConvertDetailsXML Result = Converter.Convert1(XMLString)
Due to the differences between LIG-17 XML schema and other EPC XML schemas, I have a separate class for LIG-17 files (XML_To_Survey992). Both conversion classes essentially take data from the XML:
Dim HeatingSystems As XElement = (From b In doc.Descendants().Where(Function(e) e.Name.LocalName = "SAP-Heating")).FirstOrDefault
and assign to the survey object:
Private Sub Add_MainHeating(ByVal Details As XElement) Dim System As Integer System = Details.Descendants().Where(Function(e) e.Name.LocalName = "Main-Heating-Number").Value - 1 Survey.MainHeating(0).MainHeating = True Survey.MainHeating(System).Present = True Survey.MainHeating(System).Fraction = Details.Descendants().Where(Function(e) e.Name.LocalName = "Main-Heating-Fraction").Value * 100 Select Case Details.Descendants().Where(Function(e) e.Name.LocalName = "Main-Heating-Data-Source").Value .... End Sub
Here I use Select Case statements to give XML enumerations readable text equivalents:
Public Function Terrain(ByVal Survey As Integer) As String Select Case Survey Case 1 Terrain = "Urban" Case 2 Terrain = "Suburban" Case 3 Terrain = "Rural" Case Else Terrain = "" End Select End Function
For performance I prefer to write data like this to CSV rather than use Excel Interop to write directly to .xls:
Try 'write headers Dim sep As String = "," Dim builder As New System.Text.StringBuilder For Each col As DataColumn In dt.Columns builder.Append(sep).Append(col.ColumnName) Next writer.WriteLine(builder.ToString()) 'write all rows For Each row As DataRow In dt.Rows builder = New System.Text.StringBuilder For Each col As DataColumn In dt.Columns builder.Append(sep).Append(row(col.ColumnName)) Next writer.WriteLine(builder.ToString()) Next Finally If Not writer Is Nothing Then writer.Close() End Try