Transcript Options when Opening Excel files

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 Institute Inc. All rights reserved.
FILE > OPEN OPTIONS WHEN OPENING EXCEL FILES
Option Triangle on Open Button
Copyright © 2013, SAS Institute 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 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 Files\SAS\JMP\11\ExcelAddIn
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
2nd, then 3rd…
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
MyPath = ThisWorkbook.Path
Excel Wizard does
For Each sht In ThisWorkbook.Sheets
this automatically
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 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 );
n2=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( 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 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, NoSave );
);//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 to Concatenate
Copyright © 2013, SAS Institute 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 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 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 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
• 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 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=(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 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, NoSave );
•
);//end of loop
•
•
•
Copyright © 2013, SAS Institute Inc. All rights reserved.
JMP AND EXCEL PULL EACH RANGE INTO SEPARATE TABLES
•
Use dialog to set
range and make
tables
Copyright © 2013, SAS Institute Inc. All rights reserved.
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.