Saving an export directly to the shared folder

MarcR
VIP

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:

MarcR_0-1643639272951.png

 

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.

Marc Roest
OneStream consultant @Finext
1 ACCEPTED SOLUTION

ChristianW
Valued Contributor

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

View solution in original post

9 REPLIES 9

Sai_Maganti
Contributor II

Have you tried this

Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetGroupsFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName)

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!

Marc Roest
OneStream consultant @Finext

ChristianW
Valued Contributor

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

Hi Christian, 

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


Thanks!

Marc Roest
OneStream consultant @Finext

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

 

Hi Christian,

 

does intellisense work for you when you use interpolated strings in BR editor?


Thanks!

No, but we internally asked for it, please support us with an enhancement request.

Sure thing!

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)