Hacking COBie: flat table to ATTRIBUTE Sheet
This is an example of how I hack data from one structure to another and in this example it’s COBie Attributes which seems to cause people the most problems. Why do it this way? Well the reality is the IFC data model is a great data model and most people have their existing data in spreadsheets or databases you don’t need to go through an expensive process and software just to get some data together for a deliverable. You most likely have all the tools, you just need to know how to use them.
To understand this hack you will need to be familiar with some basic data structure which is Tables of Rows and Columns. In this example I have written a Stored Procedure that will work in MSSQLServer or MSSQLExpress. It’s pretty straight forward in that you can do this in the QUERY window.
First up is some preparation. You will need to add a function called db.Split to your data base. Cut and paste this into the Query window. This function will be used in the Stored procedure to pull out map, and process the data table. We will provide a few arrays of field names, names we want to remap to and what their units are.
GO /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 07/07/2015 13:15:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split] ( @RowData NVARCHAR(MAX), @Delimeter NVARCHAR(MAX) ) RETURNS @RtnValue TABLE ( ID INT IDENTITY(1,1), Data NVARCHAR(MAX) ) AS BEGIN DECLARE @Iterator INT SET @Iterator = 1 DECLARE @FoundIndex INT SET @FoundIndex = CHARINDEX(@Delimeter,@RowData) WHILE (@FoundIndex>0) BEGIN INSERT INTO @RtnValue (data) SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1))) SET @RowData = SUBSTRING(@RowData, @FoundIndex + DATALENGTH(@Delimeter) / 2, LEN(@RowData)) SET @Iterator = @Iterator + 1 SET @FoundIndex = CHARINDEX(@Delimeter, @RowData) END INSERT INTO @RtnValue (Data) SELECT Data = LTRIM(RTRIM(@RowData)) RETURN END
Next we need to import a flat table spread sheet into the database that we want to process.
The COBie attribute structure is very flexible in that it can store an infinite number of structures. Say we had 2 types of products one with 5 attributes and one with 10. Normally we would have to create a Table for each product which is fine if we only had a few product types but a building can have 100’s or types. The UK taskforce published 700 types and NBS say they have 7,000. So it is a bit of a challenge to manage that many unique tables. So what COBie does is transpose the columns to be named rows. This may mean there are more rows of data but the open Office Excel XML format has no limit to the number of rows a table can have.
In my example I have a schedule of Fan-coil unit
From this I want a COBie Attribute output. So I have a Stored procedure that will read the table and output only those fields I want and map them to the right name.
This schedule is for the instance settings of each unit so the my SheetName will equal “Component” The Reference code for each unit is called AREA so this equals RowName
I want 3 Attributes Flat Type, Sequence Number and the Cooling Duty. In the table these are called “Flat Type”, “SEQ No” and “SENS CLG KW” because they have spaces you need to enclose them in “[]” square brackets, these are the AttributeNameCols. I need to remap them to names that are either specified in a standard or the Client has specifically asked for. “FlatType”, “Sequence” and “CoolingDuty”. These are set to AttibuteMapNameCols. The last important thing to do is declare what the units or the field are and this is done in AttributeUnitCols
CreateCOBIEAttributes 'FanCoilUnits',--@TableName nvarchar(255)='Rooms', '[Flat type],[SEQ No],[SENS CLG KW]',--@AttributeNameCols nvarchar(max), 'FlatType,Sequence,CoolingDuty',--@AttributeMapNameCols nvarchar(max), 'number,number,kw',--@AttributeUnitCols nvarchar(max), 'Requirment',--@Category NVARCHAR(255) = 'Requirment' , 'Component',--@SheetName nvarchar(255)='Type', 'AREA',--@RowNameCol nvarchar(255)='' 'info@fancoilunits.net',--@CreatedBy NVARCHAR(255)=NULL, '2015-07-07T09:00:00'--@CreatedOn NVARCHAR(255)=NULL
So how does it work?
If you know your SQL then this is straight forward. In simple language you first declare a temporary table. (although this could be a table in your database)
Next we declare a few variables. Then we set up the first cursor and feed it the first array AttributeNameCols. The split function breaks the string and for each value we get the mapping name and unit .
Next within the cursor we build an insert into our temporary table and select from the fancoilunit table naming the fields we want. Lastly we remove any empty RowNames because we always get guff in spreadsheets and the result is a formatted Attributes table. I’ve added some other bits to identify the source, source table and row number in the table if anyone ever needed to check against the source.
CREATE PROCEDURE [dbo].[CreateCOBIEAttributes] @TableName NVARCHAR(255) = 'Rooms' , @AttributeNameCols NVARCHAR(MAX) , @AttributeMapNameCols NVARCHAR(MAX) , @AttributeUnitCols NVARCHAR(MAX) , @Category NVARCHAR(255) = 'Requirment' , @SheetName NVARCHAR(255) = 'Type' , @RowNameCol NVARCHAR(255) = '' , @CreatedBy NVARCHAR(255) = NULL , @CreatedOn NVARCHAR(255) = NULL AS /* SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FanCoilUnits' */ CREATE TABLE #T ( Name NVARCHAR(255) , CreatedBy NVARCHAR(255) , CreatedOn NVARCHAR(255) , Category NVARCHAR(255) , SheetName NVARCHAR(255) , RowName NVARCHAR(255) , Value NVARCHAR(MAX) , Unit NVARCHAR(255) , ExtSystem NVARCHAR(255) , ExtObject NVARCHAR(255) , ExtIdentifier NVARCHAR(255) , Description NVARCHAR(MAX) ) DECLARE @SQL NVARCHAR(MAX) DECLARE @ID INT DECLARE @DATA NVARCHAR(255) DECLARE @N INT DECLARE @MAPNAME NVARCHAR(255) DECLARE @ATTRIBUTE NVARCHAR(255) DECLARE @MAPUNIT NVARCHAR(255) DECLARE @Unit NVARCHAR(255) DECLARE Level1 CURSOR FOR SELECT * FROM dbo.Split(@AttributeNameCols, ',') OPEN Level1 FETCH NEXT FROM Level1 INTO @ID, @DATA WHILE @@FETCH_STATUS = 0 BEGIN DECLARE Level2 CURSOR FOR SELECT * FROM dbo.Split(@AttributeMapNameCols, ',') OPEN Level2 FETCH NEXT FROM Level2 INTO @N, @MAPNAME WHILE @@FETCH_STATUS = 0 BEGIN IF @ID = @N BEGIN SET @ATTRIBUTE = @MAPNAME BREAK END FETCH NEXT FROM Level2 INTO @N, @MAPNAME END CLOSE Level2 DEALLOCATE Level2 DECLARE Level2 CURSOR FOR SELECT * FROM dbo.Split(@AttributeUnitCols, ',') OPEN Level2 FETCH NEXT FROM Level2 INTO @N, @MAPUNIT WHILE @@FETCH_STATUS = 0 BEGIN IF @ID = @N BEGIN SET @Unit = @MAPUNIT BREAK END FETCH NEXT FROM Level2 INTO @N, @MAPUNIT END CLOSE Level2 DEALLOCATE Level2 SET @SQL = 'INSERT INTO #T (' SET @SQL = @SQL + 'Name ,' SET @SQL = @SQL + 'CreatedBy,' SET @SQL = @SQL + 'CreatedOn,' SET @SQL = @SQL + 'Category,' SET @SQL = @SQL + 'SheetName,' SET @SQL = @SQL + 'RowName,' SET @SQL = @SQL + 'Value,' SET @SQL = @SQL + 'Unit,' SET @SQL = @SQL + 'ExtSystem,' SET @SQL = @SQL + 'ExtObject,' SET @SQL = @SQL + 'ExtIdentifier,' SET @SQL = @SQL + 'Description' SET @SQL = @SQL + ' )' SET @SQL = @SQL + 'SELECT ' SET @SQL = @SQL + 'Name = ''' + @ATTRIBUTE + ''',' SET @SQL = @SQL + 'CreatedBy = ''' + @CreatedBy + ''',' SET @SQL = @SQL + 'CreatedOn = ''' + @CreatedOn + ''',' SET @SQL = @SQL + 'Category = ''' + @Category + ''',' SET @SQL = @SQL + 'SheetName = ''' + @SheetName + ''',' SET @SQL = @SQL + @RowNameCol + ' AS RowName, ' SET @SQL = @SQL + @DATA + ' AS Value, ' SET @SQL = @SQL + 'Unit = ''' + @Unit + ''',' SET @SQL = @SQL + 'ExtSystem = ''Spread Sheet:' + @TableName + ''',' SET @SQL = @SQL + 'ExtObject = ''dbo.' + @TableName + ''',' SET @SQL = @SQL + 'ExtIdentifier = ROW_NUMBER() OVER(ORDER BY ' + @DATA + '),' SET @SQL = @SQL + 'Description = ''Attribute ' + @DATA + ' for ' + @RowNameCol + '''' SET @SQL = @SQL + ' FROM ' + @TableName PRINT @SQL EXEC sp_executesql @SQL FETCH NEXT FROM Level1 INTO @ID, @DATA END CLOSE Level1 DEALLOCATE Level1 DELETE FROM #T WHERE RowName IS null SELECT * FROM #T