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