Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
zjuv007
Level III

How do I save a table as a variable name, given the year of the data?

For example: I have two excel files: 2014.xlsx, 2015.xlsx, and would like to automate after selecting these multiple files to name specific worksheets within these files (in this case {Product Events, Products, CodesFrmPLI}), for a total of 6 different JMP tables: Name examples would be Product_Events_2014, Product_Events_2015, Products_2014, Products_2015, CodesFrmPLI_2014, CodesFrmPLI_2015 for this case.

 

Names Default To Here( 1 );
Close All( data tables );
Delete Symbols();
Directory = Pick Directory( "Select a directory" );
Files = Pick File(
"Select Data Set(s)",
Directory,
{"All Files|*", "JMP Files|jmp;jsl;jrn", "Excel Files|xlsx"},
1,
0,
"",
"multiple"
);
For( i = 1, i <= N Items( Files ), i++,
Try( Open( Files[i],
Worksheets( NWorksheet = {"Product Events", "Products", "CodesFrmPLI"}),
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( 1 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" ),
invisible
) )
));
Show(NWorksheet);

//*** 2014 Naming convention
For( i = 1, i <= N Items( Files ), i++,
if( Contains( Files[i] , "2014") > 0,
dt = Data Table(1);
dt << Save("Codes_2014.jmp");
dt = Data Table(2);
dt << Save("Products_2014.jmp");
dt = Data Table(3);
dt << Save("Product Events_2014.jmp");
//Close All(Data Tables, No Save);
)
);

//*** 2015 Naming convention
For( i = 1, i <= N Items( Files ), i++,
if( Contains( Files[i] , "2015") > 0,
dt = Data Table(4);
dt << Save("Codes_2015.jmp");
dt = Data Table(5);
dt << Save("Products_2015.jmp");
dt = Data Table(6);
dt << Save("Product Events_2015.jmp");
//Close All(Data Tables, No Save);
)
);
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: How do I save a table as a variable name, given the year of the data?

I think the below rework of your script will get you where you want.......I did not have a very extensive test environment, but I think the code is pretty close to what you need:

Names Default To Here( 1 );
Close All( data tables );
Delete Symbols();
Directory = Pick Directory( "Select a directory" );
Files = Pick File(
	"Select Data Set(s)",
	Directory,
	{"All Files|*", "JMP Files|jmp;jsl;jrn", "Excel Files|xlsx"},
	1,
	0,
	"",
	"multiple"
);

// Place the names of the spreadsheets to be input into a list
spreadsheetList = {"Product Events", "Products", "CodesFrmPLI"};

For( i = 1, i <= N Items( Files ), i++,
	// For this file, determine the year
	If(
		Contains( Files[i], "2014" ), theYear = "2014",
		Contains( Files[i], "2015" ), theYear = "2015"
	);
	
	// Read in separately each separate spreadsheet
	For( k = 1, k <= N Items( spreadsheetList ), k++,
	
		// Create a list that will contain the current spreadsheet to
		// be read in
		extractList = {};
		Insert Into( extractList, spreadsheetList[k] );
		
		// Open the spreadsheet
		Try(
			dt = Open(
				Files[i],
				Worksheets( NWorksheet = extractList ),
				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( 1 ),
					Multiple Series Stack( 0 ),
					Import Cell Colors( 0 ),
					Limit Column Detect( 0 ),
					Column Separator String( "-" ),
					invisible
				)
			)
		);
		
		// The name for the JMP file is the same as the spreadsheet name, 
		// except for the Codes, so make the change
		If( extractList[1] == "CodesFrmPLI",
			extractList[1] = "Codes"
		);
		
		// Save the JMP file with the prescribed name
		close( dt, save( extractList[1] || "_" || theYear || ".jmp" ) );
	);
);

 

Jim

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: How do I save a table as a variable name, given the year of the data?

I think the below rework of your script will get you where you want.......I did not have a very extensive test environment, but I think the code is pretty close to what you need:

Names Default To Here( 1 );
Close All( data tables );
Delete Symbols();
Directory = Pick Directory( "Select a directory" );
Files = Pick File(
	"Select Data Set(s)",
	Directory,
	{"All Files|*", "JMP Files|jmp;jsl;jrn", "Excel Files|xlsx"},
	1,
	0,
	"",
	"multiple"
);

// Place the names of the spreadsheets to be input into a list
spreadsheetList = {"Product Events", "Products", "CodesFrmPLI"};

For( i = 1, i <= N Items( Files ), i++,
	// For this file, determine the year
	If(
		Contains( Files[i], "2014" ), theYear = "2014",
		Contains( Files[i], "2015" ), theYear = "2015"
	);
	
	// Read in separately each separate spreadsheet
	For( k = 1, k <= N Items( spreadsheetList ), k++,
	
		// Create a list that will contain the current spreadsheet to
		// be read in
		extractList = {};
		Insert Into( extractList, spreadsheetList[k] );
		
		// Open the spreadsheet
		Try(
			dt = Open(
				Files[i],
				Worksheets( NWorksheet = extractList ),
				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( 1 ),
					Multiple Series Stack( 0 ),
					Import Cell Colors( 0 ),
					Limit Column Detect( 0 ),
					Column Separator String( "-" ),
					invisible
				)
			)
		);
		
		// The name for the JMP file is the same as the spreadsheet name, 
		// except for the Codes, so make the change
		If( extractList[1] == "CodesFrmPLI",
			extractList[1] = "Codes"
		);
		
		// Save the JMP file with the prescribed name
		close( dt, save( extractList[1] || "_" || theYear || ".jmp" ) );
	);
);

 

Jim

View solution in original post

Highlighted
zjuv007
Level III

Re: How do I save a table as a variable name, given the year of the data?

Thank you Jim. This worked. Quick follow-up: If I'd like to now perform joining of tables and manipulation of data, where would be the best place for this?

Regards.

Highlighted
txnelson
Super User

Re: How do I save a table as a variable name, given the year of the data?

If you just remove the line

close( dt, save( extractList[1] || "_" || theYear || ".jmp" ) );

The data tables will remain open.  Therefore,  you can add any joins or calculations you want after the closing ")" for the initial For() loop.  Basically, after the last line in the current script.

Jim
Article Labels

    There are no labels assigned to this post.