Choose Language Hide Translation Bar
emmablue
Community Trekker

How to delete a cell in jsl

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:

NameColumn1Column2Column3Column4Column5
Emma 38332083063031360
Mary 38482634469445753
Lisa3845961427554376643887
David3832799306433136531452

 

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

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User

Re: How to delete a cell in jsl

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) );

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: How to delete a cell in jsl

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

  1. Go to     Tables==>Stack and stack columns 1 - 5 into a new data table
  2. In the new table, right click on one of the missing cells, and select, "Select Matching Cells".
  3. Delete the rows that have the missing values
  4. 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)
  5. 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.
  6. 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
Jim
gzmorgan0
Super User

Re: How to delete a cell in jsl

@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

image.png 

image.png

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) );

 

gzmorgan0
Super User

Re: How to delete a cell in jsl

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) );

View solution in original post

emmablue
Community Trekker

Re: How to delete a cell in jsl

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. 

0 Kudos