01-31-2022 09:31 AM - last edited on 05-02-2023 10:36 AM by JackLacava
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.
Solved! Go to Solution.
01-31-2022 10:54 AM
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
01-31-2022 10:24 AM
Have you tried this
Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si)
Dim folderPath As String = FileShareFolderHelper.GetGroupsFolderForApp(si, True, configSettings.FileShareRootFolder, si.AppToken.AppName)
02-02-2022 02:44 AM
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!
01-31-2022 10:54 AM
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
02-02-2022 03:38 AM
Hi Christian,
great! the brapi method does allow a write to the folder i want Documents/Public.
Thanks!
02-02-2022 09:13 PM - edited 02-02-2022 09:16 PM
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
02-02-2022 11:37 PM
Hi Christian,
does intellisense work for you when you use interpolated strings in BR editor?
Thanks!
02-03-2022 04:44 AM
No, but we internally asked for it, please support us with an enhancement request.
03-08-2022 10:54 AM
Sure thing!
09-28-2023 04:56 AM
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)