topic Re: Script: Remove prefix of string and output modified column to Excel file in Discussions

Script: Remove prefix of string and output modified column to Excel file

mostarr — Sat, 10 Jun 2023 23:33:52 GMT

Hi everyone. I have been following the advice at and some other references for writing data to files, mostly applicable to chunk text files but I found the

Save Excel File()

function in the Scripting Index. I don't know what the last parameter means/does? It isn't explained. That is to say,

Separate Rows for Each Cell Statistic( 1 )

Is this important? It doesn't write anything to any file in the location I have specified whether this parameter is present or not.

Here is a rough outline of my script:

dt = Current Data Table();
c = Words(:ID, "_");
Write(c);
s = c[2] || "_" || c[3] || "_" || c[4] || "_" || c[5];
Write(s);
s << Save Excel File(
	"$DOCUMENTS\temp.xlsx",
	Separate Rows for Each Cell Statistic( 1 )
);

So I'm trying to remove the prefix, and keep the remaining four "word blocks" plus their underscores, which I am adding back via string concatenation manually. See for string concatenation "||" reference.

Anyway, no file shows up at $DOCUMENTS\temp.xlsx.

Also the, um, what's it called, terminal, window, log, is giving me the error

Subscript Range in access or evaluation of 'c[2]' , c[/*###*/2]

and I don't know why. No strings or columns are output as a result of the Write() statements I have included.

So basically nothing is working as it should. Square one, I suppose!

Let me know what tips you all can think up.

Cheers,

Mike

Re: Script: Remove prefix of string and output modified column to Excel file

Thierry_S — Wed, 14 Jul 2021 19:13:05 GMT

Hi,

When dealing with strings, I tend to prefer to work with string specific functions (e.g. Substitute (), Munger (), Right (), Word());

Hence, in your case, I would suggest the following formula to remove the first part of the IDS string including the first instance of the "_" character:

Substitute( :IDS, Word( 1, :IDS, "_" ) || "_", "" )

Best,

Re: Script: Remove prefix of string and output modified column to Excel file

jthi — Wed, 14 Jul 2021 19:14:54 GMT

I think Save Excel file is a special case for Categorical Response Analysis Platform Additional Categorical Platform Options . To save an datatable as excel file you can just use << Save(path).

Currently you are trying to save just one string to a excel file, string created from the first row of datatable. Depending how and what you want to save, you have different options, below is one where the :ID column values are replaced with new values with the characters before first _ removed:

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("ID", Character, "Nominal", Set Values({"1_2_3_4_5_6", "2_3_4_5_6_7", "3_4_5_6_7_8"}))
);

Wait(1);

dt:ID << Set Each Value(
	Substr(:ID, Contains(:ID, "_")+1)
);

dt  << Save("$DOCUMENTS\test.xlsx");

You could also create new column with the new values which is most likely a better approach.

Re: Script: Remove prefix of string and output modified column to Excel file

mostarr — Fri, 16 Jul 2021 17:44:11 GMT

Thanks for the response, @Thierry_S ! I am currently trying your scriptlet out as well as some other things.

This may sound like a super simple thing for most, but I don't actually know what the best way to visualize output for debugging purposes when more than just a single-value variable is involved, i.e. how to pop out a single column into a new data table (using JSL only), or print the column to the log, or something like that.

I don't remember, maybe I've done this before and am just drawing a blank, but I feel kind of silly for asking.

Thanks again!

Mike

Re: Script: Remove prefix of string and output modified column to Excel file

mostarr — Fri, 16 Jul 2021 18:17:40 GMT

Also, in general, if you have a function which accepts a parameter or variable, will passing in a column perform the function element-wise to the column?

Mike

Re: Script: Remove prefix of string and output modified column to Excel file

Jeff_Perkinson — Fri, 16 Jul 2021 21:13:59 GMT

Also, in general, if you have a function which accepts a parameter or variable, will passing in a column perform the function element-wise to the column?

No. Usually if you need something evaluated for each row of a data table you will want to either create formula column in the data table or use the For Each Row() iterator.

JMP 16 introduced the For Each() function to iterate over JSL data structures: lists, associative arrays, or matrices.

Re: Script: Remove prefix of string and output modified column to Excel file

mostarr — Fri, 16 Jul 2021 22:55:23 GMT

This is a great framework; the problem I have identified now is quite simple:

The table I am working with is a subset of rows of the full table. And when I go Column(:ID), it doesn't find the column, i.e.

could not find column{3} in access or evaluation of 'Column' , Column/*###*/(dt:ID)

Which seems really simple but I don't know why. I thought it would be because I am using a subset of the rows, which I do need to do but I can find out if is the case by trying the script on the base table. One moment...

No it seems to be a problem on the full data table as well.

Any advice for "finding" my column?

Michael

Re: Script: Remove prefix of string and output modified column to Excel file

Craige_Hales — Sat, 17 Jul 2021 19:31:16 GMT

Add the data table pointer to the column function, make sure it is the table with the column.

Column(dt, :id)

it sounds like there might be multiple tables open, and JMP is trying to use the current table, which is not the one you want.

When you make the subset, you can get the table pointer for the new table:

dtSubset = dt<<subset(...);