cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ShahirahLoqman
Level II

Script to Concatenate all CSV file into One JMP Table

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;

 

);

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Script to Concatenate all CSV file into One JMP Table

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.

View solution in original post

txnelson
Super User

Re: Script to Concatenate all CSV file into One JMP Table

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.

Jim

View solution in original post

3 REPLIES 3

Re: Script to Concatenate all CSV file into One JMP Table

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.

ShahirahLoqman
Level II

Re: Script to Concatenate all CSV file into One JMP Table

thank you so much for this option! However, I can't seem to get it cleaned using a loop script after the files have been imported. Any suggestions as to why this is? 

 

// import csv files from selected folder

thisTable = Multiple File Import(

<<Set Folder( dir),

<<Set Name Filter( "*.*;" ),

<<Set Name Enable( 0 ),

<<Set Size Filter( {254056, 261197} ),

<<Set Size Enable( 0 ),

<<Set Date Filter( {3632893806, 3633422386} ),

<<Set Date Enable( 0 ),

<<Set Add File Name Column( 1 ),

<<Set Add File Size Column( 0 ),

<<Set Add File Date Column( 0 ),

<<Set Import Mode( "CSVData" ),

<<Set Charset( "Best Guess" ),

<<Set Stack Mode( "Stack Similar" ),

<<Set CSV Has Headers( 1 ),

<<Set CSV Allow Numeric( 1 ),

<<Set CSV First Header Line( 1 ),

<<Set CSV Number Of Header Lines( 1 ),

<<Set CSV First Data Line( 2 ),

<<Set CSV EOF Comma( 1 ),

<<Set CSV EOF Tab( 0 ),

<<Set CSV EOF Space( 0 ),

<<Set CSV EOF Spaces( 0 ),

<<Set CSV EOF Other( "" ),

<<Set CSV EOL CRLF( 1 ),

<<Set CSV EOL CR( 1 ),

<<Set CSV EOL LF( 1 ),

<<Set CSV EOL Semicolon( 0 ),

<<Set CSV EOL Other( "" ),

<<Set CSV Quote( "\!"" ),

<<Set CSV Escape( "" )

) << Import Data;

thisTable << Move Selected Columns( {:File Name}, To First );

 

 

// deletes columns "......PV" // stuck here

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

thisTable << Delete Columns(Column(k));

 

);

 

txnelson
Super User

Re: Script to Concatenate all CSV file into One JMP Table

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.

Jim