Append all excel files (.xls) contained in a folder

Hi, I have many result tables (in .xls format) with different file names in a specific folder. Is there any way that I can append/combine my results and save them into one single file? With an ImageJ macro or Java script? Avoiding repetition of headers, of course.

Thanks!

If you don’t mind using Python the pandas package can read Excel files https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Hi @insertsk8,

If all the .xls tables were created by Fiji, and all the headings are the same, you can use this (ugly) macro:

directory = getDirectory("Select a directory");

files =  getFileList(directory);

tableName = "fused tables";
tableText = "";

start=0;
for (i = 0; i < files.length; i++) {
	if(endsWith(files[i], ".xls")) {
		text = File.openAsString(directory+files[i]);
		lines = split(text, "\r\n");
		for(j = start; j<lines.length; j++) {
			tableText += lines[j] + "\r\n";
			}
		start=1;
		print("processed: ", files[i]);
		}
	}

Table.create(tableName);
lines = split(tableText, "\r\n");
headings = split(lines[0], "\t");
if(headings[0]==" ") firstCol=1; else firstCol=0; // in case of row numbers 
for (i = 1; i < lines.length; i++) {
	values = split(lines[i], "\t");
	for (j = firstCol; j < headings.length;  j++) {
		Table.set(headings[j], i-1, values[j]);
		}
	}

Table.update;
Table.showRowNumbers(true);
print("Done!");

On the other hand, if the headers are not the same for all files, you can try this (uglier) macro:

directory = getDirectory("Select a directory");
files =  getFileList(directory);

tableName = "fused tables";
Table.create(tableName);
nFiles = 0;
rowIndex = 0;

for (i = 0; i < files.length; i++) {
	if(endsWith(files[i], ".xls")){
		selectWindow(tableName);
		rowIndex =  Table.size;
		open(directory+files[i]);
		headings = split(Table.headings, "\t");
		for(j = 0; j < headings.length; j++){
			if (headings[j] != " "){
				selectWindow(files[i]);
				column = Table.getColumn(headings[j]);
				selectWindow(tableName);
				for(k = 0; k < column.length; k++ ){
					Table.set(headings[j], rowIndex+k, column[k]);
					}
				}
			}
		close(files[i]);
		print("processed: ", files[i]);
		}
	}
Table.update;
Table.showRowNumbers(true);
print("Done!");

Let me know if this is helpful to you.

Cheers,
Nico