topic Re: How to open private data tables in loop in Discussions

How to open private data tables in loop

uday_guntupalli — Fri, 07 Dec 2018 22:28:18 GMT

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

Re: How to open private data tables in loop

Jeff_Perkinson — Fri, 07 Dec 2018 23:19:09 GMT

You need to store your data table references in a list instead of reusing the dt variable.

Re: How to open private data tables in loop

Jeff_Perkinson — Sat, 08 Dec 2018 14:07:12 GMT

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

Re: How to open private data tables in loop

uday_guntupalli — Sat, 08 Dec 2018 14:34:40 GMT

@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 .

Re: How to open private data tables in loop

uday_guntupalli — Sat, 08 Dec 2018 14:55:12 GMT

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