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

Script to Concatenate all CSV file into One JMP Table

ShahirahLoqman — Thu, 21 Feb 2019 15:19:49 GMT

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;

);

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

Mark_Bailey — Thu, 21 Feb 2019 15:25:26 GMT

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.

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

txnelson — Thu, 21 Feb 2019 15:37:10 GMT

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.

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

ShahirahLoqman — Fri, 22 Feb 2019 01:57:12 GMT

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));

);