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; } } }

How can I get only one sheet of excel file as JMP data table?

iamericha

Hi there, new to the site so forgive me if this has been answered already. Can I import an Excel workbook and only import the first sheet without knowing the name of the sheet?

I am trying to loop through multiple excel files and just import the first sheet of each file.

Accepted answers

David_Burnham

You can discover the worksheet names (the 'tab names') using the function Get Excel Worksheets. This will return a list - so the first item in the list will be the first worksheet that appears in the file.

All comments

uday_guntupalli

@iamericha,
I might not have a direct solution for what you want - however, I think there is a roundabout - not efficient solution I can definitely offer.

If you know the tab name ahead, you could do something like this .

Open(
	FilePath,
	Worksheets(TabName ),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 2 ),
		Data Starts on Row( 3 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		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( "-" )
	)
)

If you don't , then you can open all the tables amd close the ones that you don't need.

openDTs = List(); // Initiate empty list 
For( i = 1, i <= N Table(), i++,
       Insert Into( openDTs, Data Table( i ) );
       If(i != 1,
            Close(Data Table(i),"No Save"); 
        ); 
     );

David_Burnham

iamericha

Thank you David, I was hoping for something like this

iamericha

Thank you Uday, this works too.

Peter_Bartell

Or unless I'm missing something use the Excel Import Wizard in JMP and you can see the list of worksheets in the Worksheet List window in the upper right corner of the Wizard window. The list is in the order of worksheets in the workbook. From there just pick the first sheet. One advantage of using the Wizard is the flexible import settings in case your worksheet isn't in a form which is 'ready to go' in JMP. For example, maybe you have multiple rows in the worksheet that you'd like combine into JMP column names. Easy to combine within the context of the Wizard. Here's the link to the JMP online documentation associated with the JMP Excel Import Wizard:

http://www.jmp.com/support/help/13-2/Import_Microsoft_Excel_Files.shtml#

iamericha

Thanks Peter, I wanted to loop through several different workbooks where the name of the first sheet may be different in each workbook so I needed a solution to grab just the first sheet from each file. Once I do that, I can slice and dice out the data I need from each sheet.

Thanks!