Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level I

## 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:

 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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";

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 ),
Year Rule( "20xx" )
)
);

If( File Exists(outfid), Delete File(outfid) );``````
4 REPLIES 4
Highlighted
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
Highlighted
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

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 ),
Year Rule( "20xx" )
)
);

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

Highlighted
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";

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 ),
Year Rule( "20xx" )
)
);

If( File Exists(outfid), Delete File(outfid) );``````
Highlighted
Level I

## 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.

Article Labels

There are no labels assigned to this post.