Subscribe Bookmark RSS Feed

change in data format

sksivam

New Contributor

Joined:

Dec 27, 2016

Hello

When I copy paste ( or import) data from excel into JMP, I get a different format .

For example my data is .435, it get imported as 4.35e-1.

 

Is there a better way to import or copy paste to retain same format as .435?

 

thanks

Siva

1 ACCEPTED SOLUTION

Accepted Solutions
martindemel

Staff

Joined:

Jul 10, 2014

Solution

Hi Siva,

 

I guess you are using a non-English Display language of JMP. I'm working sometimes with a German version and got the values read in in the way you described it. In addition they will be character instead of numeric in the data table. If that's the case this script should help to transform the data to numeric and then you can change the display using the options in the column info panel.

Names Default To Here( 1 );
//dt = Open("Transform_Decimals.jmp");
dt = Current Data Table();

nr = nrows(dt);
col_names = dt << get column names (String);
for (i = 1, i <= nitems(col_names), i++,
    one_col = column(dt, col_names[i]);
    for (k = 1, k <= nr, k++,
        one_col[k] = substitute(one_col[k], ".", ",");    // Replace decimal with comma
    );
    one_col << Data Type(Numeric);
    one_col << Set Modeling Type ("Continuous");
);

 

If the data is read in as continuous, you may just need to set the format to what you like.

 

When you use the Excel Import Wizard you should be able to see the way JMP will handle the data after import. If the display differs from the resulting table please send this to our technical support. If it is the same, another thing to do is to check in Excel the format setting. By using a specific format (JMP knows) usually this can be avoided as well.

Hope that helps,

Martin

3 REPLIES
martindemel

Staff

Joined:

Jul 10, 2014

Solution

Hi Siva,

 

I guess you are using a non-English Display language of JMP. I'm working sometimes with a German version and got the values read in in the way you described it. In addition they will be character instead of numeric in the data table. If that's the case this script should help to transform the data to numeric and then you can change the display using the options in the column info panel.

Names Default To Here( 1 );
//dt = Open("Transform_Decimals.jmp");
dt = Current Data Table();

nr = nrows(dt);
col_names = dt << get column names (String);
for (i = 1, i <= nitems(col_names), i++,
    one_col = column(dt, col_names[i]);
    for (k = 1, k <= nr, k++,
        one_col[k] = substitute(one_col[k], ".", ",");    // Replace decimal with comma
    );
    one_col << Data Type(Numeric);
    one_col << Set Modeling Type ("Continuous");
);

 

If the data is read in as continuous, you may just need to set the format to what you like.

 

When you use the Excel Import Wizard you should be able to see the way JMP will handle the data after import. If the display differs from the resulting table please send this to our technical support. If it is the same, another thing to do is to check in Excel the format setting. By using a specific format (JMP knows) usually this can be avoided as well.

Hope that helps,

Martin

sksivam

New Contributor

Joined:

Dec 27, 2016

script works awesome to me.. 

Great, thanks a lot

 

Siva

martindemel

Staff

Joined:

Jul 10, 2014

Thanks, Happy it is useful :)