Read data from Excel
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
Comments
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)
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.
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
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
Thanks for the suggestion. However I am afraid this is also not an approved tool in my work environment.