How to get round this problem with xlsread/xlwrite

Hi all, I have a problem with xlsread altering the values of some variables when I am loading data from a spreadsheet created by the xlwrite function. I suspect it is something to do with the variables themselves but am unsure. I describe the problem below:

I collected some variables from a spreadsheet as follows:

filename=uigetfile('*.*');
[NUM,TXT,RAW] = xlsread(filename);
name=TXT{3,20};
ABF_name=TXT{4,20};
Initial_weight=NUM(8,5);
Tr_day_1_date=TXT{25,1};
Num_tr_days = NUM(5,20);
Reward_pos = TXT{6,20};
FoR = TXT{7,20};

I save these variables into a new spreadsheet; as I am on a Mac I use the xlwrite function to write the data to an xlsx file

http://www.mathworks.co.uk/matlabcentral/fileexchange/38591-xlwrite--generate-xls-x--files-without-excel-on-mac-linux-win

xlwrite('Lever_training_metadata.xlsx',metadata);

When running the script again, I want to reload this data and append any new data:

if exist('Lever_training_metadata.xlsx','file')
  [A,B,oldmetadata]=xlsread('Lever_training_metadata.xlsx','A1:G1000');
  oldmetadata = oldmetadata(any(cellfun(@(x)any(~isnan(x)),oldmetadata),2),:);
else ....
end

These are the values copied directly from the initial .xlsx file:

name  ABF_name  Initial_weight  Tr_day_1_date  Num_Tr_days  Reward_pos FoW
M1_140927A  000  24.9  14o03  12  F  W

However, when I try loading the file using xlsread, I end up with the following in matlab:

oldmetadata = 

Columns 1 through 4

    'name' 'ABF_name' 'Initial_weight' 'Tr_day_1_date'
    'M1_140927A' '000' '24.9' '9' 

Columns 5 through 7

    'Num_Tr_days' 'Reward_pos' 'FoW'
    '12.0' [ 10] '11'

For some reason the values of Tr_day_1_date, Reward_pos and FoW have been altered.

Can anyone see any reason why this would be happening? I hope the jumble above is understandable enough.

Cheers,

Josh

[dpb]

[A,B,oldmetadata]=xlsread('Lever_training_metadata.xlsx','A1:G1000');
oldmetadata = oldmetadata(any(cellfun(@(x)any(~isnan(x)),oldmetadata),2),:);

What does oldmetadata look like before you pass it thru the cellfun call would be my first question?

Same with A, B

[Jan]

Without a proper formatting your code is unneccessarily hard to read. Why not using the "{} Code" button?

It is not getting clear to me, which file contains the modified data, where this file is created or touched and what you expect instead of the results you get. Where does the variable "metadata" come from?

Do you think that XLWRITE is buggy?

[Joshua]

In reply to dpb, oldmetadata looks as I have already posted, except below the values listed are 998 rows of [NaN], which the cellfun removes. The xlsread has already imported the incorrect values at this point.

In reply to Jan Simon, I'm sorry I made my post unnecessarily confusing, I shall attempt to make it more understandable below:

I have two excel files:

The first type contains the raw data, and there are several distinct copies, one for each of my experiments. The following code loads the data from one of the files, and defines the relevant variables based on the values within this data:

filename=uigetfile('*.*');

[NUM,TXT,RAW] = xlsread(filename);
name=TXT{3,20};
ABF_name=TXT{4,20};
Initial_weight=NUM(8,5);
Tr_day_1_date=TXT{25,1};
Num_tr_days = NUM(5,20);
Reward_pos = TXT{6,20};
FoW = TXT{7,20};

    newmetadata={name,ABF_name,Initial_weight,Tr_day_1_date,Num_tr_days,Reward_pos,FoR};

    xlwrite('Lever_training_metadata.xlsx', newmetadata);

The second file (Lever_training_metadata.xlsx) is created or added to every successive time I run the script and is supposed to be a summary of the aforementioned variables from the experimental data loaded to date. I am trying to update this second file by loading it, concatenating the old data with the new, and then overwriting the second file. This is probably a terribly inefficient way of doing things, but I am a matlab noob. The code I've come up with to do the second step is as follows:

if exist('Lever_training_metadata.xlsx','file')
    [A,B,oldmetadata]=xlsread('Lever_training_metadata.xlsx','A1:G1000');
    oldmetadata =      oldmetadata(any(cellfun(@(x)any(~isnan(x)),oldmetadata),2),:);
end

%The second line, as previously mentionned, is to remove the NaN values

However, already in the xlsread line, the second excel file is seemingly loaded incorrectly.

I don't think XLWRITE is buggy, as when I look at the second excel file, the values are correct. It is only when I try to load it back into matlab with XLSREAD that values start to become incorrect.

I hope this is more clear as to what I'm trying to do.

Perhaps it would be better if you could suggest a more efficient way of constructing a summary file of specific data from a series of spreadsheets. The second, summary file does not have to be an .xlsx file, I just found it convenient to do so. If there would be an easier way using .csv or .mat files I would be willing to try it, although I have already experimented with this to no avail.

Once again, thank you for any help you can provide me with this matter,

Josh

[dpb]

I'd suggest attaching a section of the .xls file that doesn't seem to import successfully so others can 'spearmint with the actual data instead of talking about it.

I don't have Mac so would have to be PC version but if there's something funky about the file itself perhaps it would show up.

As you say, you can always export to CSV altho the Matlab functions specifically for them are able to read only numeric data so you'd have to read them with textscan or the like.

[Joshua]

Here is the second file, after one raw data file has been summarised:

Using xlsread on this file is what causes me problems