New data added in excel - how to refresh in JMPMy source data is in excel. I created a script to import the data with wizard into JMP and then further scripts do many things in JMP, ultimately creating graphs. And the scripts re-run just fine as long as I just look at the original data. But when I add new rows in the excel and run the scripts, the new data is not included there. I just see the same data that was in the excel when I created the scripts for the first time.
I have not worked for a long with JMP and never written a script of my own.
Automation and ScriptingRe: New data added in excel - how to refresh in JMPIt looks like your original data table is still open in JMP. Try closing the original table, and then rerun the script.
Re: New data added in excel - how to refresh in JMPThank you, I tried it but it does not seem to solve the issue. I made a simplified test with single data column in excel like this:
1. I created, saved and closed excel file.
2. I opened JMP and imported the single data column from excel with wizard.
3. I saved the script in the data table.
4. I closed JMP (without saving actual data).
5. I opened excel, added new rows with data, saved and closed.
6. I opened JMP file and run the script. I still got only the original data.
Below is a clip from the script. It seems that the script has fixed the original numbers as set values.
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
)
),
New Column( "1",
Numeric,
"Continuous",
Format( "Best", 15 ),
Set Values( [2, 3, 4, 5, 6, 7, 8, 9, 10, 1000, 1000, 1000, 1000, 1000] )
Re: New data added in excel - how to refresh in JMPYou state "saved the script in the data table". What Script are you saving?
You also state "I opened JMP file and run the script. I still got only the original data". Are you opening the JMP data table, and then running the script that you saved in the data table? .
Re: New data added in excel - how to refresh in JMPI saved the the script that was generated when I imported data from the excel for the first time. I used the wizard to import the data. After that I saved the script in the data table.
Yes, later on I open again the JMP data table (which has no saved data) and then I run the script which I saved earlier in the JMP data table. And it produces only the original data.
Re: New data added in excel - how to refresh in JMPI suspect the saved script has some hardwired code in it that is preventing the new data from being opened. Could you share the saved script?
Re: New data added in excel - how to refresh in JMPHere you are:
New Table( "Sheet1",
Add Rows( 14 ),
New Script(
"Source",
Open(
"/C:/Work/OneDrive - XXXXXXXX / YYYYYYYY docs and files/JMP Script test.xlsx",
Worksheets( "Sheet1" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
)
),
New Column( "1",
Numeric,
"Continuous",
Format( "Best", 15 ),
Set Values( [2, 3, 4, 5, 6, 7, 8, 9, 10, 1000, 1000, 1000, 1000, 1000] )
)
)
Re: New data added in excel - how to refresh in JMPAnd as a clip:
Re: New data added in excel - how to refresh in JMPIs the data table always only going to have 14 rows?
The new column called "1" will always only have 14 values, and they will always have the values specified in the Set Values list.
Re: New data added in excel - how to refresh in JMPThere will be more and more rows in the data table as new data is added.
I did not write the script, I only saved what was generated during the session. And at no time wizard asked if number of rows and corresponding values should be fixed.
Re: New data added in excel - how to refresh in JMPRemove or comment out the line
Add Rows(14),
Then run the script and see if you get the data you want.
The new column called "1", what is that all about? What is it's function?
Re: New data added in excel - how to refresh in JMPI must have made some error here because I am not familiar with the scripts. Now it seems to work, doing like this:
When I open a new data table in JMP (let it be JMP1) to get data from the excel for the first time, the wizard generates another JMP data table (let it be JMP2) where the data appears. The script appears automatically as "Source" in JMP2. I copy that and paste it into the JMP1 and save it. Then I close both JMP1 and JMP2 (without saving JMP2). Then I add rows into excel. When I open JMP1 and run the script, I do get full data. Not sure what I have messed but seems to work now.
Re: New data added in excel - how to refresh in JMPThank you for all your advice!
Re: New data added in excel - how to refresh in JMPIt seems that my issue was not fully solved, after all. Let's try again.
My source data is an excel table where new data points (rows) will be added on weekly basis.
I want to update my JMP graphs every week, with the new data points from the excel added into JMP graphs.
I have a script which imports all the data from the excel into JMP, including the latest updated data, and that works now OK.
But when I work further with the tables and scripts, I get into trouble.
I manipulate the imported data in JMP (convert data type, stack columns etc.). After doing this I save the scripts and then I see that the scripts have defined the exact values in the table as set values.
So when I re-use the saved scripts later on for new data, the scripts only use the original set values. And I would like them to use all the values.
Simplified example:
Excel includes data elements A, B and C.
I have saved in JMP data table X. It includes Script 1 which imports the data into JMP, creating a new JMP table Y. This works as it should.
I manipulate data in table Y.
I save the manipulation script as Script 2 in Table X, with intention to use it every time when I bring in new data into JMP. At this time I note that Script 2 specifies set values for data: A, B and C.
I close Table Y without saving it (because Y must be generated again every time with new data)
I add in excel a new data element, D.
I run Script 1 (from Table X) to bring into JMP all data elements (A, B, C, D), in a new table Y .
When I run Script 2 (from Table X), it only considers set values A, B and C, as they are defined set values in Script 2. Thus, new data D is not included.
How to build Script 2 so that it does not use only original, fixed set values but all the values, old and new?
Re: New data added in excel - how to refresh in JMPI think I am finally understanding the issue. Your Script 2 that you are saving is "hard wired" to work only with the columns that present in the Excel file when Script 2 was generated. You want to know what has to be changed to make Script 2 generic to work with the Excel file, no matter what new columns or rows have been added to the Excel file.
What changes that will have to be made are going to be dependent upon the specifics that are in your Script 2. It will have to be examined, and then the code that it will take to look into the new data table and see what the changes in columns are and then have the JSL create the new commands to generate the results. You will need to share the complete Script 2 that is currently being generated, and also, the JSL or the steps that you go through to generate Script 2. Only then can someone help with what it will take to make the JSL work in a generic fashion.
Re: New data added in excel - how to refresh in JMPOk, it goes like this. Here is Script 1 I use to import data from excel. That works just fine.
Re: New data added in excel - how to refresh in JMPThis is the data I get into JMP (one point missing in Column 2 because there was no data in excel)
Then I use Script 2 to manipulate that data I brought into JMP. And the script uses fixed set values which have been saved in the script, instead of getting every time all the values, old and new.
Re: New data added in excel - how to refresh in JMPIn this simplified example I am just summing Col1 + Col 2. This is how the screen looks like. I have save both Scripts in "JMP Excel test" table. Then I run Script to bring data into Sheet 1. Then I run Script 2 to sum Col1 +Col2 into Col3. The outcome appears in new Sheet 2. The last data point (1000 in Col1 of Sheet 1) was added into excel after Script 2 was created. So even though it is brought into Sheet1 by Script1, it is not anymore noted by Script 2 (using only set values). Thus, row 11 has no values in Table 2.
Re: New data added in excel - how to refresh in JMPI created Script 2 like this.
When working for the first time with data in Sheet 1, I sum Col 1 with Col 2. Then, under red triangle, I copy the script with "Copy Table Script". This will be the Script 2.
Then I go to table "JMP Excel test", and under red triangle I give "New script" and paste Script 2 there and save it in "JMP Excel test".
I'm just learning about scripts so perhaps there is some fundamental mistakes I have made here?
Re: New data added in excel - how to refresh in JMPYou want to use Source script and not the table script. Which JMP version you have? If you have JMP16+ you can Enhanced Log to capture your actions and if you have JMP17+ you could also try using Workflow builder. Few questions, these do affect how you might have to handle this as JMP cannot know what the excel did look like earlier.:
- Do you always pull all the data from the excel file?
- Or does some data get removed from your excel file and new data added?
- If data gets removed, how would you know what data to pull? Does your excel have some sort of ID column?
- Where does the data come from to the excel file?
Re: New data added in excel - how to refresh in JMPI have JMP 17. I'm not yet familiar with Source Script and Workflow builder so I need to look into those. Thanks for the advice. Any recommendations where to start?
Then answers:
- Yes, all the data comes from the excel file
- Individual old data points in the excel may be removed/modified (=corrected) whenever excel table is updated. But 99% of time it is just adding new data (=rows) at the bottom of existing columns.
- All the data points from the excel are pulled out every time when we update our status. We want to observe cumulative results. Each column includes (and is labeled accordingly) values for a specific parameter.
- People (who do not work with JMP) manually fill in the data into the excel file.
Re: New data added in excel - how to refresh in JMPIn your case, I feel like creating Workflow might be the easiest option to get started as you don't really want to update JMP table but rather always re-create new table.
Create new workflow
Start recording actions
Here I have excel opened and I have added new column
You can stop recording at this point, as I feel like this is all you need.
Then you can save your workflow and just rerun it to create new table whenever you need one.
Re: New data added in excel - how to refresh in JMPThanks for the advice Jarmo, this looks quite clear. In the real JMP table I need to do more data manipulation and then create several graphs but if the whole thing can be managed simply by recording the steps in the workflow, then this looks indeed like a viable solution. I will try this out, thank you!
Re: New data added in excel - how to refresh in JMPNow it works! The structure and flow which I created are perhaps a little bit clumsy - but they serve the purpose and now I have a way forward, to further refine the solution. Thank you again Jarmo.
Re: New data added in excel - how to refresh in JMPYou can also export it as a script and it might be easier to modify that way.
Also, if you start modifying JMP created scripts, this video can be very helpful Better JSL Smoother Migration: How To Fix Your Own Code
