Read data from Excel

Question

Question

Oliver Wilms · May 27, 2020

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

Discussion (11)2

Log in or sign up to continue

Robert Cemper · May 29, 2020

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

0 0

score 0 · Answer 1 · 2020-05-28T00:56:57-04:00

Google the JDBC Driver for Excel.
Here is a good option: HXTT Excel Pure Java JDBC (1.2, 2.0, 3.0, 4.0, 4.1, 4.2, 4.3) Drivers for MS Excel (XLS, XLSX)

score 0 · Answer 2 · 2020-05-28T21:27:37-04:00

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

score 1 · Answer 3 · 2020-05-28T03:34:00-04:00

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.

score 0 · Answer 4 · 2020-05-28T21:46:22-04:00

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

score 0 · Answer 5 · 2020-05-28T22:18:27-04:00

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.

score 0 · Answer 6 · 2020-05-28T03:57:50-04:00

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

score 0 · Answer 7 · 2020-05-28T21:35:00-04:00

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

score 0 · Answer 8 · 2020-05-29T15:42:06-04:00

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

score 0 · Answer 9 · 2020-05-28T09:12:07-04:00

There are several utilities available for converting XLSX to CSV. You could run the conversion using $ZF(-100) and then read the CSV using a RecordMap.

Here's one example:
https://csvkit.readthedocs.io/en/1.0.2/scripts/in2csv.html

score 0 · Answer 10 · 2020-05-28T21:28:23-04:00

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