topic How to set row in which data starts based on keyword in Discussions

How to set row in which data starts based on keyword

Agustin — Fri, 09 Jun 2023 17:00:14 GMT

Hi, I'm trying to adapt this script:

// Import Excel file: file.xls
// → Data Table( "Results" )
Open(
	"$DESKTOP/JMP test/file.xls",
	Worksheets( "Results" ),
	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( 51 ),
		Data Starts on Row( 52 ),
		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( "-" )
	)
);

however instead of setting these parameters

		Headers Start on Row( 51 ),
		Data Starts on Row( 52 ),

I would like to check for the keyword "Start" in column 1, the row where "Start" is would be the Headers and the one after would be where the data starts.

Is there an easy way to do this?
I am putting together a script that opens several spreadsheets that are similar and then stacks them, but the row at which the data starts varies from spreadsheet to spreadsheet as they are automatically produced by lab equipment.

Thank you

Re: How to set row in which data starts based on keyword

txnelson — Mon, 30 May 2022 15:51:10 GMT

There is not a "simple" way to do this, however, it is not overly complicated either.

Each table will have to be read twice
1. First, open the table indicating there is only one column, and it is delimited by the Line Feed character at the end of each line.
Loop through the data table until the word "Start" is found.
1. Save the Row number
Delete the current data table
Reopen the data table, reading it using your above parameters, with the Header Start and Data Start values set based upon the saved Row Number

Re: How to set row in which data starts based on keyword

Agustin — Mon, 30 May 2022 16:13:04 GMT

I'm only just starting using JSL and don't really know how to do that. Is there a documentation guide for JSL?

Re: How to set row in which data starts based on keyword

txnelson — Mon, 30 May 2022 20:02:05 GMT

The Scripting Guide, which documents JSL(Jmp Scripting Language) is available in the JMP Documentation Library, found under the Help pull down menu.