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

Hi Nico,
I was trying to use your macro with cvs file but I did not have any merge file.
I would like to have a single excel file with all the data of singles cvs from a folder.
How I can adapt your macro for that?
I already changed xls for cvs

Thanks

My files have the name 0ROI.cvs 1ROI.cvs…etc because the macro that I use create the file in that format

Hi @Pablo_M,

Sorry for the delay! I totally missed your post.

If you want to modify the macro to work with .csv files, you should also change the delimiter from “\t” (tab) to “,” (comma)

This should work now:

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], ".csv")) {
		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], ","); // <--- separator
if(headings[0]==" ") firstCol=1; else firstCol=0; // in case of row numbers 
for (i = 1; i < lines.length; i++) {
	values = split(lines[i], ","); // <--- separator
	for (j = firstCol; j < headings.length;  j++) {
		Table.set(headings[j], i-1, values[j]);
		}
	}

Table.update;
Table.showRowNumbers(true);

Cheers!
Nico

Thank you very much, Nico :slight_smile: