Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Neal85
Level II

Run script even if directory changes

I have an excel file that gets updated everyday(rows increase but column header and column no. remain intact).

I need to update the data in jmp file to make statistical analysis.  For this task, I created a script to read the contents of the excel file & to update the jmp table.

I have two questions :

 1) Is there a better way of doing what I am currently doing?

 2) What if I want my other work colleagues to use the same script? I mean they will also have the same excel file,

     but the directory will vary. What I am looking for is something like the script in MATLAB, where the script executes on the

     file that is in the same directory where the script is located?

 

Thanks

Names Default to Here (1);

//current data table in JMP that has earlier data
cdt = Current Data Table();

//Delete all the rows in the current data table

cdt << Select All Rows()<<Delete Rows;

//Holder for the newly added data in the excel file
dt2 = Open("C:\Users\Neal\Desktop\Data collection.xlsx",
	Worksheets( "Data entry" ),
	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( 3 ),
		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( "-" )
	)
);
// Concatenate the JMP Table with the new imported data 
cdt << Concatenate( dt2, Append to first table );

//Close the table that was used to import data
Close( dt2, nosave );
3 REPLIES 3
Highlighted
Georg
Level IV

Re: Run script even if directory changes

You could place the .jsl for conversion in the same Directory as the Excel file,

but Point to a Folder different for each user to save the .jmp file. See the example from Scripting index how to Access the DOCUMENTS Folder specific to each user.

In that way you can Control how the conversion goes from Excel to JMP, but each user has it's own JMP file to work with.

 

Names Default To Here( 1 );
Convert File Path( "$DOCUMENTS" );
/* try: HOME, DOCUMENTS, SAMPLE_DATA, 
SAMPLE_IMPORT_DATA, SAMPLE_SCRIPTS, and TEMP */
Georg
Highlighted
David_Burnham
Super User

Re: Run script even if directory changes

Instead of hard-coding the path to the file, you could use the PickFile function which will allow the user to select the file.

-Dave
Highlighted
David_Burnham
Super User

Re: Run script even if directory changes

Having read your question again:

 

if you have the file in the same directory as the script then you only need to use the file name:

 

dt2 = Open("collection.xlsx", ...
-Dave
Article Labels

    There are no labels assigned to this post.