topic Re: Help on script to import multiple TXT files and removing certain data JMP 15 in Discussions

Help on script to import multiple TXT files and removing certain data JMP 15

ClaireatBH — Sat, 10 Jun 2023 23:27:43 GMT

Hello,

I would like to import a number of TXT files into one data table.

An individual TXT file looks like this and I would like to only import the highlighted data and if possible create a new column to capture the batch number.

I’ve written the following script (below) which gets me partially there but I still have to delete some of the data as highlighted below. This is ok when I only have a few files but when there are more than 10 it’s a problem.

Can anyone recommend an addition to the script to resolve this issue? I'm currently using JMP 15.

Thank you!

Multiple File Import(
	<<Set Folder(
		"C:\folder"
	),
	<<Set Show Hidden( 0 ),
	<<Set Subfolders( 0 ),
	<<Set Name Filter( "*.txt; " ),
	<<Set Name Enable( 1 ),
	<<Set Size Filter( {3334, 3334} ),
	<<Set Size Enable( 0 ),
	<<Set Date Filter( {3694079995.454, 3694079995.454} ),
	<<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( 4 ),
	<<Set CSV EOF Comma( 1 ),
	<<Set CSV EOF Tab( 0 ),
	<<Set CSV EOF Space( 0 ),
	<<Set CSV EOF Spaces( 1 ),
	<<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( "" ),
	<<Set XML Method( "guess" ),
	<<Set XML Guess( "huge" ),
	<<Set XML Settings( XML Settings() ),
	<<Set JSON Method( "guess" ),
	<<Set JSON Guess( "huge" ),
	<<Set JSON Settings( JSON Settings() ),
	<<Set Import Callback( Empty() )
) << Import Data

Re: Help on script to import multiple TXT files and removing certain data JMP 15

benfrancis — Wed, 24 Mar 2021 13:52:02 GMT

Hi @ClaireatBH,

In addition to your Multiple File Import code, the JSL below should do the trick. It was mostly put together with the enhanced log in JMP 16, just released yesterday.

Kind Regards,

Ben

// Setup and point to open data table
Names Default to Here(1);
dt = Current Data Table();	

// Highlight Batch# rows
dt << Select Where( :Column 1 == "Batch#" );
// Make a subset of Batch# rows
dt_batch = dt << Subset( Output Table( "Batch Numbers" ), Selected Rows( 1 ), selected columns( 0 ) );
// Change name in subset so doesn't overwrite when joined back onto
dt_batch:Time << Set Name("Batch No");

// Delete unwanted rows
dt << Select Where( Is Missing( :V 2 ) ) << Delete Rows;

// Pull Batch No into main table
dt << Update(
	With( dt_batch ),
	Match Columns( :File Name = :File Name ),
	Add Columns from Update Table( :Batch No ),
	Replace Columns in Main Table( None )
);

// Close subset with Batch No
Close(dt_batch, nosave)

// Move selected column: Batch No
Data Table( "SAS_TEST_FILE_FROM_1_SAS_TEST_FILE_FROM_3" ) <<
Move Selected Columns( {:Batch No}, after( :Column 1 ) );


// Delete column: Column 1
Data Table( "SAS_TEST_FILE_FROM_1_SAS_TEST_FILE_FROM_3" ) <<
Delete Columns( :Column 1 );

Re: Help on script to import multiple TXT files and removing certain data JMP 15

ClaireatBH — Wed, 24 Mar 2021 15:57:03 GMT

@benfrancis That works perfectly. Thank you for your help!

Re: Help on script to import multiple TXT files and removing certain data JMP 15

kendy — Fri, 10 Feb 2023 22:09:38 GMT

Re: Help on script to import multiple TXT files and removing certain data JMP 15

txnelson — Thu, 09 Feb 2023 15:00:52 GMT

@kendy Here is a script that should convert your Measure column to separate columns for each parameter

Names Default To Here( 1 );
dt = Current Data Table();

// Create a new column to hold the parameter name
dt << New Column( "Parameter",
	character,
	set each value(
		If( Left( :col1, 1 ) == "[",
			temp = Word( 1, :col1, "[]" );
		);
		temp;
	)
);

// Find all measurement rows and subset them to a new table
dt << select where(isMissing(Num(:col2))==0);

dtMeasures = dt << subset( selected rows(1), selected columns(0));

// Convert the Measure data to numeric
dtMeasures:col4 << datatype(numeric)<<modelingtype(continuous);

// Split the table to create the new measurement table
dtFinal = dtMeasures << Split(
	Split By( :Parameter ),
	Split( :col4 ),
	Output Table( "Final" ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Cleanup
close( dtMeasures, nosave );
dt << delete columns(Parameter);

Re: Help on script to import multiple TXT files and removing certain data JMP 15

kendy — Fri, 10 Feb 2023 04:01:59 GMT

Hi Jim,

thanks :)

Re: Help on script to import multiple TXT files and removing certain data JMP 15

txnelson — Fri, 10 Feb 2023 03:48:03 GMT

I am guessing that there is a blank character in front of the [D105_Unf25]. The below script should fix that

Names Default To Here( 1 );
dt = Current Data Table();

// Create a new column to hold the parameter name
dt << New Column( "Parameter",
	character,
	set each value(
		If( Left( trim(:col1), 1 ) == "[",
			temp = Word( 1, trim(:col1), "[]" );
		);
		temp;
	)
);

// Find all measurement rows and subset them to a new table
dt << select where(isMissing(Num(:col2))==0);

dtMeasures = dt << subset( selected rows(1), selected columns(0));

// Convert the Measure data to numeric
dtMeasures:col4 << datatype(numeric)<<modelingtype(continuous);

// Split the table to create the new measurement table
dtFinal = dtMeasures << Split(
	Split By( :Parameter ),
	Split( :col4 ),
	Output Table( "Final" ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Cleanup
close( dtMeasures, nosave );
dt << delete columns(Parameter);