12-21-2021 07:41 AM
Hello - we would like to move specific Forms and Adj data from one application to another - data are extracted via Data Export in DM, does anyone have an example of the Extensibility Rule (BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile) to load the extract data? Many thanks - Wei
Solved! Go to Solution.
12-21-2021 08:23 PM - edited 12-22-2021 08:47 AM
Hi,
Below is an example snippet of using the SetDataCellsUsingCsvFile BRApi. As always, please test in a Development application first!
'get app config settings
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
'retrieve exported cube data file from a path on the current application's fileshare
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppName)
'define the full path including the file name
Dim filePath As String = folderPath & "\SomeFileNameFormsData.csv"
'next check if the file exists on the file share...
If File.Exists(filePath) Then
'define the source origin members
Dim sourceOrigins As New List(Of String)
sourceOrigins.Add("Forms")
'define target origin member
Dim targetOrigin As String = "Forms"
'process the file and load the data - do not load zeros
BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile(si, filePath, ",", sourceOrigins, targetOrigin, False)
End If
Nick Kroppe
OneStream Software
12-21-2021 08:23 PM - edited 12-22-2021 08:47 AM
Hi,
Below is an example snippet of using the SetDataCellsUsingCsvFile BRApi. As always, please test in a Development application first!
'get app config settings
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
'retrieve exported cube data file from a path on the current application's fileshare
Dim folderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppName)
'define the full path including the file name
Dim filePath As String = folderPath & "\SomeFileNameFormsData.csv"
'next check if the file exists on the file share...
If File.Exists(filePath) Then
'define the source origin members
Dim sourceOrigins As New List(Of String)
sourceOrigins.Add("Forms")
'define target origin member
Dim targetOrigin As String = "Forms"
'process the file and load the data - do not load zeros
BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile(si, filePath, ",", sourceOrigins, targetOrigin, False)
End If
Nick Kroppe
OneStream Software
12-23-2021 04:53 AM
Thanks Nick - all tested and works well!
06-30-2022 05:30 AM
Hello,
We tried this, the rule executes fine, but we don't see any data in the destination scenario. Will you please share sample CSV file? DO you have to add any named range or so in the CSV file?
06-30-2022 06:05 AM
Hi Bharti,
The data was exported via Data Management job, and no need to add any named range to it.
Here is an example - hope that helps
Thanks
Wei
Cube | Entity | Parent | Cons | Scenario | Time | View | Account | Flow | Origin | IC | UD1 | UD2 | UD3 | UD4 | UD5 | UD6 | UD7 | UD8 | Amount | HasData | Annotation | Assumptions | AuditComment | Footnote | VarianceExplanation | |
XXXX | XXXX | USD | Plan_Working | 2023M1 | YTD | Axxxxx | Input | Forms | None | None | None | XXX | Base | None | None | None | None | 2032000 | Yes | |||||||
06-30-2022 06:07 AM
Here is the screenshot of the example:
07-08-2022 10:59 AM
Hi Wei,
Does this work for "Import" data, we tried this with target origin "Import", but doesn't seem to be working ?
07-11-2022 11:41 AM
Hi Bharti,
I believe it works with "Import" data (based on the Reference Guide) - however I have not tested it before.
Thanks
Wei
07-28-2022 04:09 PM
Hi Bharti,
I am trying to use this function with target origin "Import" as well, but it doesn't work on my end either. It works perfectly when target origin is Forms. Did you get any alternative solution? Thank you
Kai
07-29-2022 03:47 AM
Hi Kai,
The only safe way would be to use the standard Import > Validate > Load process.
The other alternate probably would be to load these "Import" cells to "Forms" and a dummy U8 member say U8#Flag. Once these get loaded, copy O#Forms:U8#Flag to O#Import:U8#Originalmember and clear off data from O#Forms:U8#Flag. We have not tried this, looks too complex. Will let you know if this works.
07-29-2022 09:04 AM
Thank you very much Bharti for your reply. I totally agree with you. I think the best practice is to keep "Import" cells just for data loaded from Import > Validate > Load, as this is "import" is designed for, but I will give copy method a try and see whether this will work.
Kai
08-09-2022 01:07 AM
Hey Nick,
Not sure what the etiquette is in digging up old posts but I had another question in regards to this. Where should this data be landing once the SetDataCellsUsingUsingCsvFile function is run? Design & Ref says Stage. I couldn't see anything in stage or in the cube however. Tried getting some feedback from the returned XFResult to confirm its working but all I get is BoolValue = True.
A small paragraph in the Design & Ref guide is the only place in OneStream documentation I can find this mentioned although it mentions it as SetDataCellsUsingCsvFile rather than SetDataCellsUsingUsingCsvFile so it is hard to find. Am I missing anything?
08-09-2022 04:23 AM
Hi Daniel,
I had tried setting the target origin member as "Import" and using the SetDataCellsUsingUsingCsvFile function, but it did not work.
08-09-2022 04:45 AM
Hi Daniel,
The function push the data into the cube.
Thanks
08-09-2022 04:54 AM
Thanks Wei. That is what I expected and helps narrow down my issue. Now it's just a matter of figuring out why that isn't happening.
03-27-2023 10:36 PM
@DanielWillis I'm having the same issue. I'm able to run the load but I don't see any data loaded to the cube using this method. Did you figure out your issue and if so can you help me along?
03-28-2023 06:25 PM
Hey Chris. No, unfortunately I put it in the too hard basket as it was more of a side thing at the time and haven't gotten back to it. Its definitely a function I'm keen to understand though.
03-29-2023 02:27 AM
Hello,
I was able to load data using SetDataCellsUsingUsingCsvFile function. Followed the below mentioned steps:
1. Export source data using a DM step. This will generate a CSV file. The format of CSV file should not be changed for loading in destination application.
2. Segregate Forms and Import data manually or through a BR, this would create two CSV files say "SourceImport.csv" and "SourceForms.csv".
3. "SourceForms.csv" data can be loaded in destination scenario/application using the SetDataCellsUsingUsingCsvFile function.
4. "SourceImport.csv" would have to be loaded via the usual Import, Validate, Load step.
Hope this helps.
03-29-2023 09:25 AM
Thanks Daniel and Nihi,
This step is the trouble: 3. "SourceForms.csv" data can be loaded in destination scenario/application using the SetDataCellsUsingUsingCsvFile function.
I've run the code that uses the SetDataCellsUsingUsingCsvFile (UsingUsing) and although the Extensibility Rule says it ran without error it shows no sign of working (no records were loaded).
Meanwhile I'm able to write XFSetCell commands but that is quite cumbersome for large data sets.
Disappointed SetDataCellsUsingUsingCsvFile is not working for Daniel and I and disappointed there is scant documentation on this process.
-Chris