Import Unpopulated Excel Data

Question

I am writing a script that will import an excel sheet and perform various cleaning and analysis actions on it. However, the excel sheets that will be imported are updated daily and have 20,000+ rows that will only populate when you refresh it in excel. I have not been able to find a way to refresh this data sheet in the excel import wizard or in JMP without going into the excel sheet and refreshing it there first - does anyone know of a way to refresh excel data during import so that the user won't have to separately refresh the excel sheet?

briancorcoran · Answer

In JMP 18, there is a Update Table script that is provided in the imported JMP table.  If you run this table script, AND the # of columns exactly matches the prior table, it will update the data in place in the JMP table.  However, it will not be applying your cleaning or analysis actions so it may not be useful.  Alternatives that I can think of, which are not simple, are:

1.  Use JSL file functions and scheduling to read the Excel file information to detect when it is changed on disk, then trigger your actions

<or>

2. Use Visual Basic for Applications within Excel to drive your operations on JMP when the data changes in Excel.  This essentially reverses the flow where Excel becomes the driving application.

Regards,

Brian Corcoran

JMP Development

jthi · Answer

Depending on what the excel is doing, could you perform the data population in JMP? If not, you might have to use Run Program() from JMP and utilize commandline/vscript/powershell to update the data in the excel sheet before loading it to JMP.