This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
S_Boilermaker
S_Boilermaker
Level I

Import Unpopulated Excel Data

This widget could not be displayed.
Created: Jul 25, 2024 10:48 AM  |  Last Modified: Jul 25, 2024 7:48 AM (61 views)

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?

0 Kudos
2 REPLIES 2
jthi
Super User jthi
Super User

Re: Import Unpopulated Excel Data

This widget could not be displayed.
Created: Jul 25, 2024 10:57 AM  |  Last Modified: Jul 25, 2024 7:57 AM (57 views)  |  Posted in reply to message from S_Boilermaker 07-25-2024

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.

-Jarmo
briancorcoran
Staff briancorcoran
Staff

Re: Import Unpopulated Excel Data

This widget could not be displayed.
Created: Jul 25, 2024 12:53 PM  |  Last Modified: Jul 25, 2024 9:53 AM (23 views)  |  Posted in reply to message from S_Boilermaker 07-25-2024

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