Subscribe Bookmark RSS Feed

Re: Import Excel table with columns that have bad data types

vince_faller

Super User

Joined:

Mar 17, 2015

I've got a weird issue that I can't get around.  

I'm trying to import a table that has the column in Scientific Notation but has characters in it (see attached table).  Because of this, I can't actually import the data in correctly (JMP just assumes it's numeric and blanks everything out).  Is there a way in JMP to make it ignore the excel settings and just import it as is?  Or convert them to csv or something?

1 ACCEPTED SOLUTION

Accepted Solutions
briancorcoran

Joined:

Jun 23, 2011

Solution

When there is a specific (not "General") format applied to a column, JMP tries hard to respect this above the cell contents.  So, short of retyping the column in Excel there is no good solution.

 

Brian Corcoran

JMP Development

5 REPLIES
briancorcoran

Joined:

Jun 23, 2011

Solution

When there is a specific (not "General") format applied to a column, JMP tries hard to respect this above the cell contents.  So, short of retyping the column in Excel there is no good solution.

 

Brian Corcoran

JMP Development

vince_faller

Super User

Joined:

Mar 17, 2015

Thanks Brian.  I was afraid of that.  

mwechtal

Community Trekker

Joined:

Jan 19, 2012

You should be able to select the column in Excel, right click on it, select format, then select character format. Save the file, and try importing it again. I suspect that it will work.

ih

Community Trekker

Joined:

Sep 30, 2016

If your data is small enough, the add-in for excel imports the values in your table as characters.

 

To be honest though I often use vba macros to output csv files which I then read in JMP.  There are a lot of situations that the excel import tool just doesn't crack.

 

abcd.gif

Highlighted
adam

Contributor

Joined:

Sep 20, 2017

I encountered similar problem before. The columns(contain chars and numbers) in Excel is in General format. I can choose to manually copy them to JMP or convert the xlsx file to csv. Both way can work.