Force Excel Import Wizard to Use Character for All Columns?I am trying to import several one sheet from several auto-generated Excel tables and then concatenate into a larger file. The problem is that the first row contains the 'blank' data which has no batch/lot/etc. info, and the Excel cells have 'general' as the format. The next three columns are empty for the 'blank' but full for all other rows (samples). The first (batch) always imports correctly as text, and the third (fill volume) imports correctly as continuous ... but the middle row which contains either a lot # (which starts with a character) or experimental sample ID always comes in as numeric which empties the text info. I know in the Wizard you can change column format ... but since it starts as numeric it has already killed the content so I just end up with blank cells. So if I could just bring in all of the files as completely text and then post-process that would be acceptable as it would retain the data.
I saw this, but it doesn't solve my problem - https://community.jmp.com/t5/Discussions/Force-Import-Format-of-Excel-columns/td-p/342394
I have attached a sample file - it is possible that this is just something not currently possible. Just wanted to check.
Data TableRe: Force Excel Import Wizard to Use Character for All Columns?Hello,
Have you tried going into Excel and changing the entire column (Lot?) to Text instead of General? This might allow you to always bring it in as Character.
JMP 17 adds the ability to change the column type to Character in the Excel Wizard. If you are on the Early Adopter program, you can try it out there.
I hope this helps,
Brian Corcoran
JMP Development
Re: Force Excel Import Wizard to Use Character for All Columns?Thanks Brian:
Unfortunately these files are autogenerated after a chemical analysis, dozens per day so manual adjustment wouldn't work. Obviously I could do something in an Excel VB macro to make the change - but I was trying to avoid dual scripts if possible.
And agree on JMP 17EA, but it allows the change to be made but doesn't 're-import' but just adjusts the type for the table it has already imported - in other words, since it pre-decided it was numeric it eliminated the data in all of those cells. Sounds like I need to take a trip to the 'wish list' 
