I have written a new script for csv file export, which writes lines to a %Library.File file based on a SQL query.
The code is simplified into the following as
try {
set f = ##class(%Library.File).%New()
set f.Name = "export.csv"
if (##class(%File).Exists(f.Name)) { $$$ThrowOnError(##class(%File).Delete(f.Name)) }
$$$ThrowOnError(f.Open("WUN"))
$$$ThrowOnError(f.LineTerminatorSet($C(13,10)))
&sql(DECLARE cur CURSOR FOR SELECT a, b into :a, :b FROM table)
&sql(OPEN cur)
&sql(FETCH cur)
do f.Write("a col, b col"_$C(13,10))
while (SQLCODE = 0) {
s line = ""_a_","_b_""
do f.Write(line)
do f.Write($C(13,10))
&sql(FETCH cur)
}
&sql(CLOSE cur)
do f.Close()
$$$ThrowOnError(f.%Save())
return 1
} catch err {
do err.Log()
write "Error exporting file."
}
The SQL table has two columns a and b, whose values can contain special characters. For the example, say the characters are エッジ
I ran into an issue here that special characters were not properly handled in the exported file. I expected to be able to set a standard character encoding of UTF-8 on the file itself, but after a deep dive into %Library.File in the documents, and all the classes it inherits from, I was unable to find anything regarding character encoding or any conversation on this.
I ended up using a $zconvert with "O" mode to "UTF8" on each line to ensure on a line by line basis the proper encoding was used as
do f.Write($zconvert(line,"O","UTF8"))
This works perfectly well. But, I remain curious if anyone is aware of character encoding, historically or currently, for a File type. And/or knows or has opinions on why this is not a property of the File. Just wanted to open this up for discussion.
Thanks anyone for your thoughts!