Subscribe Bookmark RSS Feed
Craige_Hales

Staff

Joined:

Mar 21, 2013

Column Formulas to CSV

JSL to find the formulas in a JMP data table and write a CSV (comma separated value) file.  There is an open issue for exporting formulas to Excel; maybe this script can serve as a starting point to determine what is really needed.

The code also demonstrates a simple use of Regex, user functions, and saving a file to the $TEMP directory.

This code has only been tested on the included data table.

7896_formulas.PNG

which opens this table

7895_dataTable.PNG

which the attached JSL exports to this CSV text

7897_CSVtext.PNG

If the .CSV file is opened in Excel, the formulas are character fields.

7870_excel.PNG

Comments
Wendy_Murphrey

Another option is to add a row to the table where any column formulas could be appended.

 

The following example loops through each column to check for a formula.  If a formula is found, the formula is added to the last row.  Finally, the table is stored as CSV or Excel. 

 

 

dt = Open( "$SAMPLE_DATA\Tiretread.jmp" );
dt << Run Formulas;
dt << Add Rows( 1 );
r = N Rows( dt );
For( i = 1, i <= N Cols( dt ), i++,
	f = Column( i ) << Get formula;
	If( !Is Empty( f ),
		Column( i ) << Delete Formula;
		Column( i ) << Data Type( "Character" );
		Column( i )[r] = Char( Name Expr( f ) );
	);
);

dt << Save( "$TEMP\Tiretread_Formulas.xlsx" );
dt << Save( "$TEMP\Tiretread_Formulas.csv" );
Close( dt, "No Save" );

 

 

Is this helpful for exporting JMP column formulas to Excel?

Craige_Hales

Much better approach, thanks!  You've used JMP's builtin functions to do a lot of the stuff I did the hard way.

Article Labels
Article Tags
  • Find more articles tagged with:
Contributors