ansaurus

Question

Answer 1

A:

Use xlswrite(filename, M, range) to write your files one after the other. Read the Excel file into M with xlsread.

xlswrite(filename, M, range) writes matrix M to a rectangular region specified by range in the first worksheet of the file filename.

Jacob 2010-03-18 22:47:09

You can use uigetfile to get the user to select which files they want to concatenate together, then use xlsread and xlswrite to read them in to matlab, and write them out again

Fuzz 2010-03-18 22:51:48

Answer 2

+1 A:

Get file names

names=dir('XXXXX-*.xls');
names={names.name};
output='out.xls';

First file. This will overwrite the output each time you run this program - it's up to you if this is the behavior you want.

copyfile(names{1},output);

Cycle through the files

for i=2:length(names)
  num_in = xlsread(names{i}); % read the data
  num_out = xlsread(output);      

  range=['A' num2str(size(num_out,1)+1)]; % next free line 
  xlswrite(output, num_in, 1, range); %always write to the 1st sheet
end

This should work if (1) you only have numerical data and (2) you want to concatenate ("sum", as you put it) the files top to bottom.

If (1) is wrong, please read xlsread's help -- look for txt and raw outputs.

AVB 2010-03-18 23:42:42

does not work, says filename should be string

AP 2010-03-19 01:53:16

There's at least one error: it should be names{i}

Jonas 2010-03-19 01:59:48

@AP: sorry, fixed; @Jonas: thanks! I could not run this to check...

AVB 2010-03-19 02:08:57

@AB : i tried , it creates a new sheet A and copies over ( overwrites ) so i only have my last file

AP 2010-03-19 02:11:17

@AP: finally got to MATLAB, checked, fixed, edited - it works for me. The problem with the new sheet is a bit strange. I've solved it by adding a ',1' in the last line, but if I understand XLSWRITE's help correctly, it should have worked without it. Never mind, though - it really should be OK now, as it appears in the answer.

AVB 2010-03-19 03:09:19

@AB wow, thanks a lot buddy, i still do not understand looking at code and reading the help of matlab, but it works for 5 dummy files , i hope it works for my huge 100 files at work tomorrow.

AP 2010-03-19 03:38:35

@AB: The 'sheet' and 'range' arguments to XLSWRITE are tricky. If you only enter three inputs, XLSWRITE will interpret '1' or 'A1' as the *sheet* name, but 'A2:D2' will be interpreted as a *range*. The presence of a colon makes the difference. That's why it's usually better to specify 4 inputs (both sheet and range) to avoid confusion. (This is explained in the note in the blue box of the XLSWRITE documentation: http://www.mathworks.com/access/helpdesk/help/techdoc/ref/xlswrite.html)

gnovice 2010-03-19 04:20:50

@gnovice: Well, I guess MathWorks got quite a few complaints about not documenting that - in my R2007a there is no such blue box.

AVB 2010-03-19 04:33:26

A question: When i run the above script. It works fine for my 10 odd files. But my first column is empty. Thats how my sticthed file looks , however from 2nd file onwards it deletes the empty column and shifts all data left. Anyway to get arround this?.

AP 2010-03-19 14:14:56

@AP: make it `range=['B' ...` then. MATLAB disregards empty space in the first output of XLSWRITE

AVB 2010-03-19 16:17:31

@AB changing from 'A' to 'B' does not disregard the empty space but works for me as it keeps the resultant file in same format as each files with the first column empty.Thanks a lot

AP 2010-03-19 17:08:41

ansaurus

tags:

views:

answers:

summing up excel files in matlab

related questions