JMP and Excel Moving Data Between JMP and Excel - PowerPoint Presentation

Slide1

JMP and Excel

Moving Data Between JMP and ExcelSlide2

Statistical Discovery

Agenda

The JMP Add-In for Excel

Using and restoringOpening Excel tablesJMP 10 and 11Open xls(x) as a databaseScripting File ManipulationJMP Import Wizard OptionsQuick look at what’s next for JMPHot Topics?

Flexible Dialogue about AnalyticsSlide3

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 JMP11OptionsBest GuessAlways – My FavoriteNeverSlide4

File > Open

Options when Opening Excel files

Option Triangle on Open ButtonSlide5

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-In1. 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

.Slide6

The Excel Add-In

Uninstall and restore

Restoring

the Excel Add-In

Re-run the installation filesNote: Excel Add-In will only work with one version, if multiple versions exitsInstalling a new version, but don’t want it to take control of Excel Add-In?Uncheck Excel Add-In on installationC:\Program Files\SAS\JMP\11\ExcelAddInSlide7

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 nameSlide8

JMP 10

Combining Multiple WorksheetsSlide9

JMP and Excel

Splitting many tabs to many files

Paste this macro into the macro editor in Excel

Sub Splitbook()

MyPath = ThisWorkbook.PathFor Each sht In ThisWorkbook.Sheetssht.CopyActiveSheet.Cells.CopyActiveSheet.Cells.PasteSpecial Paste:=xlPasteValuesActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

ActiveWorkbook.SaveAs

_

Filename:=MyPath & "\" &

sht.Name

& ".xls"

ActiveWorkbook.Close

savechanges

:=FalseNext shtEnd Sub

Spoiler Alert:Starting in JMP 11

Excel Wizard does this automaticallySlide10

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);Slide11

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)

)

)

;Slide12

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 listFor( 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 loopSlide13

JMP 11

Excel Import Wizard in JMP 11

Select Sheets to Concatenate

Click Option to ConcatenateSlide14

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 tablesUse Database Open to query an Excel tableWith JSL we can extract a range of Excel cells from multiple tablesThe script makes a new column with the workbook nameWorkbook name is the key for joining the field tablesSlide15

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??Slide16

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]"

)

;Slide17

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 filesFor

( i = 1, i < n

+

1, i++,filenow = (

filelist[i] );Slide18

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;Slide19

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)Slide20

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 loopSlide21

JMP and Excel

Pull each Range into separate Tables

Use dialog to set range and make tablesSlide22

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 stepJMP creates SQL in the background to let the database do aggregation, filtering and sorting before the table is exported.