cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Leonard
Level III

Importing xlsx file. Help me turn the coma into a dot, please.

Good evening, dear colleagues!

I didn't find the same topic.

So, I imported xlsx into jmp, but my numeric values became nominal.

How to avoid this situation?

1 ACCEPTED SOLUTION

Accepted Solutions
SDF1
Super User

Re: Importing xlsx file. Help me turn the coma into a dot, please.

Hi @Leonard ,

 

  There's a few different ways to do that, but here's some JSL I wrote a while back that fixes that. I think JMP 16 is supposed to have a setting within the import wizard that allows overriding commas.

Names Default to Here(1);
//This replaces all commas by decimals
dt = Current Data Table();
Col_Names = dt << Get Column Names( Nominal, String );
Nr = N Rows( dt );
For( l = 1, l <= N Items( Col_Names ), l++,
	One_col = Column( dt, Col_Names[l] );
	For( k = 1, k <= Nr, k++,
		One_col[k] = Substitute( One_Col[k], ",", "." )
	);
);
//This part turns the "nominal" columns back to "numeric"
For( l = 1, l <= N Items( Col_Names ), l++,
	Column( dt, Col_Names[l] ) << Data Type( Numeric ) << Modeling Type( Continuous ) << Format( Best, 12 )
);

You can try going File > Preferences > Windows Specific and check the box at the top: Use JMP.... But, I actually haven't gotten much luck with that actually overriding the comma.

 

But, the JSL code should help.

 

Hope this helps!,

DS

 

View solution in original post

2 REPLIES 2
SDF1
Super User

Re: Importing xlsx file. Help me turn the coma into a dot, please.

Hi @Leonard ,

 

  There's a few different ways to do that, but here's some JSL I wrote a while back that fixes that. I think JMP 16 is supposed to have a setting within the import wizard that allows overriding commas.

Names Default to Here(1);
//This replaces all commas by decimals
dt = Current Data Table();
Col_Names = dt << Get Column Names( Nominal, String );
Nr = N Rows( dt );
For( l = 1, l <= N Items( Col_Names ), l++,
	One_col = Column( dt, Col_Names[l] );
	For( k = 1, k <= Nr, k++,
		One_col[k] = Substitute( One_Col[k], ",", "." )
	);
);
//This part turns the "nominal" columns back to "numeric"
For( l = 1, l <= N Items( Col_Names ), l++,
	Column( dt, Col_Names[l] ) << Data Type( Numeric ) << Modeling Type( Continuous ) << Format( Best, 12 )
);

You can try going File > Preferences > Windows Specific and check the box at the top: Use JMP.... But, I actually haven't gotten much luck with that actually overriding the comma.

 

But, the JSL code should help.

 

Hope this helps!,

DS

 

Leonard
Level III

Re: Importing xlsx file. Help me turn the coma into a dot, please.

"File > Preferences > Windows Specific and check the box at the top: Use JMP...."

@SDF1 I've unchecked this box Use JMP language and voila.