cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
Combine multiple excel files
MKawaguchi
Level III

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!

Comments
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!

MKawaguchi_1-1605749222139.png

//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!