312101_en-US

liliconcom

Combine multiple excel files

Hi, 

 

Sometimes I have to combine a lot of excel files. But "Import multiple files" menu would not work for excel files properly. So I wrote a JSL for it and I would like to share it here. It starts with select an excel file and open it using excel import wizard. Then the script gets import settings and files list in the directory. Finally, open all excel files (.xls, xlsx, xlsm) in the directory and combine them. Final dataset contains source files column at the right end of the table. 

 

I hope that this script will help your work. Any feedback would be really appreciated!

UtilitiesRe: Combine multiple excel files

Hallo, thank you @MKawaguchi for the jsl file. It is verry helpfull.

 

Is it possible also to add a collumne with the file Date (and Time)?

 

I try it but it not worke.

 

Thank you in edvanced.

Re: Combine multiple excel files

Hi @voy-voy , 

 

Thank you for your comment. 

I added 2 rows in "scr_head" variable for file modification date and time. 

mydate = format(Last Modification Date(fullfile),\!"y/m/d\!");
mytime = format(Last Modification Date(fullfile),\!"h:m:s\!");

Then I added 2 rows in "scr_tail" variable to create columns for date and time as well.

dt << New Column(\!"File_Date\!", character, <<set each value(mydate));
dt << New Column(\!"File_Time\!", character, <<set each value(mytime));

As a result, you will get file_date and file_time column in your combined datatable. 

 

I put overall script at the bottom of this comment. I hope it helps!

//define err_msg function;
err_msg = Function( {string},
 err_win = New Window( "Error",
  V List Box(
   H List Box(
    Icon Box( "Error" ),
    Text Box( string, <<SET wrap=""> 1, dt1 << Concatenate(dt, \!"Append to first table\!"); 
dt << closewindow))";

//concatenate modules;
final_script = scr_head || mysetting || scr_tail;

//run final script;
eval(parse(final_script));</SET>

 

Re: Combine multiple excel files

Thank you very mutch, now it works perfect.:)