Get names of excel worksheets in Elixir / Erlang

Question

There's a very convenient way of reading Excel files in Erlang/Elixir using ODBC.

For example:

def open(src) do
    conn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
    DBQ=#{src};"

    {:ok, pid} =
      :odbc.connect(to_charlist(conn),
        timeout: 10000,
        binary_strings: :on,
        tuple_row: :off,
        scrollable_cursors: :off,
        trace_driver: :off,
        extended_errors: :on
      )

    result = :odbc.sql_query(pid, to_charlist("select * from [Sheet1$]"))

    :odbc.disconnect(pid)

   result
end

However this requires that we know the name of the excel sheets ahead of time.

Is there a way to get the list of sheet-names from the Excel file?

what would be the equivalent of OdbcConnection.GetSchema("Tables") in Erlang ?

legoscia legoscia · Accepted Answer · 2018-12-14T13:55:46

I had a quick look at the source code for the Erlang ODBC server. If I understand correctly, getting a list of tables requires calling the SQLTables function in the ODBC library, but there is no such call in the server - so as far as I can tell this is currently not possible. (A pull request implementing this would probably be welcomed)

Get names of excel worksheets in Elixir / Erlang

1 Answers