cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
uday_guntupalli
Level VIII

How to open private data tables in loop

All, 
     I have a reasonably simple problem. It is my understanding that when you open the data table as private, you would lose the table when you don't retain the reference of the data table name. So, how do you retain the data tables especially when you are opening them as a part of a loop. For e.g.: 

 

FilePath = "C:\Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		dt = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					Use for all sheets( 0 ),
					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( "-" )
									  )
				 );
		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 

// This fails for(i = 1, i <= N Items(OpenDts), i++, OpenDts[i] << Show Window(1); );
Best
Uday
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: How to open private data tables in loop

To clarify, you don't need to try to insert into a different List (your "OpenDTS"). Use a list on the left side of the Open() statement, in place of "dt".

 

FilePath = "~/Desktop/Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This fails 
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << Show Window(1); 
   );	
-Jeff

View solution in original post

4 REPLIES 4
Jeff_Perkinson
Community Manager Community Manager

Re: How to open private data tables in loop

You need to store your data table references in a list instead of reusing the dt variable.
-Jeff
Jeff_Perkinson
Community Manager Community Manager

Re: How to open private data tables in loop

To clarify, you don't need to try to insert into a different List (your "OpenDTS"). Use a list on the left side of the Open() statement, in place of "dt".

 

FilePath = "~/Desktop/Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This fails 
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << Show Window(1); 
   );	
-Jeff
uday_guntupalli
Level VIII

Re: How to open private data tables in loop

@Jeff_Perkinson

       It still fails.  I used the exact script and I do see that the data table references are stored into the list. However, when I try to make them visible, that still fails .

Best
Uday
uday_guntupalli
Level VIII

Re: How to open private data tables in loop

@Jeff_Perkinson
 You know what, I see the mistake, instead using the window command, I should have created a new data view since it is a private data table. 

FilePath = "C:\Test.xlsx"; 
SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This works
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << New Data View; 
   );	
Best
Uday