606048_en-US

liliconcom

How to shorten the script of opening 30 Excel files?

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 we open and assign the opened Excel file to one data table? Copy and paste 30 times seem 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" );

AnalyticsData TableScriptingRe: How to shorten the script of opening 30 Excel files?

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.

Re: How to shorten the script of opening 30 Excel files?

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.

 

 

Re: How to shorten the script of opening 30 Excel files?

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);
   )
)