topic Re: How do I create a JMP script to import multiple Excel workbooks from SharePoint? in Discussions

How do I create a JMP script to import multiple Excel workbooks from SharePoint?

Onjai — Fri, 09 Jun 2023 22:05:19 GMT

Hi Everyone,

I have several Excel o365 workbooks stored in SharePoint that I need to load to JMP and rename each table based on the filename of the excel workbook.

I found code snippets from the JMP Community to help me, thank you everyone!!

The script:

hard codes the file names and SharePoint paths
opens the SharePoint site
locates the excel file
opens/imports the Excel file

However there are 2 issues. The JMP table names do not rename and there are modal pop up windows that show up that I would like to have closed using script.

I am attaching Big Class data formatted as an Excel Workbook that you can upload to SharePoint. The script is also attached. Make sure you change the hard coded paths and files names to your SharePoint site.

Thank you!

Script:

//SharePoint path, copy link from SharePoint then delete everything after "?"
path = {"https://<<your site>>/Big%20Class.xlsx?", "https://<<your site>>/Big%20Class_2000.xlsx?"};
//SharePoint file, copy link from SharePoint then delete everything after "?" and add "download=1"
file = {"https://<<your site>>/Big%20Class.xlsx?download=1",
"https://<<your site>>/Big%20Class_2000.xlsx?download=1"};

Batch Interactive( 1 );
For( i = 1, i <= N Items( path ), i++, //loop for paths to excel workbooks
    Web( path[i], JMP window );
    For( i = 1, i <= N Items( file ), i++, //loop for excel files
        dt = Open(
            file[i],
            Worksheets( "Big Class" ),
            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( "-" ),

            ),
            "Invisible",

        );
        Window( "Web Browser" ) << close window; //closes the JMP web window
    );
    name = {"Big Class", "Big Class 2000"}; //change table names THIS IS NOT WORKING
    For( i = 1, i <= N Items( name ), i++,
        dt << Set Name( name[i] )
    );
    Batch Interactive( 0 );
);

Re: How do I create a JMP script to import multiple Excel workbooks from SharePoint?

ih — Fri, 05 Feb 2021 16:55:46 GMT

It looks like you are assigning all tables to the same variable, dt. That variable can only hold a reference to a single table at a time. Instead, you could make dt a list, and then let it store references to all of the data tables. Here is what that might look like (not tested).

//SharePoint path, copy link from SharePoint then delete everything after "?"
path = {"https://<<your site>>/Big%20Class.xlsx?", "https://<<your site>>/Big%20Class_2000.xlsx?"};
//SharePoint file, copy link from SharePoint then delete everything after "?" and add "download=1"
file = {"https://<<your site>>/Big%20Class.xlsx?download=1",
"https://<<your site>>/Big%20Class_2000.xlsx?download=1"};

Batch Interactive( 1 );

//Make an empty list to store references to all data tables
dt = list(); 

For( i = 1, i <= N Items( path ), i++, //loop for paths to excel workbooks
	Web( path[i], JMP window );
	For( i = 1, i <= N Items( file ), i++, //loop for excel files
		dt[i] = Open(
			file[i],
			Worksheets( "Big Class" ),
			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( "-" ),

			),
			"Invisible",

		);
		Window( "Web Browser" ) << close window; //closes the JMP web window
	);
	name = {"Big Class", "Big Class 2000"}; //change table names THIS IS NOT WORKING
	For( i = 1, i <= N Items( name ), i++,
		dt[i] << Set Name( name[i] )
	);
	Batch Interactive( 0 );
);

Re: How do I create a JMP script to import multiple Excel workbooks from SharePoint?

Onjai — Mon, 08 Feb 2021 14:58:53 GMT

Adding the dt = list() works.

Thank you!