Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
vince_faller
Super User

Import Excel table with columns that have bad data types

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?

Vince Faller - Predictum
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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

View solution in original post

6 REPLIES 6
Highlighted

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

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

View solution in original post

Highlighted
vince_faller
Super User

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

Thanks Brian.  I was afraid of that.  

Vince Faller - Predictum
Highlighted
mwechtal
Level III

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

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.

Highlighted
ih
ih
Level VII

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

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
Level IV

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

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.

Highlighted
JWi
JWi
Level II

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

Thanks for bringing this up. I have a similar problem here. I respect, that the origin of the problem is a bad excel formatting. However, this mistake is present in hundreds of excel files on our server, which I would like to analyze using the power of JMP. Honestly, I don't want to go manually through all these files. But with the current state, the data in all these files in the malformatted columns is simply lost to further analysis.

 

I would be very happy to have an option in the JMP Excel import wizard, which allows for either ignoring any Excel formatting (recall also ih's comment above in this context) or to specify the data type for columns.

Article Labels

    There are no labels assigned to this post.