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 Sample table - image 1.PNG](/t5/image/serverpage/image-id/9112iF3B06C77EBA6F638/image-size/large/is-moderation-mode/true?v=v2&px=999)
We also created a flat file (.csv) containing the data we want to load:
![Flat file sample - image 2.PNG Flat file sample - image 2.PNG](/t5/image/serverpage/image-id/9114i5B2C4156B3F075B6/image-size/large/is-moderation-mode/true?v=v2&px=999)
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 Test file - image 3.PNG](/t5/image/serverpage/image-id/9115iF83331B0A455A7F2/image-size/large/is-moderation-mode/true?v=v2&px=999)
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 Business rule - image 5.PNG](/t5/image/serverpage/image-id/9117iB6C7C1EAAC872C41/image-size/large/is-moderation-mode/true?v=v2&px=999)
We will have to write this business rule to upload the file:![Code - image 4.PNG Code - image 4.PNG](/t5/image/serverpage/image-id/9116i6C7C110C8465EF2D/image-size/large/is-moderation-mode/true?v=v2&px=999)
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 Extender rule - image 6.PNG](/t5/image/serverpage/image-id/9118i3510DC3B5E1461C3/image-size/large/is-moderation-mode/true?v=v2&px=999)
The GetFileShareFolder function, used on line 34 to retrieve our directory path, is also documented in the helper tree:
![JackLacava_0-1710931037044.png JackLacava_0-1710931037044.png](/t5/image/serverpage/image-id/9230iDF9BD2639F992224/image-size/medium?v=v2&px=400)
Make sure you Compile your code after saving it, to check for errors!
![JackLacava_3-1710931317678.png JackLacava_3-1710931317678.png](/t5/image/serverpage/image-id/9233iD0DDCE5C76EE2A0E/image-size/medium?v=v2&px=400)
(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 JackLacava_2-1710931294570.png](/t5/image/serverpage/image-id/9232i6BE4A36EFBAAB34C/image-size/medium?v=v2&px=400)
The business rule will upload the data and we will hopefully get a pop-up message that it ran successfully:
![JackLacava_6-1710932040172.png JackLacava_6-1710932040172.png](/t5/image/serverpage/image-id/9236i169C0F5A8C87D90E/image-size/medium?v=v2&px=400)
We can now go back to Table Data Manager and bask in the glory of our newly-loaded data:
![complete table - image 10.PNG complete table - image 10.PNG](/t5/image/serverpage/image-id/9122iFF5312F89AD69E44/image-size/large/is-moderation-mode/true?v=v2&px=999)
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 Tips - image 11.PNG](/t5/image/serverpage/image-id/9123i688905A7861C32F7/image-size/large/is-moderation-mode/true?v=v2&px=999)
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 Excel flat file - image 12.PNG](/t5/image/serverpage/image-id/9124i9736357044AE5B4F/image-size/large/is-moderation-mode/true?v=v2&px=999)
Data can be replaced or merged by changing the LoadMethod parameter (notice the comment on that line):
![Replace - image 13.PNG Replace - image 13.PNG](/t5/image/serverpage/image-id/9125iDFEE7C9C3849BCDA/image-size/large/is-moderation-mode/true?v=v2&px=999)
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.