Re: how to append results in excel file

Posted by Michael Schmid on Aug 02, 2017; 8:33am
URL: http://imagej.273.s1.nabble.com/how-to-append-results-in-excel-file-tp5002126p5019153.html

Hi Guy,

instead of opening each single .csv file in Excel, copy & paste into one
big sheet you could write a short macro to combine the .csv files into a
single file. Something roughly like this:

getFileList(directory) to get the list of files
loop over all suitable files:
   str = File.openAsString(directory+filename);
   For the first file:
     File.saveString(str, path);
   For the subsequent files:
     lineOneEnd = indexOf(str, '\n');
     str = substring(str, lineOneEnd+1);
     File.append(str, path);

If you want to have a column for the name for the source file, you would
have to split the string read from each file into lines
   lines = split(str,"\n")
and for each line (except the header line), append
   filename + "," + line +"\n"
to a String Buffer, then convert it to a string and save it once.
   https://imagej.nih.gov/ij/developer/macro/functions.html#String

[If Excel on Windows does not like the unix-style "\n" line ending, use
fromCharCode(13,10) instead]

Michael
________________________________________________________________
On 02/08/2017 05:51, Guy Lyons wrote:

> Thanks for the rapid reply, Brandon. I'm using Mac OsX 10.10.5, ImageJ
> 2.0.0-rc-43/1.51p. The macro as it stands has over 100 lines, so I'm not
> sure if it would be helpful to include it here. The macro currently is
> designed to analyse the output from a Cytation 3, which takes images of cell
> cultures in 3 channels from multiple areas of a culture vessel. There is a
> DAPI channel and 2 nuclear antigen-specific channels, and the Cytation
> software saves each channel in a separate file. Typically, 64 images are
> obtained per channel for each sample. The macro first uses the DAPI channel
> to create a mask to identify the nuclei and then uses a for loop to measure
> the area and the integrated density for each channel of each nucleus,
> writing the data for the area and each channel to a separate array, and then
> finally combining the arrays using Array.show and saving it as a csv. The
> macro does this for all of the Cytation 3 images in a folder. It does this
> successfully and reasonably quickly, writing the data to a csv file for each
> image. We (i.e. my student) then manually combine the data from the 64 csv
> files by copying and pasting within Excel – a tedious task that includes the
> possibility of making mistakes.
>
> The strategies that I have tried in order to generate a single file
> (including some based on the links that you sent) by modifying the for loop
> run into the problem of having to write to the Results window but also
> having to "Clear Results" between analysis of channels (each channel being
> recorded in a separate original file). The simplest solution for me would be
> a method that combines the data of multiple csv files into a single csv file
> with just one row of headers. Maybe someone out there can accept this as a
> challenge? I'm sure I'm not the only one who would find it useful.
>
> Guy.
>
>
> Brandon Hurr wrote
>> Depends how you're doing your analysis.
>>
>> If you're looping through pictures in a folder then you should create a
>> results table and print to that in every iteration. I typically save the
>> results table to a file every loop too in case of problems. You can just
>> overwrite this every iteration rather than append (writing a csv file is
>> not that time consuming unless the file is huge).
>>
>> If you're doing something else... you'll have to spell that out and help
>> us
>> help you (and others).
>>
>> How to make a custom results table and record to it:
>> http://imagej.1557.x6.nabble.com/How-to-create-a-customised-results-table-and-add-data-to-it-within-a-macro-td5000701.html
>> Save that file out as csv (txt):
>> http://imagej.1557.x6.nabble.com/save-results-table-as-csv-with-custom-name-td5003427.html
>>
>> Put that into a loop and you're golden.
>>
>> B
>>
>> On Tue, Aug 1, 2017 at 7:15 PM, Guy Lyons <
>
>> guy.lyons@.edu
>
>> > wrote:
>>
>>> I am trying to do exactly what Annie Venien described in the original
>>> post
>>> using ImageJ macro language i.e. save analysis data from images into
>>> individual files AND also combine them into a single csv file. Tried all
>>> sorts of things involving strings, File.append, etc., to combine them
>>> into
>>> a
>>> single csv file, but can't crack it. A lot of people have requested
>>> advice
>>> for the same thing in various forums. The "Read and Write Excel" plugin
>>> adds
>>> data side-by-side, instead of appending it in columns. Has anyone come up
>>> with a plugin or other method to achieve this, either as you go during
>>> analysis of multiple files, or post-analysis to concatenate csv data
>>> files
>>> vertically?
>>>
>>>
>>>
>>> --
>>> View this message in context: http://imagej.1557.x6.nabble.
>>> com/how-to-append-results-in-excel-file-tp5002126p5019149.html
>>> Sent from the ImageJ mailing list archive at Nabble.com.
>>>
>>> --
>>> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>>>
>>
>> --
>> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>
>
>
>
>
> --
> View this message in context: http://imagej.1557.x6.nabble.com/how-to-append-results-in-excel-file-tp5002126p5019151.html
> Sent from the ImageJ mailing list archive at Nabble.com.
>
> --
> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>

--
ImageJ mailing list: http://imagej.nih.gov/ij/list.html