cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
steven_gong
Level II

Cols utilities: how to let take care of empty data?

Hi

I got a problem when using Cols->utilities->text to columns.

For example I have data 1, ,2,3, after doing text to columns, I want to get {1},{ },{2},{3} these four columns, while JMP gives me {1},{2},{3} as below screen shot.

Excel can do this with <Text to Columns>

Is there an solution out there in JMP?

 

The result I got from JMP:

steven_gong_1-1585200437035.png

what I expected:

steven_gong_2-1585200470456.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Cols utilities: how to let take care of empty data?

I am not aware of doing what you want with the Text to Columns() function, but here is a simple function that will do what you want

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "string",
		Character,
		"Nominal",
		Set Values( {"1,2,3", "1,2,3,4", "1,,3,4"} ),
		Set Display Width( 44 )
	)
);

newTexttoColumns = Function({dt,x},{defaultlocal},
start = N Cols( dt );
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
	theWord = "";
	wordCnt = 1;
	For( i = 1, i <= Length( Column( x )[theRow] ), i++,
	
		If( Substr( Column( x )[theRow], i, 1 ) == "," | i == Length( Column( x )[theRow] ),
			If(i == Length( Column( x )[theRow]),theWord = theWord || Substr( Column( x )[theRow], i, 1 ));
			If( N Col( dt ) < start + wordCnt,
				dt << New Column( "", character )
			);
			Column( start + wordCnt )[theRow] = theWord;
			wordCnt++;
			theWord = "";
		,
			theWord = theWord || Substr( Column( x )[theRow], i, 1 )
		)
	
	);
);
0;
);

return = newTexttoColumns(dt,"string");
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Cols utilities: how to let take care of empty data?

I am not aware of doing what you want with the Text to Columns() function, but here is a simple function that will do what you want

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "string",
		Character,
		"Nominal",
		Set Values( {"1,2,3", "1,2,3,4", "1,,3,4"} ),
		Set Display Width( 44 )
	)
);

newTexttoColumns = Function({dt,x},{defaultlocal},
start = N Cols( dt );
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
	theWord = "";
	wordCnt = 1;
	For( i = 1, i <= Length( Column( x )[theRow] ), i++,
	
		If( Substr( Column( x )[theRow], i, 1 ) == "," | i == Length( Column( x )[theRow] ),
			If(i == Length( Column( x )[theRow]),theWord = theWord || Substr( Column( x )[theRow], i, 1 ));
			If( N Col( dt ) < start + wordCnt,
				dt << New Column( "", character )
			);
			Column( start + wordCnt )[theRow] = theWord;
			wordCnt++;
			theWord = "";
		,
			theWord = theWord || Substr( Column( x )[theRow], i, 1 )
		)
	
	);
);
0;
);

return = newTexttoColumns(dt,"string");
Jim
steven_gong
Level II

Re: Cols utilities: how to let take care of empty data?

Hi Jim,

What a speed to deliver such a cool script!

Thanks a lot.

 

BTW, I think JMP may need to consider to enhance the function of <text to columns>.

txnelson
Super User

Re: Cols utilities: how to let take care of empty data?

If you think JMP should have the enhancement, please add it to the JMP Wish List

Jim
hogi
Level XI

Re: Cols utilities: how to let take care of empty data?

I recently had the same issue and wondered if a checkbox can be added to the tool:
Then the user can decide on his own if he wants to enable/disable the feature to treat multiple separator character as a single one:

 

Text To Columns: handle missing data correctly