BookmarkSubscribeRSS Feed
Craige_Hales

Staff

Joined:

Mar 21, 2013

Concatenate data tables

Problem

Concatenate data tables using Files in Directory, without using Multiple File Import

 

Solution

Use Files in Directory to make a list and Concatenate to put them together.

 

// make a test directory
dt = New Table( "Untitled", Add Rows( 1 ), New Column( "a" ), New Column( "b" ) );
path = "$TEMP/test/";
Delete Directory( path ); // Be careful! This will delete your files!
Create Directory( path );
For( i = 1, i <= 3, i += 1,
	dt:a = i; // make each table a little different
	dt:b = 10 * i;
	dt << save( path || Char( 1000 + i ) ); // make the table names sort 
);
Close( dt, nosave );

// here's the code to open and concatenate...
namelist = Files In Directory( path );
Sort List Into( namelist ); // not really needed?
dtlist = {}; // hold the opened, hidden, tables here
While( N Items( filename = Remove From( namelist, 1 /*first item*/ ) ) == 1,
	filename = filename[1]; // {dt} -> dt
	filename = path || filename; // prepend path name
	// inserts opened table at end of dtlist
	Insert Into( dtlist, Open( filename, invisible ) );
);
rememberdt = dtlist; // need these later to close the hidden tables
firstdt = Remove From( dtlist, 1/*first item*/ )[1]; // need one to start
If( N Items( dtlist ), // concatenate needs more than one table
	resultdt = firstdt << Concatenate( dtlist, Create source column )
, // subset returns a fresh table, like concatenate, so both cases have same behavior
	resultdt = firstdt << Subset( All rows, Selected columns only( 0 ) )
);
// clean up the hidden tables
While( N Items( closedt = Remove From( rememberdt ) ) == 1, Close( closedt[1], nosave ) );
// clean up new table
resultdt << setname( "all my tables" );
resultdt << deletetableproperty( "Source" ); // from subset or concatenate
//
Print( "done", resultdt );

 

Discussion

JMP 14 introduce Multiple File Import; @wu noticed it did not preserve some table attributes. (JMP 15 should do better, but is not available yet.)

The first part of this example uses DeleteDirectory and CreateDirectory to make a clean set of sample files for testing. You might discover DeleteDirectory fails if any of the JMP tables are still open from a previous attempt; you can close the tables manually, even if they are hidden, using the table widget

 

tableWidget.PNGClick the table icon beside the widget to open the hidden table

That same code adds 1000 to the file names to make them sort nicely ( not 1,10,2,3,4,5,6,7,8,9 ). The path variable has a trailing slash so the slash doesn't have to be added in other places.

namelist is initialized by Files In Directory; dtlist is initialized to an empty list. The names are removed, one by one, from namelist and opened (hidden). The opened tables are stored one by one in dtlist. (That's just a pointer, or handle, to the table, not the contents of the table.)

Then, if there is more than one dtlist item, concatenate makes a new table from them. (I was happy to find concatenate accepts a list of tables.) If there is only one, subset makes a copy. Making a copy makes an unsaved table, just like concatenate made, so the results are similar in both cases.

Clean up removes the hidden tables and renames the result table and removes the source script from the result. You might want to keep the source script. This example could also use Delete Directory at the end, but it is probably a bad idea to do that to real source files.

 

See Also

Files-In-Directory

concatenate-data-tables

Article Labels
Article Tags
Contributors