Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Staff (Retired)

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

Files-In-Directory

concatenate-data-tables