CodeMan conversion

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.

The Problem

codeman output

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:

  1. Map the survey class properties to the Excel spreadsheet columns
  2. Amend the properties where a non-standard format was used
  3. 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)

XML_To_Survey992

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

ConvertDetailsXML

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

Output

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s