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:
We also created a flat file (.csv) containing the data we want to load:
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:
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.
We will have to write this business rule to upload the file:
Most of it was actually copied from the Custom Table Load (Delim) snippet, so it's available in your application already!
The GetFileShareFolder function, used on line 34 to retrieve our directory path, is also documented in the helper tree:
Make sure you Compile your code after saving it, to check for errors!
(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:
The business rule will upload the data and we will hopefully get a pop-up message that it ran successfully:
We can now go back to Table Data Manager and bask in the glory of our newly-loaded data:
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:
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:
Data can be replaced or merged by changing the LoadMethod parameter (notice the comment on that line):
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.
:link: Philip on LinkedIn
:briefcase: Perficient Inc.
