JMP AND EXCEL MOVING DATA BETWEEN JMP AND

FILE > OPEN PREFERENCES FOR OPENING EXCEL FILES • File/Preferences Set up defaults that make sense for your most frequent application • “Excel Open Method” is new in JMP 11 • Options • Best Guess • Always – My Favorite • Never • Copyright © 2013, SAS Institute Inc. All rights reserved.

FILE > OPEN OPTIONS WHEN OPENING EXCEL FILES Option Triangle on Open Button Copyright

THE EXCEL ADD-IN UNINSTALL AND RESTORE In some very highly regulated systems a program that installs an Add-In in another program is trouble. Uninstall the Excel Add-In • 1. Open the Windows Add or Remove Programs utility (in the Control Panel). • 2. Select JMP Profiler Core and click Uninstall. • 3. Select JMP Profiler GUI and click Uninstall. Copyright © 2013, SAS Institute Inc. All rights reserved.

THE EXCEL ADD-IN UNINSTALL AND RESTORE Restoring the Excel Add-In • Re-run the installation files • Note: Excel Add-In will only work with one version, if multiple versions exits • Installing a new version, but don’t want it to take control of Excel Add-In? • • Uncheck Excel Add-In on installation C: Program FilesSASJMP11Excel. Add. In Copyright © 2013, SAS Institute Inc. All rights reserved.

EXCEL ADD-IN IMPORTANT FEATURES • Preferences Must set preferences first time • Use first row as col name • Number of Rows • • Concatenates multiple rows into one column name Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP 10 COMBINING MULTIPLE WORKSHEETS Open worksheets individually Concatenate all the open files Convert worksheets into files Open all the files Concatenate all the open files Convert worksheets into files Iterate: Open first, concatenate 2 nd, then 3 rd… Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL SPLITTING MANY TABS TO MANY FILES Paste this macro into the macro editor in Excel • • • Spoiler Alert: Sub Splitbook() Starting in JMP 11 My. Path = This. Workbook. Path Excel Wizard does this automatically For Each sht In This. Workbook. Sheets sht. Copy Active. Sheet. Cells. Paste. Special Paste: =xl. Paste. Values Active. Sheet. Cells. Paste. Special Paste: =xl. Paste. Formats Active. Workbook. Save. As _ Filename: =My. Path & "" & sht. Name & ". xls" Active. Workbook. Close savechanges: =False Next sht End Sub Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL LAUNCH DIALOG TO GET THE FILE LOCATION • • • //launch dialog to get the file location prefilepath = munger(Pick Directory( "Browse to directory with Files " ), 1, "/", ""); filepath = Convert File Path( prefilepath, Windows ); prefilelist = Files In Directory( filepath ); n 2=nitems(prefilelist); Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL FILTER OUT ANY NON-EXCEL FILES //filter out any non-Excel files For( i 2 = 1, n 2 >= i 2, i 2++, file=(prefilelist[i 2]); If( Item( 2, prefilelist[i 2], ". " ) == "xls" | Item( 2, prefilelist[i 2], ". " ) == "xlsx", Insert Into( filelist, file), show(file) ) ); Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL ITERATE THROUGH FILE LIST n=nitems(filelist); //number of items in the working list cctable= New Table( "Combined data table "); //make an empty table //Iterate through file list For( i = 1, i < n+1, i++, filenow = ( filelist[i] ); fileopen=(filepath||filenow); dt=open(fileopen); dt << New Column( "File Name", formula( filenow )); cctable << Concatenate( Data Table( dt ), Append to first table ); Close( dt, No. Save ); ); //end of for loop Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP 11 EXCEL IMPORT WIZARD IN JMP 11 Select Sheets to Concatenate Click Option

JMP AND EXCEL GETTING FIELDS OUT OF EXCEL TABLES • Case study: Excel tables used as a form • Need to combine a range of cells from many tables • Use Database Open to query an Excel table • With JSL we can extract a range of Excel cells from multiple tables • The script makes a new column with the workbook name • Workbook name is the key for joining the field tables • Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL GETTING FIELDS OUT OF EXCEL TABLES Creating a Relational Database after the fact • I have 500 excel forms and I need to copy and paste the same field from each one into one table • Can I use JMP to get several sets of values out of the forms? • >Are all the forms identical? ? Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL OPEN EXCEL AS A DATABASE • Open Database( • "DSN=Excel Files; DBQ=C: Byron WingerdExamplesExcel FilesQC FormsQC Assay Form 1. xlsx; • Default. Dir=C: Byron WingerdExamplesExcel FilesQC Forms; Driver. Id=1046; FIL=excel 12. 0; • Max. Buffer. Size=2048; Page. Timeout=5; ", "SELECT * FROM [f 9: g 16]" • ); • Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL SET UP FOR RECEIVING THE DATA • • //Expression for making the table //This "maketable" expression is activated by a button in the window maketable=expr( cctable= New Table( "Combined data table "||char(tablenumber) ); //make an empty table //Iterate through building the SQL string to query the Excel files For( i = 1, i < n+1, i++, filenow = ( filelist[i] ); Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL PARTS OF THE SQL STRING • • • //First build all the parts part 1="DSN=Excel Files; DBQ="||filepath||filenow||"; "; part 2="Default. Dir="||filepath||"; "; part 3="Driver. Id=1046; FIL=excel 12. 0; Max. Buffer. Size=2048; Page. Timeout=5; "; part 4="SELECT * FROM "||"["||excelrange||"]"; part 5=filenow; Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL BRUTE FORCE ASSEMBLY OF SQL TEXT STRING • • • //assemble the parts for the open data base argument (below) parta=(part 1||part 2||part 3); partb=part 4; partc=part 5; dt= Open Database(parta, partb, partc); Note: There are much more elegant methods of doing this (but this works) Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL CONCATENATE TABLES //add the contents of the query to the table created above • dt << New Column( "File Name", formula( filelist[i] ) ); • cctable << Concatenate( Data Table( dt ), Append to first table ); • Close( dt, No. Save ); • • ); //end of loop Copyright © 2013, SAS Institute Inc. All rights reserved.

JMP AND EXCEL PULL EACH RANGE INTO SEPARATE TABLES • Use dialog to set

JMP AND EXCEL WHAT’S COMING NEXT? JMP 12: Query Builder Skip the intermediate step of pushing data from your database to an Excel workbook • Connect to a database and join across multiple tables in one step • JMP creates SQL in the background to let the database do aggregation, filtering and sorting before the table is exported. • Copyright © 2013, SAS Institute Inc. All rights reserved.