678056_en-US

liliconcom

Multiple excel file import, extracting data from specific sheet

Dear expert JMP users, 

I have a lot of data files generated by an instrument that I've used - each one is consistent in format but they are all in individual sub-folders and each folder contains other files that are not of interest.

Is there a way of writing a script (I'm completely new to JMP scripting) to look in all sub-folders of a given directory, search for excel files ending in ' infos.xlsx', and extracting the data from the 13th row and columns 3 to 25 of a worksheet called "Datas", and appending the full filename of each file to each data row??

Please help, I've got 600 files to process!!

Best regards

Ian

Automation and ScriptingData Exploration and VisualizationQuality and Process EngineeringRe: Multiple excel file import, extracting data from specific sheet

use filesindirectory() with the recursive option; then process the list to identify the file of interest.

use the excel wizard to open a sample file; save the script from the source script in the opened table.

combine that with something like Concatenate data tables  (or any of the other similar scripts from pre-MFI days...)

 

JMP 17 does not have the excel support for anything but the simplest case in MultipleFileImport (yet).

 

 

Re: Multiple excel file import, extracting data from specific sheet

@JMPinthePool Just noticed @Feli  has a script close to what you need https://community.jmp.com/t5/JSL-Cookbook-Archived/Concatenate-data-tables/tac-p/328226/highlight/true#M103

 

Re: Multiple excel file import, extracting data from specific sheet

Thanks - that does look close...

I'm comfortable with setting the 'worksheet settings' as i need them, but that snippet on it's own doesn't seem to run - what do i need to do to make it run properly??

 

Many thanks

Re: Multiple excel file import, extracting data from specific sheet

It fits back together with the original code; @Feli  was just showing how to modify the open. You'll want to use the wizard to interactively open one file, then grab the similar looking open(...) and replace the original open that was for JMP tables with yours for excel files. You'll also need to make some tweaks for the recursive option; without recursive, FilesInDirectory() returns filenames that have to be prefixed with the file path. With recursive, it might just work, but the filenames now include a prefix of a partial pathname. the show() function will print to the log to help you figure it out.

show(filename, path||filename);

for example, might show where a missing or doubled \ or / gets into a path...