Fill down after open .txt

danielrbiber

Hello All,

I have a program that will export flat .txt files that are similar to the one attached.  I would like to write a script that will take the first column, create new & fill those values down.  I have been unsuccessful in implementing any JSL I've found on the site.

Utilizing just the JSL script, I would Like to take "Column 1" in "Example Before" split it into three new columns, label according instances in that column and then fill them down until the next valid value.  Like seen in "Example After".

 

Example Before

Example After:

 

 

 

OpenScript.jsl

Example Before.txt

Example Before.jmp

Example After.jmp

gzmorgan0

@danielrbiber, the attached script is written with the nested XML-like structure in mind: get a value that remains the same until changed, and create a new row and write results when time point is changed.  Note this might not handle all instances of your files, but will do what you want for this example text file.

 

You need to change the path of the text file to run.

Names Default to Here(1);
dt0 = Open(
	"C:\Users\_xx\Downloads\Example Before.txt",
	Table Contains Column Headers (1),
	End of Line ( CRLF, CR, LF),
	End of Field (comma, CSV(0)),
	Column Names Start(1));

//create a new table
dt = New Table("Restruct", 
       New Column( "Column 1", Character),
       New Column( "Meta Data", Character),
       New Column( "Data", Numeric),
       New Column( "Project", Character),
       New Column( "Time Point", Character),
       New Column( "Condition", Character) );
//Initialize values
md="";
dv=empty();
tp="";
pj="";
cd="";

crow = 0;
//new rows will be created for new time point
//this might not cover all files, but matches your sample text data.
for(i=1,i<=nrow(dt0),i++,
    cval = dt0:Column 1[i];
	If(cval =="", Continue());
	if(starts with(cval,"PT"), pj=cval);
	if(contains(cval,"/"), cd = cval);
	if(contains(cval,"Initial") | contains(cval,"month"),
	  tp = cval;
	  if(trim(cval)=="Initial", cd = cval);
	  md = dt0:Meta Data[i];
	  dv = dt0:Data[i];
	  dt << add rows(1, At End);
	  crow++; 
	  dt:Column 1[crow]   = cval;
	  dt:Meta Data[crow]  = md;
	  dt:Data[crow]       = dv;
	  dt:Time Point[crow] = tp;
	  dt:Project[crow]    = pj;
	  dt:Condition[crow]  = cd;
	); // end if
); // end for