Specify Location of Column Headers and Data in Excel?
Apr 8, 2010 9:17 AM(1379 views)
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?
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.
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.