odbc

2021-07-30

今天想试试用 erlang/elixir 通过 odbc 读 excel, 结果...

从 erlang mail list

http://erlang.org/pipermail/erlang-questions/2009-April/043094.html

这里读到可以通过 odbc 来读 excel, 就不用再去读 excel 的各个版本的 format(几百页...) 了

立即着手去找 excel 的 odbc driver, 结果只找到一个 cdata 出的商业版的,

立即下载试用, 安装后照着目录里带的 help.htm 操作了一波装好 driver

先试一波 excel 2003, 即 xls

1> odbc:start().
ok
2> ConnStr = "Driver={CData ODBC Driver for Excel};URI=/Users/chenduo/Downloads/53KF_broken.xls".
"Driver={CData ODBC Driver for Excel};URI=/Users/chenduo/Downloads/53KF_broken.xls"
3> {ok, Ref} = odbc:connect( ConnStr, [{timeout, 45000}] ).
{ok,<0.95.0>}
4> odbc:sql_query(Ref, "SELECT * FROM Worksheet;").
{error,"This Excel file version is not supported. SQLSTATE IS: HY000"}

歇菜...

再试一波 excel 2007+, 即 xlsx

1> odbc:start().
ok
2> ConnStr = "Driver={CData ODBC Driver for Excel};URI=/Users/chenduo/Downloads/test.xlsx".
"Driver={CData ODBC Driver for Excel};URI=/Users/chenduo/Downloads/test.xlsx"
3> {ok, Ref} = odbc:connect( ConnStr, [{timeout, 45000}] ).
{ok,<0.95.0>}
4> odbc:sql_query(Ref, "SELECT * FROM sheet1;").
{selected,["RowId","head1","head2","head3","head4","head5"],
          [{2,
            <<97,0,0,0,50,0,0,0>>,
            <<49,0,0,0,48,0,0,0,48,0,0,0>>,
            <<98,0,0,0,50,0,0,0>>,
            <<99,0,0,0,50,0,0,0>>,
            <<100,0,0,0,50,0,0,0>>},
           {3,
            <<101,0,0,0,50,0,0,0>>,
            <<97,0,0,0,51,0,0,0>>,
            <<98,0,0,0,51,0,0,0>>,
            <<101,0,0,0,51,0,0,0>>,
            <<99,0,0,0,51,0,0,0>>}]}