48132_en-US

liliconcom

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?

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.

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

Thanks Brian.  I was afraid of that.  

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.

 

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.

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

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.