Specify Location of Column Headers and Data in Excel?

Question

I have a lot of data in Excel files.  I would like to open them into JMP directly, but the column headers are on line 3 and the data starts on line 4.  Using the best guess doesn't cut it.  Is there any way to do this via a script?

laural · Answer

This works great.  I just wanted to let people know that the Labelrow may depend whether the excel files import option for "Should Row 1 be Labels?" is set for Always or Best Guess versus Never.

Thanks, GH.

gh · Answer

I write a lot of scripts that have to use Excel data. I use the following workaround:

dt0 = Open( "data.xls");
// column labels in 4th row
labelrow = 4;
For( i = 1, i [less than]= N Col( Fdt0 ), i++,
	:Column( i ) << set name( :Column( i )[labelrow] )
);
Fdt0 << delete Rows( 1 :: labelrow );

(message gets cut off when I use < above - bit of a problem for including JSL :(  )

All of the columns are this way imported as character, which is actually good because I find often in excel apparently numeric columns will contain some characters (eg people love to add * or < to numbers), which would import as missing values if the column was recognised as numeric.

Cheers, Gunter

Message was edited by: GH

louv · Answer

I was able to do this by saving my excel file as a text delimited file and then using the open text Data with Preview to specify what row my column names were on and what row that my data begins. Alternatively I could use the preferences to set my Text Import preferences if all files had the same characteristics.