Combine multiple excel files

[#1]

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!

[voy-voy]

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.

[MKawaguchi]

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( 400 ), <<Padding( Left( 20 ) ) ),
				<<Padding( Left( 10 ), Right( 10 ), Bottom( 15 ) )
			),
			H Center Box( OK_btn = Button Box( "OK", err_win << Close Window ) )
		)
	);
	Stop();
);

//open one excel table with excel wizard;
myfile = Pick File( "Select Excel File", , {"Excel files|xlsx;xlsm;xls"} ); 
If( myfile == "", err_msg("No file available")); 

mytable = Open( myfile, "excel wizard" );
if(isempty(mytable) == 1, err_msg("No datatable available"));

//get directory path;
dir = left(myfile, contains(myfile, "/",-1));

//get file list in the directory;
files = Files In Directory(dir);
files_cnt = nitems(files);
For( i = 1, i <= files_cnt, i++,
	if(left(files[files_cnt+1-i],2) == "~$" | contains(files[files_cnt+1-i],".xls") == 0,files = remove(files,files_cnt+1-i))
);


//get source script from "Source";
sourcescript = Current Data Table() << get property( "Source" );
if(isempty(sourcescript),err_msg("No Source Script in the Datatable"),mysource = Char( Name Expr( sourcescript ) ));

//extract excel import settings;
mylen = Length( mysource );
myleft = Contains( mysource, "\!", " );
mysetting = Substr( mysource, myleft + 3, mylen - myleft - 2 );

//close excel table;
mytable << close window (nosave);

//define module for import multiple excel files;
scr_head =
"For(i = 1, i <= N Items(files), i++, 
	fullfile = dir || files[i]; 
	mydate = format(Last Modification Date(fullfile),\!"y/m/d\!");
	mytime = format(Last Modification Date(fullfile),\!"h:m:s\!");
	dt = Open(fullfile, ";

scr_tail =
";
dt << New Column(\!"Source File\!", character, width(8), <<set each value(files[i]));
dt << New Column(\!"File_Date\!", character, <<set each value(mydate));
dt << New Column(\!"File_Time\!", character, <<set each value(mytime));
If(i == 1, dt1 = dt; dt1 << Set name(\!"Combined Datatable\!")); 
If(i > 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));

[voy-voy]

Thank you very mutch, now it works perfect.

[yummy]

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

[students_t]

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.

[Ake]

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

[MerGrung]

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.