How to shorten the script of opening 30 Excel files?

[#1]

Hi folks,

I need to read data from 30 Excel files, and my current script is shown below, 383mm has 10 files, 533mm has 10 files and 683mm has 10 files. Is there any way to have a "for loop" type of script that can read all Excel files and assign all data to one stacked data table? Copy and paste 30 times to open 30 files seems awkward. Thanks for your help. 

dt01 = Open( "E:_383mm_001/FRF.csv" );
dt02 = Open( "E:_383mm_002/FRF.csv" );
dt03 = Open( "E:_383mm_003/FRF.csv" );
dt04 = Open( "E:_383mm_004/FRF.csv" );
dt05 = Open( "E:_383mm_005/FRF.csv" );
dt06 = Open( "E:_383mm_006/FRF.csv" );
dt07 = Open( "E:_383mm_007/FRF.csv" );
dt08 = Open( "E:_383mm_008/FRF.csv" );
dt09 = Open( "E:_383mm_009/FRF.csv" );
dt10 = Open( "E:_383mm_010/FRF.csv" );

dt11 = Open( "E:_533mm_001/FRF.csv" );
dt12 = Open( "E:_533mm_002/FRF.csv" );
dt13 = Open( "E:_533mm_003/FRF.csv" );
dt14 = Open( "E:_533mm_004/FRF.csv" );
dt15 = Open( "E:_533mm_005/FRF.csv" );
dt16 = Open( "E:_533mm_006/FRF.csv" );
dt17 = Open( "E:_533mm_007/FRF.csv" );
dt18 = Open( "E:_533mm_008/FRF.csv" );
dt19 = Open( "E:_533mm_009/FRF.csv" );
dt20 = Open( "E:_533mm_010/FRF.csv" );

dt21 = Open( "E:_683mm_001/FRF.csv" );
dt22 = Open( "E:_683mm_002/FRF.csv" );
dt23 = Open( "E:_683mm_003/FRF.csv" );
dt24 = Open( "E:_683mm_004/FRF.csv" );
dt25 = Open( "E:_683mm_005/FRF.csv" );
dt26 = Open( "E:_683mm_006/FRF.csv" );
dt27 = Open( "E:_683mm_007/FRF.csv" );
dt28 = Open( "E:_683mm_008/FRF.csv" );
dt29 = Open( "E:_683mm_009/FRF.csv" );
dt30 = Open( "E:_683mm_010/FRF.csv" );

[#2]

Take a look at multiple file import. It is designed for opening many CSV from the same directory. File->MFI...

It will also create a script you can reuse.

[#3]

Hi Craige,

Thank you for the quick reply. My problem is that 30 files are not in the same folder. They are in 30 folders, I do not know how to open them using File->MFI. Thank you.

[#4 · Accepted solution]

try the MFI recursive flag at the e:/ directory. If there are not too many files on the E drive, it might work to use a file name pattern "FRF.csv"

More than 100K files will probably be slow to hunt them all down.

And yes, you can write a JSL loop. I might use something like this (untested, of course)

foreach( {size}, {"383","533","683"},
   foreach({run},{"001","002","003","004","005","006","007","008","009","010"},
      name = evalinsert("E:_^size^mm_^run^/FRF.csv") ;
      dt = open(name);
...
      close(dt,nosave);
   )
)