topic iterate a List of open data tables and concatenate into a table in Discussions

iterate a List of open data tables and concatenate into a table

Big_Data_Dude — Wed, 22 Jan 2020 13:20:41 GMT

I am trying to concatenate a bunch of log files into a single file and I am using version 13 ... JMP 14 is much easier... Since others will be on jmp 13 for some time I need to make a generic script.

The script I have at this time can generate a bunch of open data tables. That works fine. I can iterate and capture a list of the openDTs ..

But when I try to concatenate I haven't been successful. I do not even need to open the tables, just to concatenate into a single file.

Can someone out there help me to understand this issue?

_____________________________________________________

I ran the concatenate function on one of the open logs and generated this:

Data Table( "test_data_1.log" ) << Concatenate(
	Data Table( "test_data_2.log" ),
	Data Table( "test_data_3.log" ),
	Output Table( "RESULTS" ),
	Create source column
);

___________________________________________________

names = {"/C:/1test/test_data_1.log", "/C:/1test/test_data_2.log", "/C:/1test/test_data_3.log"};
openDTs = {DataTable("test_data_3.log"), DataTable("test_data_2.log"), DataTable("test_data_1.log")};

Name Unresolved: Results in access or evaluation of 'Results' , Results/*###*/

In the following script, error marked by /*###*/


dirpath = Munger( Pick Directory( "JMP 13.xx locate the LOG FILES: " ), 1, "/", "" );
Set Current Directory( dirpath );
names = Files In Directory( dirpath, recursive( 1 ) );
For( i = 1, i <= N Items( names ), i++,
	dt1 = Open(
		names[i] = Convert File Path( dirpath ) || names[i],
		columns(
			Column( "STARTDATETIME", Numeric, "Continuous", Format( "y/m/d h:m:s", 26, 3 ), Input Format( "y/m/d h:m:s", 3 ) ),
			Column( "FIVER", Numeric, "Continuous", Format( "Best", 12 ) ),
			Column( "CLASS", Character, "Nominal" ),
			Column( "BLANK", Character( 1 ), "Nominal" ),
			Column( "TYPE", Character, "Nominal" ),
			Column( "CSTARGET", Character, "Nominal" ),
			Column( "CSACTION", Character, "Nominal" ),
			Column( "NUMCODE", Numeric, "Continuous", Format( "Best", 12 ) ),
			Column( "DEVICE", Character, "Nominal" ),
			Column( "DATA", Character, "Nominal" ),
			Column( "ENDDATE", Numeric, "Continuous", Format( "y/m/d h:m:s", 29, 6 ), Input Format( "y/m/d h:m:s", 6 ) ),
			Column( "JUNK1", Numeric, "Continuous", Format( "y/m/d h:m:s", 29, 6 ), Input Format( "y/m/d h:m:s", 6 ) ),
			Column( "JUNK2", Numeric, "Continuous", Format( "Best", 12 ) )
		),
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Other( "0x7c" ), CSV( 0 ) ),
			Strip Quotes( 1 ),
			Use Apostrophe as Quotation Mark( 0 ),
			Use Regional Settings( 0 ),
			Scan Whole File( 1 ),
			Treat empty columns as numeric( 0 ),
			CompressNumericColumns( 0 ),
			CompressCharacterColumns( 0 ),
			CompressAllowListCheck( 0 ),
			Labels( 0 ),
			Column Names Start( 1 ),
			Data Starts( 1 ),
			Lines To Read( "All" ),
			Year Rule( "20xx" )
		)
	);
	Current Data Table( dt1 );
	dt1 << delete columns( "CLASS", "FIVER", "BLANK", "JUNK1", "JUNK2" );
	dt1 << Select Where(
		Contains( As Column( dt1, "TYPE" ), "Execute" ) | Contains( As Column( dt1, "TYPE" ), "++" ) | Contains(
			As Column( dt1, "TYPE" ),
			"SetState"
		) | Contains( As Column( dt1, "TYPE" ), "SAM" ) | Contains( As Column( dt1, "TYPE" ), "DAC" )
	);
	dt1 << Delete Rows;
);
Show( names );
openDTs = {};
For( i = 1, i <= N Table(), i++,
	Insert Into( openDTs, Data Table( i ) )
);
Show( openDTs );
dt = New Table( "Results" );
For( k = 1, k <= N Items( names ), k++,
	Current Data Table( Results/*###*/ );
	dt << concatenate( Data Table( names[k] ), Output Table( "Results" ), Create Source column );
);

Re: iterate a List of open data tables and concatenate into a table

txnelson — Wed, 22 Jan 2020 17:34:13 GMT

Your Current Data Table() function needs to be changed to

Current Data Table( dt );

or to

Current Data Table( data table( "Results" ) );

However, I typically do the concatenation as each file is read in. To me it seems cleaner

Names default to here(1);
dirpath = Munger( Pick Directory( "JMP 13.xx locate the LOG FILES: " ), 1, "/", "" );
Set Current Directory( dirpath );
names = Files In Directory( dirpath, recursive( 1 ) );

dt = New Table( "Results" );

For( i = 1, i <= N Items( names ), i++,
	dt1 = Open(
		names[i] = Convert File Path( dirpath ) || names[i],
		columns(
			Column( "STARTDATETIME", Numeric, "Continuous", Format( "y/m/d h:m:s", 26, 3 ), Input Format( "y/m/d h:m:s", 3 ) ),
			Column( "FIVER", Numeric, "Continuous", Format( "Best", 12 ) ),
			Column( "CLASS", Character, "Nominal" ),
			Column( "BLANK", Character( 1 ), "Nominal" ),
			Column( "TYPE", Character, "Nominal" ),
			Column( "CSTARGET", Character, "Nominal" ),
			Column( "CSACTION", Character, "Nominal" ),
			Column( "NUMCODE", Numeric, "Continuous", Format( "Best", 12 ) ),
			Column( "DEVICE", Character, "Nominal" ),
			Column( "DATA", Character, "Nominal" ),
			Column( "ENDDATE", Numeric, "Continuous", Format( "y/m/d h:m:s", 29, 6 ), Input Format( "y/m/d h:m:s", 6 ) ),
			Column( "JUNK1", Numeric, "Continuous", Format( "y/m/d h:m:s", 29, 6 ), Input Format( "y/m/d h:m:s", 6 ) ),
			Column( "JUNK2", Numeric, "Continuous", Format( "Best", 12 ) )
		),
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Other( "0x7c" ), CSV( 0 ) ),
			Strip Quotes( 1 ),
			Use Apostrophe as Quotation Mark( 0 ),
			Use Regional Settings( 0 ),
			Scan Whole File( 1 ),
			Treat empty columns as numeric( 0 ),
			CompressNumericColumns( 0 ),
			CompressCharacterColumns( 0 ),
			CompressAllowListCheck( 0 ),
			Labels( 0 ),
			Column Names Start( 1 ),
			Data Starts( 1 ),
			Lines To Read( "All" ),
			Year Rule( "20xx" )
		)
	);
	Current Data Table( dt1 );
	dt1 << delete columns( "CLASS", "FIVER", "BLANK", "JUNK1", "JUNK2" );
	dt1 << Select Where(
		Contains( As Column( dt1, "TYPE" ), "Execute" ) | Contains( As Column( dt1, "TYPE" ), "++" ) | Contains(
			As Column( dt1, "TYPE" ),
			"SetState"
		) | Contains( As Column( dt1, "TYPE" ), "SAM" ) | Contains( As Column( dt1, "TYPE" ), "DAC" )
	);
	dt1 << Delete Rows;
	dt1 << rerun formulas; // make sure all dt1 operations are complete
	
	// Concatenate to the Results data table
	dt << concatenate( dt1, Append to first table( 1 ), Create Source Column );
	
	// To keep the work environment clean, I normally delete the latest input data table
	close( dt1, nosave );
);

Re: iterate a List of open data tables and concatenate into a table

Big_Data_Dude — Thu, 23 Jan 2020 22:37:36 GMT

Jim - Thank you... did an overhaul.... wihtout parsing the data into columns I open the files, then add a source column. Then concatenate. Then take the concatenated file and parse it out. Its faster and more logical progression. added The following:

// create the variables to set the column name and
// define the column value
cName = "SOURCE";
fName = dt1 << get name;
dt1 << New Column( cName, Character, Nominal, Set each value( fName ) );

Then iterate the open tables:
// define a list of open data tables.
openDTs = List();
For( i = 1, i <= N Table(), i++,
	Insert Into( openDTs, Data Table( i ) )
);
// Loop through the data tables again and concatenate
For( k = 2, k <= N Items( openDTs ), k++,
	Current Data Table();
	dtUpdated = Current Data Table() << Concatenate( openDTs[k] );
	Close( openDTs[k], nosave );
);

It is working well now.