How to easily import/assign units property to multiple columns?

[#1]

I work in the petrochemical industry and like others in the process industries, I use JMP to analyze continuous/batch process measurements time-series data. Column 1 is a timestamp, columns 2 though n are process measurements like; flow; pressure; temperature; rpm; mass, etc.. The data are queried from a process historian database into Excel. The first row contains the measurement name and the second row contains the measurement units: kg/hr; deg C; kPa; psig; kbbl/hr; etc.. Is there a way have the units information assigned to the Units column property? Would be a great feature in the Excel import wizard.

[#2 · Accepted solution]

In JMP 11 the excellent Excel import wizard lets you specify how many rows are to be considered headers and where they are, and then lets you specify where the data is. Doing this interactively or using the script that gets embedded in the resulting JMP file, you can take an Excel sheet that looks like this:

Length    Width    Weight

ft               in           lb

1    10    100

2    20    200

3    30    300

4    40    400

5    50    500

6    60    600

and end up with a JMP table that looks like this:

Length-ft	Width-in	Weight-lb
1	10	100
2	20	200
3	30	300
4	40	400
5	50	500
6	60	600

If that is adequate then you are done. If not then you can run a script something like the following:

dt = Current Data Table();

For( colnum = 1, colnum <= 3, colnum++,

    col = Column Name( colnum );

    charcol = Char( Column Name( colnum ) );

    colname = Word( 1, charcol, "-" );

    colunit = Word( 2, charcol, "-" );

    newcolname = col << Set Name( colname );

    newcolname << Set Property( "Units", Eval( colunit ) );

);

to end up with a JMP table that looks like this:

Length    Width    Weight

(ft)          (in)          (lb)

1    10    100

2    20    200

3    30    300

4    40    400

5    50    500

6    60    600

where the text between parentheses are entered into the Units property of each column.

[#3]

mpb:

Thanks. That works. I'm new to scripting and still shaky when it comes to using eval.

I also posed this question to my regional JMP support person Jerry Cooper. His solution:

Names Default To Here( 1 );
dt = Current Data Table();

mycols = dt << Get Column Names( string );

For( i = 1, i <= N Items( mycols ), i++,
If( Contains( mycols, "-" ),
  myunits = Substr( mycols, Contains( mycols, "-", -1 ) + 1 );
  newname = Substr( mycols, 1, Contains( mycols, "-", -1 ) - 1 );
  Column( i ) << Set Name( newname );
  Column( i ) << Set Property( "Units", eval(myunits) );
)
);

He contacted the developers to add this feature to the Excel Import Wizard. Another situation requiring something similar is specification limits. How to import them with the data and assign them to the column properties.