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:
Question: the nested structure of your text is reminiscent of XML. Is this the original text or some version after some editing, like removing tags?
This is just something that I quickly made that mimics the flat files I work with. We don't have liscensing with the software (HAScientific, SLIM) that exports the .txt file. So I'm actually unsure how the data is natively structured. I think its SQL based, I just don't have keys into that castle. While this is frusterating, the work around I've come up with is a JSL script that reads and cleans the text files.
@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
This is a significantly different way than I was thinking of solving the problem. I think it should work with a little tinkering. Thanks for the help.
