In my previous blog post JMP and Excel - A Never ending story? - Part 1 I ended with an example importing spreadsheets with hierarchical headers and merged cells. This time I focus on importing a spreadsheet file with many worksheets as well reading multiple spreadsheets (laying in a specific folder) into JMP. I do not want to make it overcomplicated, therefore I assume the structure and parameter names in the spreadsheets as well in the different worksheets are the same!
(Note: If not the same you would have to specify the settings for each workbook and import them as separate tables. Combining them afterwards could be done by using the join/concatenate features or the JMP Query Builder for JMP data tables. If you want to automate these kind of steps usually further scripting is required to check for the role for every variable, worksheet and/or file you want to read in).
MULTIPLE WORKSHEETS - IMPORT AND AUTOMATE
So let’s look at the data in Excel first:
One can see the different workbooks, all having the same shape and non-trivial structure. Underlined in red one can see the different batch numbers on each workbook. I want to read in the data but not in separate tables. Rather I would like to have one single table and using JMP’s Excel Import Wizard this is quite easy to achieve:
If you want to see this in action I can recommend this short video (using JMP 12). A note in case you just started using the Import Wizard: It will save the last setting, which may lead to surprising views on the data, when importin new, but different data. Using the button “Restore Default Settings” you can reset your settings in the wizard.
In each imported data table you’ll find a source script to redo the import with the settings you previously have chosen. This allows for reading an updated data table, in case you got new batch data in the spreadsheet. Well, when collecting data frequently I probably would consider using databases rather than spreadsheets, but that’s a different story.
AUTOMATE IMPORTING MULTIPLE SPREADSHEETS - DIFFERENT CHALLENGE
Now you know how to read one spreadsheet with multiple worksheets with the same structure into JMP. However, you may have also faced the challenge “How do I read multiple spreadsheets with the same structure into JMP without manually do this for each file, aka doing it automatically. The short answer, using JMP Scripting Language. Using the Source Script as base you can either rerun this script for each spreadsheet, or you use a for loop looping through all files in a folder. There is a script shared in the community File Exchange you could use (maybe with some adjustments), which exactly does this: Combine-multiple-txt-or-csv-files
In short you do the following steps (just parts of the script shown):
prefilepath = munger(Pick Directory( "Browse to the Directory of the Target Excel Files " ),1,"/",""); filepath = Convert File Path( prefilepath, Windows ); prefilelist = Files In Directory( filepath ); n2=nitems(prefilelist); //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) ) );
n=nitems(filelist); //number of items in the working list cctable= New Table( "Combined data table");//make an empty table ExcelSheetName = "Sheet1"; // change this if you have a special sheet name you want to read the data from //Iterate through building the SQL string to query the Excel files For( i = 1, i < n+1, i++, filenow = ( filelist[i] ); fileopen=(filepath||filenow); dt=open(fileopen, Worksheets( ExcelSheetName ) ); dt << New Column( "File Name", formula( filenow ) ); cctable << Concatenate( Data Table( dt ), Append to first table ); Close( dt, NoSave ); );//end of for loop
So far you have read about several ways to import trivial structured data, as well how to handle hierarchical and/or merged data sets using the Excel Import Wizard in JMP. Finally I showed you ways to import multiple worksheets at once and how to automatically read multiple Excel sheets with a JSL Script. In my next post I will talk about potential challenges based on the format of the data and others.
I’m looking forward to your comments and shared experiences.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.