nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Import Excel table with columns that have bad data types

vince_faller

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?

Book1.xlsx

Accepted answers

briancorcoran

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

All comments

briancorcoran

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

mwechtal

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.

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.

vince_faller

Thanks Brian. I was afraid of that.

adam

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.

JWi

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.