Cvs files in folder into one excel sheet macro?

Data Management
, , ,
#1

Hello
I have a folder full of cvs files from a macro that create ROI analysis. I would like to know if there are a macro that can read all the cvs in a folder and merge them into a one excel sheet.
I am aware of the plugin read and write excel but I do not know how can I adapt the macro of that plugin into my request.

I have been looking on the forum but I cannot find any macro that can do what I want.

Thanks in advance for any help.
Regards

#2

There are commands that can open and read text files.CSV files are just text files, so you can open these files in succession. While opening you can create one big Results table and then you can save this results table to a new csv file that can be loaded in a spreadsheet.
You can even put functions in a text file that, as long as they are considered syntactically correct, can be read by your spreadsheet. This file contains digits 1 through 3, tab separated, and a formula:

1 2 3 =sum(a1:c1)

and after import in Excel or Numbers cell D1 will contain 6.
spreadsheet test.txt (17 Bytes)
This allows you to even generate column totals by entering the appropriate formulas in the last line.

#3

Hi,
you can import multiple CSV files into image J using this macro:

//this script opens all Text images (.csv) in a folder and saves them as tiff32 images in the output directory

dir1 = getDirectory("Choose directory");
dir2 = getDirectory("Choose Destination Directory ");
list = getFileList(dir1);
run("Close All");
setBatchMode(true);
for (i=0; i<list.length; i++) {
 file = dir1 + list[i];
 run("Text Image... ", "open=&file");
}
run("Images to Stack", "use");
setBatchMode(false);
run("Stack to Images", "use");

setBatchMode(true);
imgArray = newArray(nImages);
for (i=0; i<nImages; i++) {
	selectImage(i+1);
	imgArray[i] = getImageID(); 
 }
for (i=0; i< imgArray.length; i++) {
	selectImage(imgArray[i]);
	saveAs("TIFF", dir2+list[i]);
 }
run("Close All");

If you want to combine multiple tab delimited txt files you can use R studio.
The following script imports all files in a folder “Data” on the Desktop and creates a column for each file in a new data frame but it is also easily possible to write one dataframe for each file.

setwd("~/Desktop/Data")
files <-list.files()
df <- list()
for (f in files) {
  tempData = scan(f)
  df[[f]] <- tempData
} 
Sample <- as.data.frame(df)

If you want to create an excel file with one sheet for each file it is possible to create a list containing all open dataframes and write this list to excel using writexl package:

samplelist <- setNames(lapply(ls(), function(x) if (class(get(x)) == "data.frame") get(x)), ls())

1 Like
#5

Thank you very much. This is very helpful!