Loading Excel Templates to Custom Tables

OneStream MarketPlace Solutions typically have related SQL Server tables. Other custom solutions may also include adding custom SQL Server tables. This allows users to load data to these custom tables using an Excel template. The mechanism for loading these tables could be through the user interface of a MarketPlace Solution or through OneStream Extensibility Rules.

OneStream reads this template using a specific Named Range which is explained later in this section. Ensure the following information is included in the Named Range.

In the first three rows of the Named Range in Column A, specify the following:

Database Location

Application or System specifies which database contains the custom tables.

Table Name

Custom tables only; enter the Table name

Load Method

The load method determines the action and any additional criteria for the action. 

The syntax is:
Action:[Where Clause Criteria] (Where Clause Criteria is optional)

Load Method Definitions

Merge

If there are no criteria, Merge updates the data if it finds a matching key, otherwise it inserts it

Merge Where Clause Criteria Example

First, this will clear the values for emp1 and then Merge
Merge:[EmployeeID = ‘emp1’]

Merge Where Clause Criteria with Substitution Variable Example

Substitution Variables can be used in the Where Clause Criteria
Merge:[WFProfileName = ‘|WFProfile|’]

Replace

If there are no criteria, Replace clears everything first.  By default, instead of merging, it clears the entire table. This will perform better for high volume because it does not try to match rows from the file to the table.  An error will occur if it finds a match.

Replace Where Clause Criteria Example

This does not try to locate, it only does inserts and appends.
Replace:[EmployeeID = ‘emp1’]

Replace Where Clause Criteria with Substitution Variable Example

Replace:[WFProfileName = ‘|WFProfile|’]

Next, define the Field Types and Field Names beginning in Column A Row 4 and spanning as many columns as necessary. 

The column definition syntax is:

FieldType#:[FieldName]:StaticValue(optional):DefaultValue(optional)

Field Type

This relates to the column name in the table.

xfGuid

Unique identifier [SQL = uniqueidentifier]

xfText

Text defined column in the table [SQL = nvarchar, nchar, ntext]

xfInt

Short integer (4 byte integer) [SQL = int]

xfBit

0,1 (True, False) [SQL = bit]

xfDec

Decimal [SQL = Decimal (28,9)]

xfDbl

Floating point number (8 byte floating) [SQL = Float]

xfDateTime

Date [SQL = datetime]

Field Name

This is specific to the SQL table to be loaded.

StaticValue

Whatever is specified as the Static Value will override every row for that column regardless if it is blank or not.

StaticValueExample

This example will override all rows and enter 50,000 as the Static Value.
xfDec#:[Salary]:50,000

DefaultValue

This only applies to blank rows. 

NOTE: If something is specified in the Static Value, it will ignore whatever is in the DefaultValue.

Default Value Example

This example will enter a New Guid for all blank rows in the column.
xfGuid#:[EmployeeID]::NewGuid

Substitution Variable Example

Substitution Variables can be used in both StaticValue and DefaultValue.
xfText#:[EmployeeName]::|Username|

Finally, create a Named Range beginning with XFT making sure to include the entire template.

After the template is complete, it is ready to be loaded into the custom table.  If this is being used in conjunction with a MarketPlace Solution, refer to the Solution for further instructions on how to load the template to the table.  If this is being loaded via an Extensibility Business Rule, refer to the following example.

Example

Dim fieldTokens As New List(Of String)
fieldTokens.Add("xfGuid#:[EmployeeID]::NewGuid")
'fieldTokens.Add("xfGuid#:[EmployeeID]")
fieldTokens.Add("xfText#:[EmployeeName]")
'fieldTokens.Add("xfText#:[EmployeeName]::|Username|")
fieldTokens.Add("xfInt#:[GradeLevel]")
fieldTokens.Add("xfBit#:[Active]")
fieldTokens.Add("xfDec#:[Salary]")
fieldTokens.Add("xfDbl#:[VacationDays]")
fieldTokens.Add("xfDateTime#:[HireDate]")
BRApi.Utilities.LoadCustomTableUsingDelimitedFile(si, SourceDataOriginTypes.FromFileShare, filePath, 
	Nothing, ",", dbLocation, tableName, loadMethod, fieldTokens, True)