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

markschahl

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.

mpb

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.