Script to Concatenate all CSV file into One JMP Table

Question

Hi!

My current script to open a directory containing CSV files and concatenating them into one master file is not working, and I was hoping to get insights on why that is. I've already specified variables dir and files, and the loop seems to end after taking the first file, when there are 4 in the folder. Also, is there a script to read the file name of the CSV file and use it to fill a new column?

For( i = 1, i < N Items( files ), i++,

thisTable = Open (dir || files[i]);

// creates new column to indicate SD ID

New Column("EX801.SD.ID",Character, Multiple Response);

For Each Row (:EX801.SD.ID = "Run1" ); // stuck at inputing SD ID - need file name!

thisTable << Move Selected Columns( {:EX801.SD.ID}, To First );

// deletes columns "......PV"

For(i = N Cols (thisTable), i > 32, i--, // 1st 32 columns are needed

thisTable << delete columns(Column(i));

);

// adds "I" in front of each column name to be matched with column name later

For( i = 3, i <= 32, i++,

Column( thisTable, i ) << Set Name( "I" || (Column( thisTable, i ) << Get Name) );

);

dt << Concatenate (thisTable, "Append to First Table");

Close (thisTable);

dt << Select Where (Is Missing(:TIME)) << Delete Rows;

);

Mark_Bailey · Accepted Answer

Select File > Multiple File Import. See Help > Using JMP for details. All of the options can be scripted. Use it interactively the first time to make sure you get the results in a known case. Then copy the Source table script to your own script.

txnelson · Accepted Answer

Your issue is that you are using the variable "i" in two For Loops, where one For Loop is inside of the other....thus, your value of "i" is not being properly incremmented for the outside loop.

I have changed the Loop variable for the inside loops in the below script.  This might be your solution

For( i = 1, i < N Items( files ), i++,
	thisTable = Open( dir || files[i] );

// creates new column to indicate SD ID
	New Column( "EX801.SD.ID", Character, Multiple Response );

For Each Row( :EX801.SD.ID = "Run1" ); // stuck at inputing SD ID - need file name!
	thisTable << Move Selected Columns( {:EX801.SD.ID}, To First );

// deletes columns "......PV"
	For( k = N Cols( thisTable ), k > 32, k--, // 1st 32 columns are needed
		thisTable << delete columns( Column( k ) );

);

// adds "I" in front of each column name to be matched with column name later
	For( k = 3, k <= 32, k++,
		Column( thisTable, k ) << Set Name( "I" || (Column( thisTable, k ) << Get Name) );

);

dt << Concatenate( thisTable, "Append to First Table" );
	Close( thisTable );
	dt << Select Where( Is Missing( :TIME ) ) << Delete Rows;

);

If you are using JMP 14, you may be able to use Multiple File Import() function.