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

Thank you so much! A fantastic script that makes it possible for me to investigate a myriad of Excel files I have at hand!

Re: Combine multiple excel files

Thank you for this script. It works well except that if there is a unique tab ID in the Excel file it bombs. Is there a way to ignore what is in the tab?

 

Cheers.

Re: Combine multiple excel files

Thank you!

After running your JSL script, I selected an excel file as you explained. But actually, i don't understand "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.". For me, It doesn't work at all.Can you explain it more? I want to merge 3 files in one file to analyze some data and to make some graphs and so on. Thx:)

Re: Combine multiple excel files

Thank you very mutch, now it works perfect.

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

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

Thank you @MKawaguchi for this useful script!
Also thank you to @students_t for mentioning tab IDs which helped me to troubleshoot when the script only collected two files instead of many.
The script worked nicely for me when the sheets in the different files had the same name.
Other sheets with different names were ignored, which also was what I wanted.