poswald
New Contributor III

Table Data Manager:

OneStream has a Marketplace Solution, called Table Data Manager, which allows users to create custom tables where data can be loaded and updated. 

Table Data Manager has a feature to directly import data into tables; however, it only accepts files in XML format. In this blog post we will see how to load data to a custom table using a flat file, such as a comma delimited (.csv) file, instead of having to deal with XML.

Create a Table:

Let's assume we've created a table, in this case called XFC_Test:

Sample table - image 1.PNG

We also created a flat file (.csv) containing the data we want to load:

Flat file sample - image 2.PNG

We load the file to File Explorer, under File Share/Applications/<your application name>/Batch/Harvest, so that we won't have to worry about giving OneStream access to some other folder.

In this case, the application is called NHLBI_Blog_copy, and the file is Testfile.csv:

Test file - image 3.PNG

Business rule to upload data:

We will need a rule to take that file and load it in the application. This can be a generic Extender, so that we can trigger it directly; if we wanted repeated automated loads, we could have used a Data Management Step instead.

Business rule - image 5.PNG

We will have to write this business rule to upload the file:Code - image 4.PNG

Most of it was actually copied from the Custom Table Load (Delim) snippet, so it's available in your application already!

Extender rule - image 6.PNG

The GetFileShareFolder function, used on line 34 to retrieve our directory path, is also documented in the helper tree:

JackLacava_0-1710931037044.png

Make sure you Compile your code after saving it, to check for errors!

JackLacava_3-1710931317678.png

(Note the icon will be different for versions 7.x and below).

Uploading Data:

Now we can click on the Run button to execute our code:

JackLacava_2-1710931294570.png

The business rule will upload the data and we will hopefully get a pop-up message that it ran successfully:

JackLacava_6-1710932040172.png

We can now go back to Table Data Manager and bask in the glory of our newly-loaded data:

complete table - image 10.PNG

Tips and Tricks:

Column names in the business rule must match 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:

Tips - image 11.PNG

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

Excel flat file - image 12.PNG

Data can be replaced or merged by changing the LoadMethod parameter (notice the comment on that line):

Replace - image 13.PNG

And that's it! Happy loading!

About the Author

Phillip is a CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. His recent engagement include several Budgeting and Forecasting projects, some involving Thing Planning.
🔗 Philip on LinkedIn 
💼 Perficient Inc.

1 Comment
Juanre
New Contributor

Thanks for this

 

I got it working, and works great!