topic Re: script after file import not executing in Discussions

script after file import not executing

r30363 — Fri, 09 Jun 2023 19:48:28 GMT

hi, I am importing bunch of files from a directory and stacked them into one big file(45 files), after import into one big file, the script will clean up the dataset, add columns and rows etc. the problem I encountered is that the script will error out if being executed together as a whole piece; if I run the import first, wait for the files to load into jmp, and then run the 2nd portion of the code, it will work. I had wait(150) in between which is more than enough time to import the data, but it still does not work. the error message says " invalid argument in access or evaluation of "Column" Column/*###/(j). I did notice the script immediate after import for PVOTC update is not executed neither. Below is my code, your help is greatly appreciated.

files = Files In Directory( loadPath);
For( i = 1, i <= N Items( files ), i++, //previous steps non drb or already parsed)
	subLoadPath = loadPath ||files[i];
	stepID = num(word(2,word(-1,subLoadPath,"/" ),"_"));
	If( Is directory(subLoadPath) & stepID >= 11, // 11 is the step started to parse
 		tableName = word(-1,subLoadPath,"/" );
		tableName_flow = word(-2,subLoadPath,"/" );

		dt = Multiple File Import(
			<<Set Folder(subLoadPath),
			<<Set Name Filter( "*.csv2*.*;" ),
			<<Set Name Enable( 1 ),
			<<Set Size Filter( {2930760, 2974144} ),
			<<Set Size Enable( 0 ),
			<<Set Date Filter( {3697799737.307, 3697800543.878} ),
			<<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;
		Wait(150);
		dt << Set Name (tableName);
		Wait(3);

		dt = Current data table();
		Wait(3);
		dt << Begin Data Update;
			For Each Row( :PVOTC = Match( :PVOTC, ., 1, :PVOTC ) );
			dt << End Data Update;
		Wait(5);


		//dt = Current data table();
		For( j = N Col( dt ), j >= 1, j--,
			   If( (Col N Missing( Column( j ) ) / N Row()) == 1,
					  dt << delete columns( j )
			   )
		);
		Wait(5); 
		dt << Clear Column Selection();

Re: script after file import not executing

Craige_Hales — Thu, 03 Jun 2021 19:27:05 GMT

After

dt = Multiple File Import(...) << Import Data;

dt holds a list of data tables, even if only one table is imported.

You don't need the wait(150).

dt[1] will be the current data table if only one table was imported.

edit: don't use current data table() if you can avoid it. Use an explicit handle like dt[1] if possible. Current Data Table is a JSL function that tries to behave like JMP, before JSL was invented, where the last table touched would be the table used by the next platform.

Re: script after file import not executing

r30363 — Thu, 03 Jun 2021 20:23:56 GMT

Craige

Thanks for your reply, now I made the change but the code still can't be executed as whole, PVOTC missing value is not updated, also see the same error message "invalid argument in access or evaluation of 'Column' , Column/*###*/(j)"

dtImpt = Multiple File Import(
			<<Set Folder(subLoadPath),
			<<Set Name Filter( "*.csv2*.*;" ),
			<<Set Name Enable( 1 ),
			<<Set Size Filter( {2930760, 2974144} ),
			<<Set Size Enable( 0 ),
			<<Set Date Filter( {3697799737.307, 3697800543.878} ),
			<<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;
		dt = dtImpt[1];
		dt << Set Name (tableName);
		
		//if PVOTC has missing value, assign 1 	
		dt << Begin Data Update;
			For Each Row( :PVOTC = Match( :PVOTC, ., 1, :PVOTC ) );
			dt << End Data Update;

		//delete column with no value
		For( j = N Col( dt ), j >= 1, j--,
			   If( (Col N Missing( Column( j ) ) / N Row()) == 1,
					  dt << delete columns( j )
			   )
		);
		dt << Clear Column Selection();

Re: script after file import not executing

Craige_Hales — Thu, 03 Jun 2021 21:56:24 GMT

Probably more than one table open, try being explicit, like this

If( (Col N Missing( Column(dt, j ) ) / N Row()) == 1

I think the column function is using a different table (actually, the current table) than the one you expect. Adding the dt to the column function tells it which table, otherwise it is the current table (which may not have caught up when you run it in one go.)