308227_en-US

liliconcom

Remove from Header Rows or Cells

I frequently need to import large data sets from Excel in JMP.  Often, text cells will have embedded 's that will cause havoc with the organization of the data, since JMP interprets the as an indication to go to the next Row.  This most frequently happens in the Header Row.

 

It would be great to have a "paste and ignore embedded returns", or "import and ignore embedded returns"

 

 

Re: Remove from Header Rows or Cells - Status changed to: Needs Info

Would be possible for you to share a mocked up Excel file with an example? We don't want anything proprietary but we do want to make sure we understand the kind of spreadsheet that you're dealing with. You can edit your item above and attach the example. 

 

Thanks!

Re: Remove from Header Rows or Cells

I added the file to the post above.   Thanks for looking into this.

Re: Remove from Header Rows or Cells - Status changed to: Open for Voting

Thanks for the file. 

Re: Remove from Header Rows or Cells

I've spent some time experimenting with the file. Thanks again for sharing it.

Excel spreadsheet to be imported into JMP

 

Result from copy/paste

 

This one will be particularly difficult for us to do anything with as what Excel puts on the clipboard, that we can consume, has the returns and there's no way to tell what's a return within the header (or other cell) and what's a return for the end of a row. I'll pass it along to our developers to look at though.

 

However, the JMP Add-in for Microsoft Excel does a good job with this data table.

JMP Add-in for Microsoft Excel

 

Or opening the spreadsheet directly in JMP, with or without the Excel Import Wizard, seemed to do a good job. 

Excel Wizard

 

Excel Import Wizard

 

Both methods (Add-in or import) resulted in this data table 

 

 

Re: Remove from Header Rows or Cells

When I have tried the Excel Import Wizard before, It appeared that the cells with embedded returns were truncated at the end of the first line.  

 

I see now from your screenshot that they were not truncated.  It's just that the row height setting was hiding the data on the second line.

 

Thank you for clarifying.  I have literally been fixing this manually in Excel forever, since I assumed the import was incomplete.