OneStream MarketPlace solutions typically have related SQL Server tables. Other custom solutions may support adding custom SQL Server tables as well. This lets you load data to custom tables using an Excel template from a MarketPlace Solution or using OneStream Extensibility Rules. For information about Excel templates, see About Collecting Data in the Design and Reference Guide.
OneStream reads templates using a specific Named Range. You must include the following information in the Named Range. In the first three rows of the Named Range in Column A, specify:
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
IMPORTANT: Import templates do not support changing the Load Method.
| Load Method | Description | Example |
|---|---|---|
| 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
Merge Where Clause Criteria with Substitution Variable Example
|
| 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.
-
Example: 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.
-
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|
Last, create a Named Range beginning with XFT , being sure to include the entire template.
Once the template is complete, you can loaded it to the custom table. If this is being used with a MarketPlace Solution, refer to the solution for further instructions on loading the template to the table.
If you are loading with an Extensibility business rule, use this 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.LoadCustomTableUsing
DelimitedFile(si, SourceDataOriginTypes.FromFileShare, filePath, Nothing, ",", dbLocation, tableName, loadMethod, fieldTokens, True)
