topic Re: Import with decimal separator comma in JMP 16 - Custom Locale Settings not working in Discussions

Import with decimal separator comma in JMP 16 - Custom Locale Settings not working

Feli — Fri, 09 Jun 2023 19:55:02 GMT

I have to import some text files where comma (,) is used as decimal separator. I don't want to change my settings from English/Use JMP Settings since otherwise my other imports and scripts stop working, so I thought I'd try the new feature in JMP 16.1 (JSL command to Get and Set the Display Language in JMP ) that @Jeff_Perkinson mentioned.

So off I go building my little script :

Get Preferences( Custom Locale Settings );
    Preferences( Custom Locale Settings( Decimal Separator(",") ) );
Open(
	"myfile",
	columns(
		...
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Other( "0x3b" ), 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" )
	)
);

But nothing happens- the comma doesn't get recognized as decimal separator and the numeric columns are imported as a character column, which is exactly not what I wanted.

So any help? Is there something wrong in my import settings (I didn't change any standard preferences except that I use semicolon as end of field separator) or did I find a bug?

My JMP version is 16.1 and I use the Windows version, if that helps.

Re: Import with decimal separator comma in JMP 16 - Custom Locale Settings not working

jthi — Fri, 20 Aug 2021 14:34:45 GMT

This could be a bug (you could contact support@jmp.com), but could you script your way out of this temporarily? You could for example replace the "," with "." in specific columns and then attempt to convert the columns to Numeric.

Re: Import with decimal separator comma in JMP 16 - Custom Locale Settings not working

Lu — Wed, 25 Aug 2021 18:31:09 GMT

Hello Feli

I think the fasted way to solve your problem is to use the recode function. Go to the column heading, right click and activate "recode", Then go to the red triangle "convert to Character" again go to the red triangle "replace string" and change the string into what you want. Afterwards you can change the column charactersitic back numerical.

Hope this is of help?

regards

Re: Import with decimal separator comma in JMP 16 - Custom Locale Settings not working

Feli — Thu, 14 Oct 2021 08:48:14 GMT

Seems that I found a bug - yes, recode or scripting can resolve it, but I wanted to try it in a clever way...

Re: Import with decimal separator comma in JMP 16 - Custom Locale Settings not working

Feli — Thu, 21 Oct 2021 06:57:14 GMT

After talking with JMP support, we found this solution:

Preferences( Custom Locale Settings( Decimal Separator(",") ) );
Open(
    "/C:/Temp/test_import.csv",
    columns(
        New Column("Number", Numeric, "Continuous", Format("Best", 12)),
        New Column("Number2", Numeric, "Continuous")
    ),
    Import Settings(
        End Of Line(CRLF, CR, LF),
        End Of Field(Other(";"), CSV(0)),
        Strip Quotes(1),
        Use Apostrophe as Quotation Mark(0),
        Use Regional Settings(1),
        Scan Whole File(1),
        Treat empty columns as numeric(0),
        CompressNumericColumns(0),
        CompressCharacterColumns(0),
        CompressAllowListCheck(0),
        Labels(1),
        Column Names Start(1),
        Data Starts(2),
        Lines To Read("All"),
        Year Rule("20xx")
    )
)

The key is to set Use Regional Settings(1) and set the columns to numeric during import.