Written by

Internal Applications Developer at InterSystems

STAFF

Discussion Hannah Sullivan · Apr 16, 2025

%Library.File and no Character Encoding Option

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 up file as %Libary.Fileset 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)))
    
    // Write lines from SQL 
    &sql(DECLARE cur CURSORFORSELECT a, b into :a, :b	FROMtable)
    &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) // NOTE: workaround here of do f.Write($zconvert(line,"O","UTF8"))do f.Write($C(13,10))
        &sql(FETCH cur)
    }
    &sql(CLOSE cur)
    
    // Clean updo f.Close()
    $$$ThrowOnError(f.%Save())
    return1
} 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!

Discussion (2)2

Add reply

Comments

Dmitry Maslennikov · Apr 16, 2025

Did you try using more appropriate package %Stream for it?

In your case, you would need to use %Stream.FileCharacter

Set stream=##class(%Stream.FileCharacter).%New()
 $$$ThrowOnError(stream.LinkToFile("c:\export.csv"))
 set rs = ##class(%SQL.Statement).%ExecDirect(, "SELECT a, b FROM table")
 if rs.%SQLCODE'=0 {
     throw##class(%Exception.SQL).CreateFromSQLCODE(rs.%SQLCODE, rs.%Message)
 }
 while rs.%Next() {
    set line = $listbuild(rs.a, rs.b)
    do f.WriteLine($listtostring(line, ","))
 }
 $$$ThrowOnError(stream.%Save())

Additionally, nowadays using embedded classes does not give much advantage, if it's not for one row result, only makes the code a bit harder to read

0 0

Gertjan Klein · Apr 17, 2025

As far as I know, %File does not allow setting the file encoding. The %Stream.FileCharacter class Dmitry mentions does, but it's not called encoding. To use UTF-8, you need to set property TranslateTable to "UTF8" (no dash).

0 0