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

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

zjuv007 — Thu, 20 Jun 2019 19:07:42 GMT

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);
)
);

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

txnelson — Sat, 22 Jun 2019 17:19:23 GMT

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" ) );
	);
);

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

zjuv007 — Sun, 23 Jun 2019 01:59:02 GMT

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.

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

txnelson — Sun, 23 Jun 2019 03:24:25 GMT

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.