Skip to main content

OneStream

Uploading Flat Files to a Custom Table in OneStream

Cover Photo 3

Table Data Manager:

OneStream has a Marketplace Solution called Table Data Manager that allows users to create custom tables where data can be loaded and updated.  See also this Perficient blog post on how to get started with Table Data Manager:

OneStream – Table Data Manager / Blogs / Perficient

Table Data Manager has a function/ button to import data into custom tables.  However, the import function in Table Data Manager only accepts files in XML format.   In this blog post I will show you how to load data to a custom table using a flat file such as a comma delimited (.csv) file instead of having to use XML format.

Create a Table:

Using the Table Data Manager instructions from the blog linked above I created this XFC_Test table:

Xfc Test Table

Flat File loaded to OneStream:

I created this flat file and saved as a comma delimited file:

Flat File

I then loaded the file to File Explorer under File Share/Applications/NHLBI_Blog_copy/Batch/Harvest:

File Explorer Test File

Note: NHLBI_Blog_copy was the name of my application.  Everything else would stay the same.  The file was loaded here since we won’t have to be concerned about OneStream having access to this folder.

Business rule to upload data:

I created this business rule to upload the comma delimited file:

Business Rule For Uploading

 

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

Then I made it an Extensibility rule and made it an unknown type so it could be run from the Business rules page:

Business Rule Type And Unknown Case

I developed this code using the Custom Table Load (Delim) Snippet in OneStream:

Business Rule Snippet For Comma Delimited File

To get the file path (line 34) I used the GetFileShareFolder snippet:

Getfilesharefolder Snippet

Uploading Data:

From the business rule click on Run:

Execute Business Rule

 

The business rule uploads the data and you will get a pop-up message that the business rule ran successfully:

Load Table Data Executed

Go back to Table Data Manager and View the data:

View Data

Tips and Tricks:

The column names in the business rule must match the column names in the custom table exactly.  Lines 39, 40 and 41 had the exact same column names in brackets [ ] as the column names in the custom table:

Column Names In Business Rule

The order of the columns in the business rule must match the order of the columns in the flat file.  For example, the first column referred to in the business rule was Department and the 1st column in the flat file was also Department:

Flat File Column Order

The business rule will replace all the data since the load method is set to replace:

Business Rule Load Method

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Phillip Oswald

CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. Recently, I've had 2 different projects for Budgeting and Forecasting with one of them using Thing Planning.

More from this Author

Follow Us