topic Re: How to delete a cell in jsl in Discussions

How to delete a cell in jsl

emmablue — Tue, 15 Jan 2019 19:22:27 GMT

I wanted to import a txt file into JMP, but the text is not perfectly aligned. So I want to delete the empty cells. I tried many different ways, but it seems that JMP can only delete an entire column/row. Is there a way to get from the Input to the Output format? Thank you.

Input:

Name	Column1	Column2	Column3	Column4	Column5
Emma		38	33208	30630	31360
Mary		38	48263	44694	45753
Lisa	38	45961	42755	43766	43887
David	38	32799	30643	31365	31452

Output:

Name	Column1	Column2	Column3	Column4	Column5
Emma	38	33208	30630	31360	30350
Mary	38	48263	44694	45753	47263
Lisa	38	45961	42755	43766	43887
David	38	32799	30643	31365	31452

Re: How to delete a cell in jsl

txnelson — Tue, 15 Jan 2019 19:47:46 GMT

This could be done in various ways using JSL, however, assuming you want to do this interactively, here is how I would do it

Go to Tables==>Stack and stack columns 1 - 5 into a new data table
In the new table, right click on one of the missing cells, and select, "Select Matching Cells".
Delete the rows that have the missing values
Create a new column, calling it "Column" and specify in the Column Info for the column to Initialize Data to "Sequence Data" with From value of 1 and To value of 5 (The number of columns that were stacked) and the Step value to 4 (the number of rows in the original data table)
Now go to Tables==>Split and select the column named "Column" as the Split By selection, and the column named "Data" as the Split Column.
The resulting new data table should allow you to either do a Tables==>Update or just a Cut and Paste to copy the new results back to the original data table

Re: How to delete a cell in jsl

gzmorgan0 — Wed, 16 Jan 2019 09:38:47 GMT

@emmablue, Jim provided a solution of how to fix the problem once the text file is imported. I will explain two options, each depends upon your original file.

#1 If your original file has spaces as the column delimiters, the fix could be easy. When you select File >Open use the option Data with Preview and make sure both Space and Spaces are selected

If you had selected Data, using Text Import Preferences, your default could be Space. If that is the case, the fix via the UI is use Data with Preview. If you are using a script, the Open statement in the script below should be used.

#2 If your original file has tabs as the column delimiters, the fix is not as easy. Via the UI you can use a text editor and use find an replace a tab with spaces. I found no method to specify Tabs in case there are extra tabs. Comment: a Tabs option might be a good wish list item, or allow the user to specify more than one character such as 0x090x09 or HexToChar("0909") for Other or add an option to Ignore repeated delimiters. If you cannot edit the file, or do not want to use the text editor to replace each tab with a space, and if you are using a Windows PC, you can modify the JSL file below by changing the file paths. If your Name column has spaces this will not work and a different script using regular expressions can be used to replace duplicate tabs with a single tab.

If you copy and pasted the data into the table, then you should use Jim's solution.

If you have more details about the input file, a more useful script/solution can be provided.

Names Default to Here(1);

findchar = "\!t"; //tab
infid    = "C:\temp\ExtraTab_testIn.txt"; //file path
outfid   = "c:\temp\_xxxdummy.txt";

//This was DOS script was found at StackOverflow 1/16/2019
//https://stackoverflow.com/questions/23075953/batch-script-to-find-and-replace-a-string-in-text-file-without-creating-an-extra/23076141
//It replaces the tabs in the infid with spaces and writes it to a outfid with spaces.
//If you want to replace the original file's tabs do the following
//    uncomment lines 29 and 30 below: "del %textfile"" and "rename %newfile %textfile"
//    change the Open( outfid, ..) to Open(infid, ...) in line 38
//To keep outfid, don't make the changes described above. But comment or delete the last JSL statement

dos_cmds = {
"cd c:\temp",
"@echo off &setlocal",
EvalInsert("set \!"search=%^findchar^\!""),
"set \!"replace=% \!"",
EvalInsert("set \!"textfile=^infid^\!""),
EvalInsert("set \!"newfile=^outfid^\!""),
"(for /f \!"delims=\!" %%i in (%textfile%) do (",
"   set \!"line=%%i\!"",
"    setlocal enabledelayedexpansion",
"    set \!"line=!line:%search%=%replace%!\!"",
"    echo(!line!",
"    endlocal",
"))>\!"%newfile%\!""
// , "del %textfile%",
// "rename %newfile%  %textfile%"
};
txt = concat items(EvalList(dos_cmds), "\!N");

f = Save Text File("c:\temp\Find_Replace.bat", txt, Mode("replace"));

Web("c:\temp\Find_Replace.bat"); //simple command to execute a .bat

dt = Open( outfid,
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Spaces, Space, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 0 ),
		Column Names Start( 1 ),
		Data Starts( 1 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

If( File Exists(outfid), Delete File(outfid) );

Re: How to delete a cell in jsl

gzmorgan0 — Wed, 16 Jan 2019 10:33:42 GMT

If your file is not extremely large and your version of JMP includes the character function Collapse Whitespace();

Here is a simpler solution using JSL.

Names Default to Here(1);

infid  = "c:\temp\ExtraTab_testIn.txt";
outfid = "c:\temp\_xxxdummy.txt";

fid = Load Text File(infid);
ww = words(fid, "\!N");  //each line is stored  as text in a list
for(i=1, i<=nitems(ww), i++,
	ww[i] = Collapse Whitespace(ww[i]);  //replace tabs spaces, or any nonprinatble char even EOL with a single space
);
fid = Concat Items(ww, "\!N");  //combine into a single text and add the EOL char
Save Text File(outfid,fid);     //save it

delete symbols(fid, ww);

//open it
dt = Open( outfid,
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Space, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 0 ),
		Column Names Start( 1 ),
		Data Starts( 1 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

If( File Exists(outfid), Delete File(outfid) );

Re: How to delete a cell in jsl

emmablue — Wed, 16 Jan 2019 15:59:38 GMT

Thank you! It worked great! This piece is crucial to the thing that I'm currently working on, and this is really a big help.