JMP AND EXCEL MOVING DATA BETWEEN JMP AND

JMP AND EXCEL MOVING DATA BETWEEN JMP AND

JMP AND EXCEL MOVING DATA BETWEEN JMP AND EXCEL Copyright 2013, SAS Institute Inc. All rights reserved. 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 JMP11 Options Best Guess Always My Favorite

Never Copyright 2013, SAS I nstitute Inc. All rights reserved. FILE > OPEN OPTIONS WHEN OPENING EXCEL FILES Option Triangle on Open Button Copyright 2013, SAS I nstitute Inc. All rights reserved. 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 I nstitute 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 dont want it to take control of Excel Add-In? Uncheck Excel Add-In on installation

C:\Program Files\SAS\JMP\11\ExcelAddIn Copyright 2013, SAS I nstitute 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 I nstitute 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 2nd, then 3rd Copyright 2013, SAS I nstitute Inc. All rights reserved. JMP AND EXCEL SPLITTING MANY TABS TO MANY FILES Paste this macro into the macro editor in Excel

Spoiler Alert: Starting in JMP 11 Excel Wizard does this automatically Sub Splitbook()

MyPath = ThisWorkbook.Path For Each sht In ThisWorkbook.Sheets sht.Copy ActiveSheet.Cells.Copy ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats ActiveWorkbook.SaveAs _ Filename:=MyPath & "\" & sht.Name & ".xls" ActiveWorkbook.Close savechanges:=False Next sht End Sub Copyright 2013, SAS I nstitute 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 ); n2=nitems(prefilelist); Copyright 2013, SAS I nstitute Inc. All rights reserved. JMP AND EXCEL FILTER OUT ANY NON-EXCEL FILES //filter out any non-Excel files For( i2 = 1, n2 >= i2, i2++, file=(prefilelist[i2]); If( Item( 2, prefilelist[i2], "." ) == "xls" | Item(

2, prefilelist[i2], "." ) == "xlsx", Insert Into( filelist,file), show(file) ) ); Copyright 2013, SAS I nstitute 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, NoSave ); );//end of for loop Copyright 2013, SAS I nstitute Inc. All rights reserved. JMP 11 EXCEL IMPORT WIZARD IN JMP 11 Select Sheets to Concatenate Click Option to Concatenate Copyright 2013, SAS I nstitute Inc. All rights reserved. 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 I nstitute 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 I nstitute Inc. All rights reserved. JMP AND EXCEL OPEN EXCEL AS A DATABASE

Open Database( "DSN=Excel Files;DBQ=C:\Byron Wingerd\Examples\Excel Files\QC Forms\QC Assay Form 1.xlsx; DefaultDir=C:\Byron Wingerd\Examples\Excel Files\QC Forms\;DriverId=1046;FIL=excel 12.0; MaxBufferSize=2048;PageTimeout=5;",

"SELECT * FROM [f9:g16]" ); Copyright 2013, SAS I nstitute 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 I nstitute Inc. All rights reserved. JMP AND EXCEL PARTS OF THE SQL STRING

//First build all the parts part1="DSN=Excel Files;DBQ="||filepath|| filenow||";"; part2="DefaultDir="||filepath||";"; part3="DriverId=1046; FIL=excel 12.0; MaxBufferSize=2048; PageTimeout=5;"; part4="SELECT * FROM "||"["|| excelrange||"]"; part5=filenow; Copyright 2013, SAS I nstitute 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=(part1||part2||part3); partb=part4; partc=part5; dt= Open Database(parta, partb, partc); Note: There are much more elegant methods of doing this (but this works) Copyright 2013, SAS I nstitute 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, NoSave ); );//end of loop Copyright 2013, SAS I nstitute Inc. All rights reserved. JMP AND EXCEL PULL EACH RANGE INTO SEPARATE TABLES

Use dialog to set range and make tables Copyright 2013, SAS I nstitute Inc. All rights reserved. JMP AND EXCEL WHATS 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 I nstitute Inc. All rights reserved.

Recently Viewed Presentations

  • The World We Live In

    The World We Live In

    Our Universe (which means the space, time, matter and energy) was born in a Big Bang 13.7 billion (13,700,000,000) years ago from a singularity, a point in space-time in which gravitational forces cause matter to have an infinite density and...
  • Chapter 4B Circuit Analysis Techniques B) Mesh Current

    Chapter 4B Circuit Analysis Techniques B) Mesh Current

    Chapter 4B Circuit Analysis Techniques B) Mesh Current Node Voltage and Mesh Current methods derive a minimal set of equations solve all circuit variables Node Voltage uses KCL Mesh Current uses KVL NV more powerful than MC MC only solves...
  • Online resources for traditional classrooms - student use and ...

    Online resources for traditional classrooms - student use and ...

    Images from Creative commons. Martin Dougiamas is the creator of Moodle. The first release happened in 2002. Tom Worthington. A computer programmer who led a movement for increasing web-accessibility for physically impaired people.
  • Biodiversity - Ms. Pici&#x27;s Science

    Biodiversity - Ms. Pici's Science

    Critical biodiversity. Parts of the world have a larger amount of biodiversity than others. These areas have large numbers of endemic species, or species that are native to and only found within a specific area. Ex). Trigger fish or the...
  • Chemical Pathways - ryanhs.org

    Chemical Pathways - ryanhs.org

    Chemical Pathways When you are hungry, how do you feel? ... the word respiration is often used as a synonym for breathing This is why we have used the term cellular respiration to refer to energy-releasing pathways within the cell...
  • Human Geography Models

    Human Geography Models

    Gravity Model. Migration. Large places attract more people. Closer places are of higher attraction. Therefore people will migrate to the next largest city. "migration between two places is . directly. proportionate to . population. and . inversely. proportionate to .
  • Science Starter: Have your workbook out and complete the ...

    Science Starter: Have your workbook out and complete the ...

    Food Web Scenario Activity . Construct food web on construction paper. Turn in today. 5. Complete food webs in workbook. 6. Launch. Vocabulary. Primary Consumer: Consumers that feed upon producers ; Secondary Consumer. An organism that feeds upon primary consumers.
  • The Evolution of Fingerprinting Amanda Hurtado, Katherine McGowan,

    The Evolution of Fingerprinting Amanda Hurtado, Katherine McGowan,

    Kept the first fingerprint files based on Galton's details. Made the first criminal fingerprint identification. Sir Edward Richard Henry (1896) Inspector General of Police for the Lower Provinces in Bengal.