OneStream Solutions typically have some SQL Server tables related to them. Other custom solutions may include adding custom SQL Server tables as well. This technique allows the user to load data to these custom tables via an Excel template. The mechanism for loading these tables could either be through the user interface of a OneStream 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
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|
Finally, create a Named Range beginning with XFT making sure to include the entire template.
Once the template is complete, it is ready to be loaded into the custom table. If this is being used in conjunction with a OneStream 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:
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)
