371044_en-US

liliconcom

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

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=""></SET>

 

 

Advanced Statistical ModelingAutomation and ScriptingConsumer and Market ResearchData AccessData Blending and CleanupMass CustomizationRe: Help on script to import multiple TXT files and removing certain data JMP 15

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></MODELINGTYPE>

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

Hi Jim,

thanks

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

@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></MODELINGTYPE>

 

 

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

NA

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

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

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

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

</CODE></P> <PRE><CODE class=" language-jsl">// 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 );