Using Excel files as a databaseI have a customer who wants to use Excel files as a database. I know you can connect to a single workbook and build queries off the different worksheets. For example, the worksheets can be joined by a key field. The customer wants to join and query multiple workbooks. I don't see a way to do this but checking to see if I missed it.
They also want to be able to save results back to the Excel file. Not sure if this is supposed to work but I tried doing a "Save Table" and this crashes JMP 16.0.
Re: Using Excel files as a databaseObviously, there are many objectively better ways, but if they are genuinely constrained to office, then the Excel power query will get all of these excel sheets into a single table. It is effective (I've previously bought together several SharePoint Excel files together into a local file before using that as a single source for transfer to JMP), but it isn't at all user friendly!
Re: Using Excel files as a databaseJust another idea. People with flat files like Excel often want to keep that file structure if used as kind of database. Why not using SQLite. They could convert their current Excel Sheets into SQLite and use them like any other DB in JMP.
Re: Using Excel files as a databaseHi Hyde,
As you've discovered with the Excel ODBC driver (at least you can install the 64-bit version now), it only allows access to one workbook. Multifile access is also something that's been requested for Excel Wizard and never made it. So, I really have no ideas on a way outside of JMP to do it. Of course, you could bring the tables into JMP separately and then use JMP Query Builder for JMP tables to do Join and Query.
For saving, I would recommend using File->Export. We will save out as .xlsx files pretty well.
Brian
