Concatenate data tables

[#1]

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

Click 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

[Alex-L_NC]

New user here and I found this to be very helpful when trying to consolidate data from many multiple sets. Thanks! 

[Craige_Hales]

@Alex-L_NC Thanks!

[Feli]

Really great snippet for importing multiple Excel-Files, which is a weakness of the MFI.

Also helped for brute force concatenating files that have a slightly different number of columns, which is not possible in MFI.

You can also specify further options when using Open(), which helped me import Excel files with two header lines.

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, Worksheets( "MyWorksheet" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 2 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 3 ),
		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( "-" )
	) ) );
);

[Craige_Hales]

@Feli  Thanks!