Read data from Excel

Primary tabs

Hello,

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?

Thanks

Replies

Thanks for the suggestion. However I am afraid this is also not an approved tool in my work environment.

if you install Excel you have automatically also a DSN for ODBC access to EXCEL
check your DSN data sources in your Windows installation.
Use ODBC-Gateway in Cache / IRIS to access it. 

Robert,

I added a DSN for an Excel Workbook ODBC 32-bit was my only option on my laptop. When I go to SQL Gateway Connection, I see other DSN but not my Excel DSN. Maybe I will try %Activate next

So I also tried Activate Wizard. Found it in my HealthShare but not in IRIS CE. Once in Activate Wizard, I do not see Excel.

To use a DSN it has to be a SYSTEM-DSN and have the same bit as Cache /  IRIS.  Depends on your EXCEl instalation
Therefore 64bit.
Activate as you might know from Cache is not available in IRIS

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

Alex,

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.

Thanks

Oliver

to check how MX works on your machine
just open file sea-battle-mx.xlsb
and select sea-battle (top big button ).

You will connect to our m-server.

Alex

Thanks for the suggestion. However I am afraid this is also not an approved tool in my work environment.