How ro read unicode data from excel or SQL or any odbc source

[Mike Chris]

load 'unicode'

load 'dd'

connStr =: 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=c:\Temp\1.xlsx'

ch =. ddcon connStr

sh =. 'SELECT * FROM [Sheet1$]' ddsel ch

rawRows =. ddfet sh , _1

rawRows

┌──┬───────────┐

│A2│B2 ????????│

├──┼───────────┤

│A3│B3 │

└──┴───────────┘

rows =. (utf8 each) each rawRows

rows

┌─────┬───────────────────────┐

│┌─┬─┐│┌─┬─┬─┬─┬─┬─┬─┬─┬─┬─┬─┐│

││A│2│││B│2│ │?│?│?│?│?│?│?│?││

│└─┴─┘│└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘│

├─────┼───────────────────────┤

│┌─┬─┐│┌─┬─┐ │

││A│3│││B│3│ │

│└─┴─┘│└─┴─┘ │

└─────┴───────────────────────┘

[Mike Chris]

[chris burke]

What happens if you use "toutf8" (in the unicode addon), rather than "utf8"?

[Mike Chris]

Hi Chris, still does not display unicode.

   rows =. (toutf8 each) each rawRows

rows

┌─────┬───────────────────────┐

│┌─┬─┐│┌─┬─┬─┬─┬─┬─┬─┬─┬─┬─┬─┐│

││A│2│││B│2│ │?│?│?│?│?│?│?│?││

│└─┴─┘│└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘│

├─────┼───────────────────────┤

│┌─┬─┐│┌─┬─┐ │

││A│3│││B│3│ │

│└─┴─┘│└─┴─┘ │

└─────┴───────────────────────┘

[bill lam]

I tested using windows j32 (no 64-bit excel driver)
The driver returns VARCHAR for the column type which means non-unicode.
If driver had returned WVARCHAR then unicode should be handled properly.
Do you have msaccess database to test with?
You can confirm whether column type is nvarchar or not in that case.

t1=: 3 : 0''

connStr =: 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,

*.xlsb)};Dbq=c:\Temp\Book1.xlsx'
ch=: ddcon connStr
if. _1-:ch do.
echo dderr''
return.
end.
echo ddtblx ch
echo 'Sheet1$' ddcol ch
sh=: 'SELECT * FROM [Sheet1$]' ddsel ch
if. _1-:sh do.
echo dderr''
return.
end.
echo rawRows=: ddfet sh,_1
)

+------------------++-------+------------++
|c:\Temp\Book1.xlsx||Sheet1$|SYSTEM TABLE||
+------------------++-------+------------++
+------------------+-----------+----------+-----------+---------+---------+-----------+-------------+--------------+--------------+--------+-------+----------+-------------+----------------+-----------------+----------------+-----------+-------+
|TABLE_CAT
|TABLE_SCHEM|TABLE_NAME|COLUMN_NAME|DATA_TYPE|TYPE_NAME|COLUMN_SIZE|BUFFER_LENGTH|DECIMAL_DIGITS|NUM_PREC_RADIX|NULLABLE|REMARKS|COLUMN_DEF|SQL_DATA_TYPE|SQL_DATETIME_SUB|CHAR_OCTET_LENGTH|ORDINAL_POSITION|IS_NULLABLE|ORDINAL|
+------------------+-----------+----------+-----------+---------+---------+-----------+-------------+--------------+--------------+--------+-------+----------+-------------+----------------+-----------------+----------------+-----------+-------+
|c:\Temp\Book1.xlsx| |Sheet1$ |fld1 |12
|VARCHAR |255 |510 |_2147483648 |_2147483648 |1
| | |12 |_2147483648 |510
|1 |YES |1 |
+------------------+-----------+----------+-----------+---------+---------+-----------+-------------+--------------+--------------+--------+-------+----------+-------------+----------------+-----------------+----------------+-----------+-------+
|c:\Temp\Book1.xlsx| |Sheet1$ |fld2 |12
|VARCHAR |255 |510 |_2147483648 |_2147483648 |1
| | |12 |_2147483648 |510
|2 |YES |2 |
+------------------+-----------+----------+-----------+---------+---------+-----------+-------------+--------------+--------------+--------+-------+----------+-------------+----------------+-----------------+----------------+-----------+-------+
+--+-------+
|A2|B2 ????|
+--+-------+
|A3|B3 |
+--+-------+

> To unsubscribe from this group and stop receiving emails from it, send an email to forum+un...@jsoftware.com.

[Mike Chris]

Hi,

aceodbc.dll is able to handle unicode.

Is it at all possible the J 'load 'dd' library is not handling it to handle unicode ?

[bill lam]

What is the output, column type info in particular if you run my script?

[Michael Christodoulides]

Hi,
Maybe the J ODBC library was never designed to handle Unicode as a
constant seems to be set to 0 value?

[bill lam]

But what is the output I requested?

[Michael Christodoulides]

[bill lam]

The UseUnicode constant is no longer used because J use utf8 as default
OBBC application is at mercy of odbc driver if the driver returns
incorrect information,

On Wed, May 28, 2025 at 5:41 PM Michael Christodoulides

[Michael Christodoulides]

Hi,

Even connecting with SQL Server, that indicates nvarchar column within
J, does not display unicode.
As image indicates.

[bill lam]

It is strange. It should work. If you are using jconsole, try run it on jqt.

I tested linux J odbc unicode using Microsoft "ODBC "Driver 17/(or
18?) for SQL Server" work for SQL Server.
But I did not test on windows and I have no access the SQL Server now.

On Wed, May 28, 2025 at 6:21 PM Michael Christodoulides

[Mike Chris]

Many thanks for the effort Bill. does not work in jconsole either.

[bill lam]

I meant jconsole on windows cannot display unicode because windows
command prompt doesn't use utf8 encoding.

May be you can turn on odbc trace capacity and examine the log.

[Mike Chris]

Hi Bill,

I was not able to get the odbc tracing working.

BUT with 

connStr =: 'Driver={SQL Server};Server=...

sh=: 'select F1=X from TEMP_A ' ddsel ch

no unicode output

sh=: 'select F1=Cast(x as nvarchar(100)) from TEMP_A ' ddsel ch

no unicode output

sh=: 'select F1=Cast(x as nvarchar(MAX)) from TEMP_A ' ddsel ch

Works!!

Any ideas?

[bill lam]

You can check the column type using ddcol,
nvarchar(MAX) should be WLONGVARCHAR (2GB) which is fetched by j/odbc
using a different routine.

odbc tracing can be set inside odbcad32 tracing tab.

[Mike Chris]

DATA_TYPE= _9
TYPE_NAME=nvarchar