topic Re: Force Excel Import Wizard to Use Character for All Columns? in Discussions

Force Excel Import Wizard to Use Character for All Columns?

andersonmj2 — Sat, 10 Jun 2023 23:44:09 GMT

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.

Re: Force Excel Import Wizard to Use Character for All Columns?

briancorcoran — Mon, 14 Feb 2022 18:17:35 GMT

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?

andersonmj2 — Mon, 14 Feb 2022 18:32:22 GMT

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' :)

Re: Force Excel Import Wizard to Use Character for All Columns?

briancorcoran — Tue, 15 Feb 2022 13:43:55 GMT

Hi,

JMP 17 doesn't change the column type after the import operation. If you run the Source script that is generated from a table where the column has been manually adjusted once, you should see it come in as Character again. Here is an example of a script where the General column has been changed to Character.

Open(
	"$DESKTOP/Test Decimals Horizontal.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 1317 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" ),
		Column Numeric Format(
			Index( 1 ),
			Column Name( General ),
			Datatype( "Character" )
		),
		Column Numeric Format(
			Index( 2 ),
			Column Name( Number 0 ),
			Format( "Best", 9 )
		)
	)
)

Re: Force Excel Import Wizard to Use Character for All Columns?

andersonmj2 — Tue, 15 Feb 2022 15:30:38 GMT

Thanks again - appreciate the reminder to look at the table source script!

Interesting finding - when I run the generated script it changes the 3rd column to character but it is still empty. If I go to my attached Excel file and type the letter 'A' into the empty cell in that 3rd column and run the script it imports everything correctly (column type and contents), but when I delete that 'A' and save ... re-running the script once again results in an empty column.

Again appreciate the help and gives me stuff to play with - and to see if they can tweak the table auto-generation system to make sure those top rows are not empty!