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

Multiple excel file import, extracting data from specific sheet

JMPinthePool — Fri, 15 Sep 2023 12:14:14 GMT

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

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

Craige_Hales — Fri, 15 Sep 2023 13:44:58 GMT

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

Craige_Hales — Fri, 15 Sep 2023 13:55:35 GMT

@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

JMPinthePool — Fri, 15 Sep 2023 14:10:27 GMT

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

Craige_Hales — Fri, 15 Sep 2023 14:46:42 GMT

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