· May 27, 2020

Read data from Excel


I work as a contractor for the Department of Veterans Affairs. I want to read data from an Excel workbook. I searched posts here and saw Apache POI suggested. I believe Apache POI is not approved for use within VA. I also believe we should be able to run SQL query against Excel. Has anybody done such a thing or are there other ways to read data from Excel workbooks?


Discussion (11)2
Log in or sign up to continue

Hi Oliver,

(other way)
If you connect the MX virtual excel to your m-database, you can read books very simply:
In an Excel cell, write down the formula
$$ReadSheet c:\myfolder\mybook.xlsb#mysheet ^myGlob(myIndex) *

After starting MX, the specified sheet will be read and converted to global.
On one sheet there are many such formulas.

Or read the entire book at once in ^global, and not just one sheet:
$$ReadSheet c:\mx\mybook.xlsb#* ^myGlob(myIndex, *

And you can work with this globals as you like, write the results in Excel or another way.
MX Works with all Cache or IRIS. 

see also


I looked at MX. I added one connection in the Configuration. I am not exactly sure what to do with this tool. However it sure looks interesting, I am working on a dashboard page where I want to frequently read the status of monitored components from an Excel spreadsheet. I think I should be able to collect the data from another file format.