Excel writing Problem: find correct row, write to empty cell

Hello Community,

I have a fairly advanced excel writing task. I have 2 types filenames that I need to write to specific cells in a spreadsheet. I acquire these names from user selection in a GUI. The files are named in this fashion: Label_SLR_LL_S1_0006_v1

Segment_SCH_RL_S3_0008_v1

So the files with "Label" are written to the Label and column and so forth.

First problem is I need to write to the next empty row instead of overwriting existing data. Ideally, I would like to check to see if the data already exist in the column, if it does, overwrite, if not find empty cell and write.

The second problem is the sheet contains data that is organized by row in this fashion:

2009-10-06 09-26-15_SHC_RL_S1_0002FinalData.mat

2009-10-06 09-28-01_SHC_RL_S2_0002FinalData.mat

2009-10-06 09-37-31_SHC_LL_S1_0002FinalData.mat

2009-10-06 09-39-18_SHC_LL_S2_0002FinalData.mat

2009-10-06 09-48-43_RHA_RL_S2_0002FinalData.mat

2009-10-06 09-50-06_RHA_RL_S3_0002FinalData.mat

2009-10-06 10-09-46_SLR_RL_S1_0002FinalData.mat

So the keywords are

SLR_LL_S1

SHC_LL_S2

RHA_RL_S3

and so forth Each row in the the sheet contains data specific to that identifer. I need to be able to write my filenames:

Label_SLR_LL_S1_0006_v1

Segment_SCH_RL_S3_0008_v1

into the correct row based on the keywords.

The second part of my problem might not be necessary if a simple sort in excel can put my filenames in the correct order? I dont think it can because of how they are named.

I realize this a hefty question and any suggestion are greatly appreciated. I litteraly spent over 3 hours searching the Answers and newsgroup but couldt find a solution to this problem.

[Fangjun Jiang]

Please double check your question.

SCH_RL or SHC_RL?

What are all the keyworkds? The three provided don't cover all the examples.

[B_Richardson]

Here all all possible keywords:

SHC_RL_S1_

SHC_RL_S2_

SHC_RL_S3_

SHC_LL_S1_

SHC_LL_S2_

SHC_LL_S3_

RHA_RL_S1_

RHA_RL_S2_

RHA_RL_S3_

RHA_LL_S1_

RHA_LL_S2_

RHA_LL_S3_

SLR_RL_S1_

SLR_RL_S2_

SLR_RL_S3_

SLR_RL_S4_

SLR_LL_S1_

SLR_LL_S2_

SLR_LL_S3_

SLR_LL_S4_

[B_Richardson]

this is an example of what I'm trying to accomplish:

Header..............Label.........................Segment.........

SHC_RL_S1.....Label_SHC_RL_S1_0006_v1.....Segment_SHC_RL_S1_0006_1

SHC_RL_S2.....Label_SHC_RL_S2_0006_v1.....Segment_SHC_RL_S2_0006_1

Like I said, having the data in the correct row might not be important if the sort in Microsoft excel can put them in correct order as long as they are in the correct column and the filenames are writing to the next empty cell instead of overwriting everytime the GUI is ran.

[Fangjun Jiang]

So, to summarize, you have nx3 strings in an Excel file, you want to insert new strings into the right cell. The right cell means that the strings with the same keywords are grouped together. I assume you want to insert at least 3 strings at a time, with one for each of the "Header", "Label" and "Segment" column. These 3 strings shall have the same keywords.

[B_Richardson]

The insertion will occur one at a time in 2 pushbutton callbacks, 1 for Labels and the other for Segment. The data in the column "Header" is always there and doesnt change. I only wont to insert data into the "Segment" and "Label" columns.

Answer 1

[Fangjun Jiang]

There are still things you didn't clarify. The data in the column "Header", are there multiple lines of file name that contain the same key? If not,, the following code should be good enough.

NewFileName='Label_SLR_LL_S1_0006_v1';
if strfind(NewFileName,'Label_')
    Column=2;
    Key=NewFileName(7:16);
elseif strfind(NewFileName,'Segment_');
    Column=3;
    Key=NewFileName(9:18);
else
    error('Wrong File Name.');
end

ExcelFile='MyData.xls';
[Dummy,Dummy,Raw]=xlsread(ExcelFile);

Index=strfind(Raw(:,1),Key);
Index=find(~cellfun('isempty',Index));
Raw{Index,Column}=NewFileName;

xslwrite(ExcelFile,Raw);

[B_Richardson]

No, the data an actual key, "SLR_LL_S1" will never repeat.

Is this necessary?

if strfind(NewFileName,'Label_')

Column=2;

Is this finding the correct column? I know the column number for "Label" and "Segment" those never change. And can you give me an overview on how this works? Thank you so much.

[B_Richardson]

i get this error. I'm trying to figure it out.

??? The left hand side is initialized and has an empty range of indices.

However, the right hand side returned one or more results.

[Fangjun Jiang]

If the key in your first column data never repeats, it makes the task easier. The code above should apply.

The if statement is looking for 'Label_' or 'Segment_' to determine the NewFileName goes to second or third column.

Which line did the error occured?

[B_Richardson]

Ok, so I can take that if statement out because the "Label" and "Segment" are written at different times any ways.

>> NewFileName='Label_SLR_LL_S1_0006_v1';

Column=7; %this is the cloumn #

Key=NewFileName(7:16); %What does this do?

ExcelFile='Test.xlsx';

[Dummy,Dummy,Raw]=xlsread(ExcelFile);

Index=strfind(Raw(:,1),Key);

Index=find(~cellfun('isempty',Index));

Raw{Index,Column}=NewFileName;

xslwrite(ExcelFile,Raw);

??? The left hand side is initialized and has an empty range of indices.

However, the right hand side returned one or more results.

it doesnt say where

[B_Richardson]

Actually, I am mistaken! That keyword will repeat! The only data that wont repeat is in column #4 "RHA_LL_S1_0005_v1.59806163.avi"

[B_Richardson]

%Column 4

----------Video--------------

SHC_RL_S1_0005_v1.59806163.avi

SHC_RL_S2_0005_v1.59806163.avi

SHC_RL_S3_0005_v1.59806163.avi

SHC_LL_S1_0005_v1.59806163.avi

SHC_LL_S2_0005_v1.59806163.avi

SHC_LL_S3_0005_v1.59806163.avi

RHA_RL_S1_0005_v1.59806163.avi

RHA_RL_S2_0005_v1.59806163.avi

RHA_RL_S3_0005_v1.59806163.avi

RHA_LL_S1_0005_v1.59806163.avi

RHA_LL_S2_0005_v1.59806163.avi

RHA_LL_S3_0005_v1.59806163.avi

SLR_RL_S1_0005_v1.59806163.avi

SLR_RL_S2_0005_v1.59806163.avi

SLR_RL_S3_0005_v1.59806163.avi

SLR_RL_S4_0005_v1.59806163.avi

SLR_LL_S1_0005_v1.59806163.avi

SLR_LL_S2_0005_v1.59806163.avi

SLR_LL_S3_0005_v1.59806163.avi

SLR_LL_S4_0005_v1.59806163.avi

[Fangjun Jiang]

Wait a minute. You are going wild with all the extra info.

Key=NewFileName(7:16) is taking the 7th to 16th characters from NewFileName. That's an easy way to get the key word because I thought your file names are always in a consistent format.

You can put a break point in the code and run the code line by line. At any time, you can observe the value of any variable to see the effect of the code and also help you to understand the code. I thought you've been here long enough to know this.

[Fangjun Jiang]

If the data in your first column could have duplicated key word, how do you decide which row to put if the new file name has that key word? This is a very important question you have to be clear at the beginning.

[B_Richardson]

"You can put a break point in the code and run the code line by line. At any time, you can observe the value of any variable to see the effect of the code and also help you to understand the code. I thought you've been here long enough to know this."

No, I had no idea. That sounds very convenient.

"If the data in your first column could have duplicated key word, how do you decide which row to put if the new file name has that key word? This is a very important question you have to be clear at the beginning."

Thats a good question. I thought it would be possible to find the correct row # by matching with column 7 since the data in that column never repeats. After making that match, because we already know the column #, we could write to the correct cell? Thats what I had in mind but I'm not sure if its possible with my limited skills at this point.

As I said before, excel might be able to properly sort the data in the column, so finding the exact correct row might not be necessary. As long as its writing to the next empty cell

Answer 2

[Fangjun Jiang]

Watch this video: http://blogs.mathworks.com/videos/2010/09/02/using-debugger-to-walk-through-code/

You can't expect an exact code to solve your problem with so much details.

[B_Richardson]

Of course not, I certainly do expect that. If I was better, I could probably use the bits and pieces to put together a solution. I'm not quite there but I'm getting better. I think my problem is I dont know all of matlabs functions or the syntax to use the functions just quite right yet. I guess that comes with time! This tutorial you posted should help me move forward! Thank you

[B_Richardson]

But thats why I hate when people just post answers without explaining the code or why they chose that method. People will never improve if they are just handed code. Most of the time I'm more interested in the logic behind the code, more so than the actual code itself. Thats where the real "answer" is.

[Fangjun Jiang]

In that case, it is import to ask for a particular question, using simple example, not with overwhelming task details. It is true that sometimes the answers provided are hard to understand. Maybe people want to show off their skills with those one-line or no-loop code. What you can do is to ask for further explanation, or the best way is to try it out in MATLAB, step by step.

[B_Richardson]

So I should provide less details when asking questions here on out?

[Fangjun Jiang]

Not less details but enough or sufficient details. For example, if you want to ask how to extract the keyword 'SHC_RL_S1' from the file name '2009-10-06 09-26-15_SHC_RL_S1_0002FinalData.mat', you may just need to state this and provide maybe a few examples of the file names. Next, you may ask how to match this keyword to a list of existing keyword. Once you got answers to all this individual, specific questions, you'll able to stitch them together and solve your problem. You may have a huge, complicated task, you need to do divide-and-conquer.

[B_Richardson]

I see. I'm trying to practice using the debugger. It doesnt seem to let me create breakpoints anywhere I want. Is there another tutorial somewhere?

[Fangjun Jiang]

Doug Hull provided lots of tutorial video over there, you can watch them whenever you like. Not every line can be put in a break point. You don't need to actually. Have a few break point and then you can press F10 to run line by line.