The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

MarcR's avatar
MarcR
Contributor II
4 years ago
Solved

Saving an export directly to the shared folder

Hi All, 

For a client i've created a SQL script that generates a datatable that i want to save to a .csv file.

This works as expected via 

 

 

 

BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, sFileName, fileBytes)

 

 

 

From this location i can present the file to the user from the dashboard button that is generating the export.

However, this file is big and the user settings (of some users) change the number formatting of the export.

Therefore i want to export the file directly to the shared folder (which is easy for the user to access from the front end:

 

I've tried all options within brapi.FileSystem but cannot find the proper syntax to save this file directly.

The 'issue' is currently solved by storing this file in the user temp folder, then copying it over to the shared folder (via another article on this forum) but that touches the file twice so preferably i want to save the file directly from the fileBytes (that is a Encoding.UTF8.GetBytes(dt) into the shared folder.

 

Any suggestions would be great.

Admin
  • ChristianW's avatar
    ChristianW
    4 years ago

    Hi Marc

    This works for me

    Dim dt As datatable = aDataTable
	
Dim fileName As String = "a File Name"
Dim filePath As String = $"Documents/Users/{StringHelper.RemoveSystemCharacters(si.UserName,False,False)}"

'Use a string builder object
Dim filesAsString As New StringBuilder()

'headers as first row, if needed
filesAsString.AppendLine(String.Join(",", dt.Columns.Cast(Of DataColumn).select(Function(x) x.ColumnName)))

'loop the rows
For Each dr As DataRow In dt.Rows
	'use String.join to create a comma separated line
	filesAsString.AppendLine(String.Join(",", dr.ItemArray()))
Next dr

'Convert the string to an array of byte
Dim fileAsByte() As Byte = system.Text.Encoding.Unicode.GetBytes(filesAsString.ToString)

'Save fileAsByte
Dim fileDataInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, $"{fileName}.csv", filePath)
Dim fileData As New XFFile(fileDataInfo, String.Empty, fileAsByte)
brapi.FileSystem.InsertOrUpdateFile(si, fileData)

    you can control the culture using this command

    Dim ci As CultureInfo
ci = New CultureInfo("de-DE")
CultureInfo.CurrentCulture = ci

    but be careful, some cultures like German use the comma as decimal separator, then you have to use another character to separate columns

    If Ci.NumberFormat.CurrencyDecimalSeparator = "," then ...

    I hope this helps and cheers

    Christian

9 Replies

  • ChristianW's avatar
    ChristianW
    Icon for OneStream Employee rankOneStream Employee
    4 years ago

    Hi Marc

    This works for me

    Dim dt As datatable = aDataTable
	
Dim fileName As String = "a File Name"
Dim filePath As String = $"Documents/Users/{StringHelper.RemoveSystemCharacters(si.UserName,False,False)}"

'Use a string builder object
Dim filesAsString As New StringBuilder()

'headers as first row, if needed
filesAsString.AppendLine(String.Join(",", dt.Columns.Cast(Of DataColumn).select(Function(x) x.ColumnName)))

'loop the rows
For Each dr As DataRow In dt.Rows
	'use String.join to create a comma separated line
	filesAsString.AppendLine(String.Join(",", dr.ItemArray()))
Next dr

'Convert the string to an array of byte
Dim fileAsByte() As Byte = system.Text.Encoding.Unicode.GetBytes(filesAsString.ToString)

'Save fileAsByte
Dim fileDataInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, $"{fileName}.csv", filePath)
Dim fileData As New XFFile(fileDataInfo, String.Empty, fileAsByte)
brapi.FileSystem.InsertOrUpdateFile(si, fileData)

    you can control the culture using this command

    Dim ci As CultureInfo
ci = New CultureInfo("de-DE")
CultureInfo.CurrentCulture = ci

    but be careful, some cultures like German use the comma as decimal separator, then you have to use another character to separate columns

    If Ci.NumberFormat.CurrencyDecimalSeparator = "," then ...

    I hope this helps and cheers

    Christian

    • MarcR's avatar
      MarcR
      Contributor II
      4 years ago

      Hi Christian, 

      great! the brapi method does allow a write to the folder i want Documents/Public.


      Thanks!

    • Jarek_Sadowski's avatar
      Jarek_Sadowski
      New Contributor II
      2 years ago

      Hello Christian

      thank you, your solution helped me a lot.

      I had to add one modification as in my case folder names does include a dot sign, so I do not remove them when looking for user's folder

       

      Dim cleanUser As String = StringHelper.RemoveSystemCharacters(si.UserName, True, False)

       

       

    • Mustafa_A's avatar
      Mustafa_A
      Contributor II
      4 years ago

      Hi ChristianW 

      Thank you for sharing this. 

      How do you define the source sql table within OS? I'm assuming this is what we use. However I can not seem to make it work. Any direction will be appreciated.  

      Dim dt As datatable = aDataTable

       

  • Sai_Maganti's avatar
    Sai_Maganti
    Contributor II
    4 years ago

    Have you tried this

    Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetGroupsFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName)
    • MarcR's avatar
      MarcR
      Contributor II
      4 years ago

      Hi Sai, i tried this one, it will work but still stores the folder inside the FileShare patch instead of the application database/ public.

      Thanks for your suggestion!