topic Re: New data added in excel - how to refresh in JMP in Discussions

New data added in excel - how to refresh in JMP

Markn — Fri, 01 Dec 2023 13:45:20 GMT

My 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.

Re: New data added in excel - how to refresh in JMP

txnelson — Fri, 01 Dec 2023 13:50:39 GMT

It 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 JMP

Markn — Fri, 01 Dec 2023 15:55:07 GMT

Thank 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 JMP

txnelson — Fri, 01 Dec 2023 16:32:46 GMT

You 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 JMP

Markn — Fri, 01 Dec 2023 16:55:51 GMT

I 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 JMP

txnelson — Fri, 01 Dec 2023 18:47:20 GMT

I 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 JMP

Markn — Fri, 01 Dec 2023 18:58:40 GMT

Here 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 JMP

Markn — Fri, 01 Dec 2023 19:01:31 GMT

And as a clip:

Re: New data added in excel - how to refresh in JMP

txnelson — Fri, 01 Dec 2023 19:14:35 GMT

Is 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 JMP

Markn — Fri, 01 Dec 2023 19:43:37 GMT

There 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 JMP

txnelson — Fri, 01 Dec 2023 19:59:22 GMT

Remove 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 JMP

Markn — Fri, 01 Dec 2023 20:03:02 GMT

I 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 JMP

Markn — Fri, 01 Dec 2023 20:05:34 GMT

Thank you for all your advice!

Re: New data added in excel - how to refresh in JMP

Markn — Mon, 04 Dec 2023 10:28:23 GMT

It 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 JMP

txnelson — Mon, 04 Dec 2023 13:47:41 GMT

I 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 JMP

Markn — Tue, 05 Dec 2023 06:01:26 GMT

Ok, 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 JMP

Markn — Tue, 05 Dec 2023 06:09:23 GMT

This 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 JMP

Markn — Tue, 05 Dec 2023 06:18:58 GMT

In 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 JMP

Markn — Tue, 05 Dec 2023 06:29:07 GMT

I 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 JMP

jthi — Tue, 05 Dec 2023 07:09:46 GMT

You 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?