How to use recursive in concatenating CSV files in folders?

[#1]

Hi, can someone help me modify this code?  I am trying to concatenate csv files per folder. The user will have to select a folder and all the qualified csv folders will be concatenated and be saved according to the folder name. This method makes user pick one folder per script run.

However, what I am trying to do now is to make the user pick the main folder then run the script and concatenate the data per subfolders (saving them individually with its folder name as the filename like in the previous method).

But with these code, all the csv files are being concatenated into one file. Can you help modify and explain how recursive works? And how can I save this files individually (concatenate data per folder) with just opening the main folder. I am using JMP 15. Thank you

p = pick Directory("Select a Folder");
//f = Files In Directory(p);
f = Files In Directory(p, recursive(1));

Dir = Word(-1, p, "/"); //get filename
saveDir = Substr( p, 1, Length( p ) - Length( Word( -1, p, "\/" ) ) - 1 ); //save file outside main folder

/* Removes the file names from the list f which are not CSV files */
for(j=1,j<=N Items(f),j++, 
	If (Substr(f[j],Length(f[j])-2,3)!= "CSV", 
		RemoveFrom(f,j,1);
		j=j-1;
	);
);

A = {}; /* creates a empty list for storing the names of each data table */
B = {};
C = {};
D = {};
k = {};
m = {};


/*Opens each one of the CSV files and deletes all unwanted rows */
for(j=1,j<=N Items(f),j++, 
	CSV = Open(Concat(p,f[j]), End of Field(comma), Strip Quotes(1), Labels(1), 
								Column Names Start(3), Data Starts(4), 	Columns(:MRR=Numeric));
	colname = {};
	colname = CSV<<get Column Names(String);
	if(Contains(colname,"Age")>20 & Contains(colname,"Weight")>50, 
		InsertInto(A,CSV);
		CSV<<Select Where(:Sex =="Female");
		CSV<<delete rows;
		CSV<<Select Where(:Sex =="");
		CSV<<delete rows;,
	//Else
		InsertInto(B,CSV);
		InsertInto(k,f[j]);
	);
);

//Concatenating the Group A files
dt1=New Table();
If(N Items(A)>1, 
	dt2 = dt1<<Concatenate(A, Output Table name("All Data"));
	dt2<<Delete Columns("Column 1");
);
Close(dt1, No Save);
//Get column header from the final table
colheader = {};
colheader = dt2<<get Column Names(string);


//Setting the correct column names for the errored files
For(j=1,j<=N Items(B), j++,
	If(Ncol(B[j])< N Items(colheader), temp =Ncol(B(j)), temp =N Items(colheader) );
	For (i=1,i<=temp,i++,
	Column(B[j],i)<<Set name(colheader[i]);
	 );
);
//Concatenating the Group B files
dt1=New Table();
If(N Items(B)>1, 
	dt3 = dt1<<Concatenate(B, Output Table name("Remaining Data"));
	dt3<<Delete Columns("Column 1");
);
Close(dt1, No Save);

//checking if the column numbers are the same among all the files Group A
flga = 0;
for(j=1, j<=N Items(A),j++, 
	If ((NCol(A[1]) != NCol(A[j])) & flga == 0,
	flga = 1;
	);
);

//Closing all the open files
for(j=1, j<=N Items(A),j++, 
Close(A[j], No Save);
);
for(j=1, j<=N Items(B),j++, 
Close(B[j], No Save);
);

//Showing important messages
If(flga ==1, Dialog("Done!", Button("OK")););

Close(dt2,save(char(saveDir)|| Dir ||".jmp"));

[#2]

What do you need explained about recursive(1) option?

Names Default To Here(1);
Files In Directory("$SAMPLE_DATA", recursive(1));

Without that option you will just get the first level of files in the folder and when using recursive you will also get the subfolders and files inside those.

Highlighted ones are there only because recursive(1) was set.

Regarding your script: how many levels of folders do you have? might it change? If there are multiple levels until .csv files are found what should be the file name?

[#3]

Hi, I only have two levels of folders the main folder then the multiple folders with the CSV inside. Here is an example output of the script without using recursive. What I would want to do is when the script run, only the Main folder will be picked and the script will have an output like this.

[#4]

You can easily get the folder name using Word() with negative index. Example below

Names Default To Here(1);
foldername = Word(-1, "F1/1.jmp", "/\"); //"1.jmp"

I would most likely create an associative array with the keys being the folder names. Then I would loop over the file list and check if the folder doesn't already exist in the associative array, then add it there and initialize it's value as empty list. Then you can use insert into to add the file paths to that associative array to correct keys (use full paths so it is easier to open the files). After you have gone over the file list, perform other loop over the associative array. This time open the files and concatenate them as you go

[#5 · Accepted solution]

Here is a starter script that reads in all of the recursive files and concatenates them into separate data tables, based upon the folder they were found in.  

I did not include all of your individual error checks, etc.   You should be able to modify my example to put that stuff back into the code.

There is a structure difference in this code.  the JSL reads in a file, and then concatenates it before reading in the next file.  I checked out as much as I could but it could still have issues.  

names default to here(1);
p = pick Directory("Select a Folder");//f = Files In Directory(p);
f = Files In Directory(p, recursive(1));

for(i=nitems(f),i>=1,i--,
	if(uppercase(word(-1,f[i],"."))!="CSV",
		remove from(f,i,1)
	)
);

// Create the Associative Array with all of the files, with their directories
// Place all data into a data table, and then move into an Associative Array which will give the
// folder(data table to concatenate to)
dt= new table("The Files", new column("File", character));
dt << add rows(nitems(f));
dt:file<<set values(f);
for each row(
	:file=p || :file;	
);
dt << New Column( "folder",
	character,
	set each value( Substr( :File, 1, Length( :file ) - Length( Word( -1, :file, "/\" ))-1  ) )
);

theArray = Associative Array(dt:File<<get values,dt:folder<<get values);
thearray<<get value("/C:/Users/Jim/Documents/Discussion Group/complex3.csv")
// Process the files
For Each( {table}, theArray << get keys,
	stable = theArray << get value( table );
	stable = Substitute( stable, ":", "-" );
	stable = Substitute( stable, "/", "-" );
	If( Try( Data Table( stable ) << get name, "" ) == "",
		dt = New Table( theArray << get value( table ) ),
		dt = Data Table( stable )
	);
	CSV = Open(
		table,
		End of Field( comma ),
		Strip Quotes( 1 ),
		Labels( 1 ),
		Column Names Start( 3 ),
		Data Starts( 4 ),
		Columns( :MRR = Numeric )
	);
/*	CSV << Select Where( :Sex == "Female" );
	CSV << delete rows;
	CSV << Select Where( :Sex == "" );
	CSV << delete rows; */
	dt << new column("Source", character, set each value(table));
	dt << concatenate(CSV, append to first table)
	close(CSV, nosave);
);

[#6]

Hi, I have a question in this line of code as I am still using JMP 15. How can I translate this into JMP 15 (for loop format) I'm kind of confused of what {table} is when it is in the for loop format in JMP 15. And also, how can I save this CSV files accordingly? For my example, it is by sex. So the file names should be saved by sex (Male.csv & Female.csv)

For Each( {table}, theArray << get keys,
	stable = theArray << get value( table );
	stable = Substitute( stable, ":", "-" );
	stable = Substitute( stable, "/", "-" );
	If( Try( Data Table( stable ) << get name, "" ) == "",
		dt = New Table( theArray << get value( table ) ),
		dt = Data Table( stable )
	);

[#7]

I think this should work:

arrayKeys = theArray << get keys;
For(i=1,  i <=nitems( arrayKeys), i++,
	table = arrayKeys[i];
	stable = theArray << get value( table );
	stable = Substitute( stable, ":", "-" );
	stable = Substitute( stable, "/", "-" );
	If( Try( Data Table( stable ) << get name, "" ) == "",
		dt = New Table( theArray << get value( table ) ),
		dt = Data Table( stable )
	);