04-20-2023 06:06 AM - last edited on 05-02-2023 11:14 AM by JackLacava
Hi all, I have a dashboard with an SQL table editor component. Through the component in the dashboard, I can save text to the table, including special characters. For example, there is no problem in saving a string containing α (alpha).
Now I am trying to load a CSV file to that same table. The file contains some special characters too. To load the file, I have built a business rule which contains the following command:
loadResults = BRApi.Utilities.LoadCustomTableUsingDelimitedFile(si, SourceDataOriginTypes.FromFileUpload, filePath, fileinfo.XFFile.ContentFileBytes, "|", "App", "LOAD_TABLE", "Merge", lfieldtokens, True)
The special characters are not recognised, and this is the result when loading TestNameαβπ.
Is there a way to load the CSV file and preserve the special characters?
Solved! Go to Solution.
04-20-2023 09:09 AM - edited 04-20-2023 09:13 AM
Thank you. As I suspected, it's an encoding issue. The file is UTF-8, whereas OneStream largely reasons in UTF-16 (traditional default in Windows/.Net world). There are two ways to fix this, as far as I can see:
Here's some (untested) code that should send you in the right direction:
' at the top
Imports System.Text
[...]
' get the encodings we need
Dim utf8 As Encoding = encoding.UTF8
Dim utf16 As Encoding = Encoding.Unicode
' turn original bytes into String ...
Dim text As String = utf8.GetString(fileinfo.XFFile.ContentFileBytes)
' ... and from there to UTF16 bytes
Dim toUpload As Byte() = utf16.GetBytes(text)
' upload utf16 bytes
BRApi.Utilities.LoadCustomTableUsingDelimitedFile(si, _
SourceDataOriginTypes.FromFileUpload, filePath, _
toUpload, _
"|", "App", "LOAD_TABLE", "Merge", lfieldtokens, True)
04-20-2023 06:44 AM
I expect that will depend on the encoding of the original file. Can you upload the sample file you're using, so I can run a few tests?
04-20-2023 07:05 AM
Hi, I have uploaded the test file to WeTransfer. Let me know if I should share it via a different method. Thank you
04-20-2023 09:09 AM - edited 04-20-2023 09:13 AM
Thank you. As I suspected, it's an encoding issue. The file is UTF-8, whereas OneStream largely reasons in UTF-16 (traditional default in Windows/.Net world). There are two ways to fix this, as far as I can see:
Here's some (untested) code that should send you in the right direction:
' at the top
Imports System.Text
[...]
' get the encodings we need
Dim utf8 As Encoding = encoding.UTF8
Dim utf16 As Encoding = Encoding.Unicode
' turn original bytes into String ...
Dim text As String = utf8.GetString(fileinfo.XFFile.ContentFileBytes)
' ... and from there to UTF16 bytes
Dim toUpload As Byte() = utf16.GetBytes(text)
' upload utf16 bytes
BRApi.Utilities.LoadCustomTableUsingDelimitedFile(si, _
SourceDataOriginTypes.FromFileUpload, filePath, _
toUpload, _
"|", "App", "LOAD_TABLE", "Merge", lfieldtokens, True)