find strings in excel on different sheets

I found a question asking a basically the same thing as me: https://www.mathworks.com/matlabcentral/answers/502430-search-through-an-excel-file-and-save-sheet-and-cells-as-variables?s_tid=prof_contriblnk

basically, this finds a user inputted string, and returnes the row in which that string is found.

this is the code I adapted from there:

in = input('Enter code: ', 's');
[~,~,raw] = xlsread('examplesheet.xlsx');
p = strcmp(in,raw);% Compare user input string with entries in the Excel sheet
rowNum = find(p==1)%Get Row number

this works for my purpose as well, with 1 caveat: it can only search the first sheet in an excel sheet. is there any way to get this to look through multiple sheets, and return the row and sheet name in 2 different variabvles? I have included an example sheet here.

Answer 1

[Walter Roberson]

filename = 'examplesheet.xlsx';
[~, sheets] = xlsfinfo(filename);

rows_found = [];
sheets_found = {};

for K = 1 : length(sheets)
    this_sheet = sheets{K};
    [~, ~, raw] = xlsread(filename, this_sheet);
    [rowNum, colNum] = find( strcmp(in, raw));
    if ~isempty(rowNum)
        rows_found = [rows_found; rowNum];
        sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
    end
end

Output is a numeric matrix rows_found and a cell array of character strings sheets_found . rows_found(K) is arow number and sheets_found{K} is the corresponding sheet name.

[avram alter]

Once again you save me. Thank you so much, from the bottom of my heart

[avram alter]

I've moved along in my code, and this code has stopped working properly. i have appended the new code here. instead of a user input, this code takes data from serial, and uses it to find the excel sheet. It doesn't get the correct sheet or row.

In plat of the variable name "in", I use "extracted_data".

addpath('C:\Users\Administrator\Dropbox (********)\******** Team Folder\Matlab\RFID chip reader\RfidChipData');
filename = 'CorrectedRFIDValues.xlsx';
[~, sheets] = xlsfinfo(filename);
% decision = fscanf(tags{portidx});
% [decision, receivedcount] = fscanf(tags{portidx});
delete(instrfind());
deciding_port = serialport('COM3', 9600);
deciding_data = readline(deciding_port)
while strlength(deciding_data) < 24
   deciding_data = readline(deciding_port)
   pause(.2)
end
% decision = waitfor(readline(deciding_port), strlength(readline(deciding_port)), 24);
% if receivedcount < 24
%     error('Received less data than expected. Received data was: %s', decision)
% end
%now we know we've got at least 24 characters. 
extracted_data = extractAfter(deciding_data, 11);
extracted_data = extractBefore(extracted_data, 14);

rows_found = [];
sheets_found = {};
for K = 1 : length(sheets)
    this_sheet = sheets{K};
    [~, ~, raw] = xlsread(filename, this_sheet);
    [rowNum, colNum] = find( strcmp(extracted_data, raw));
    if ~isempty(rowNum)
        rows_found = [rows_found; rowNum];
        sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
    end
end

the first section reads from COM3, and loops until there is a tag present. once a tag is presented, it extracts only the tag, and then uses that to find the correct sheet and row.

[Walter Roberson]

I notice you did not configureTerminator() on the serialport() object. Is it possible that it is sending CR as well as LF ? That would not be stripped off . It is worth examining extracted_data

Once you have extracted_data on hand, then where it came from is not relevant, so you should be able to debug the matching independently.

[avram alter]

There is a carriage return included in extracted_data. In what way can a carriage return be removed from a string?

Would using

extracted_data = regexprep(extracted_data,'[\n\r]+','')

Work?

[Walter Roberson]

Yes, that should work.

However if you configureTerminator then it should automatically eat the terminators you indicate, breaking the line at that point.

[avram alter]

Each of my tags ends with a % character. would using that as the terminator remove the % as well, or only the things after that?

deciding_port = serialport('COM3', 9600);
configureTerminator(deciding_port, '%');
deciding_port.Terminator

ans =
'%'

[Walter Roberson]

The configured terminator would signal end of line. It would be removed from what is returned to the user, and whatever else was received after would be left in the buffer, not to be returned until another read call and another % received.

If you have cr/lf those almost always represent end of the unit for processing purposes and should be the terminator.

There are some uncommon cases with binary data where cr or lf are data and something else like SOH (0x01, control-A) marks a processing boundary

[avram alter]

Is there any way for the Terminator character to he included in what is returned to the user?

[Walter Roberson]

If that were needed, then I would suggest using serial() instead of serialport(), and using fgets() on the serial object. readline() on serialport objects is the equivalent of fgetl in serial objects.

[avram alter]

Is that potentially why I am getting weird Unicode in my tags? Other places in my code I use fscanf on serial(), and my tags scan fine. Is there some kind of issue with readline() where the tags get scanned incorrectly sometimes?

[Walter Roberson]

If your stream includes characters with position beyond 255 then I would recommend using fread() and native2unicode or unicode2native.

If your stream contains bytes with the first two bits set then if you were reading in a mode that expected text and the encoding were not configured otherwise, then potentially the bytes could be understood as introducing a utf8 sequence. If the first bit is set but not the second then that could be a continuation byte in utf8 but the implementation would have to be broken for utf8 to be triggered without an introducer byte with both first two bits set.

... Basically if you have bytes above 7f then be sure to configure the translation mode, or else use fread and do the translation yourself.

I would need to review the serial() and serialport() settings to control multibyte translation.

[avram alter]

That might be the issue. First couple bytes sent are set, so I'll try setting it to read as a character to attempt to translate properly. If that doesn't work, could I post the files so you can take a look?

[avram alter]

EDIT: I was able to find a fix for the code, just replaced a different variable type, and got rid of some of the concatonations. I noticed that nearly all the weird unicode gibberish occurs where a ) should be on the of a tag. It very rarely happens with any other number.

[Walter Roberson]

After the line

extracted_data = char(extracted_data);

has been executed, what shows up for

double(extracted_data)

[avram alter]

when running the debugger (with breakpoint set at line 94)

94  extracted_data = char(extracted_data);
K>> double(extracted_data)

ans =

    48    48    48    48    48    48    48    50    48    70    48    68     3    37

K>> 

those are the acsii values for each character in the string. where the second to last 3 is should be a 48, indicating another 0.

3 is an etx, i am unsure why that is at that point.

every single one of the unicode gibberish i get is either 1 and/or 2 spaces before the ending %.

[Walter Roberson]

Could you show double() of the data before you apply the char() to it? And also double() of the data just after it has been read in and before any processing.

[avram alter]

this is the double before char:

K>> extracted_data

extracted_data = 

    "000000020F0D%"
K>> double(extracted_data)

ans =

   NaN

K>> 

this is the double before any processing is done

K>> deciding_data % precursor to extracted_data

deciding_data = 

    "Reader 1: 000000020F0D%
     "

K>> double(deciding_data)

ans =

   NaN

K>> 

[Walter Roberson]

Ah, I hadn't noticed that readline() returns string() datatype. Okay, so then

double(deciding_data{1})

should give what is needed.

[avram alter]

Before char is done:

K>> extracted_data

extracted_data = 

    "000000020F0D%"

K>> double(extracted_data{1})

ans =

    48    48    48    48    48    48    48    50    48    70    48    68     3    37

before any processing is done:

K>> deciding_data

deciding_data = 

    "Reader 1: 000000020F0D%
     "

K>> double(deciding_data{1})

ans =

  Columns 1 through 15

    82   101    97   100   101   114    32    49    58    32    48    48    48    48    48

  Columns 16 through 27

    48    48    50    48    70    48    68     3    37     4    26    13

[Walter Roberson]

3    37     4    26    13

Well, that is a bit strange. ETX, %, EOT, EOF, CR . It's like someone wanted to be really sure that the line terminator was received.

I suggest you experiment with

regexp(deciding_data, '[ -~]+', 'match', 'once')

which will only match characters from space (32) to ~ (126) . That excludes newline (10) and carriage return (13) and ETX (3) and EOT (4) and EOF (26). Note that the % will get trimemd by this, as it is after the first character in the range 0 to 31 .

[Walter Roberson]

By the way, I happened to notice that the documentation for readline() for serialport() objects specifically says "Read line of ASCII string data from serial port". That implies no Unicode translation is taking place.

[avram alter]

That's very odd, and possibly explains why I am getting those incorrect reads. One problem I foresee is that the tags sometimes scans in with the ETX before the %, and sometimes scans with a zero. I don't know exactly why a zero replaces the ETX, (or why etx replaces a 0). Is there a way around that? As in, make the search code work whether there is an etx or zero in that space? Alternatively, since each tag ends with a 0% (or maybe an etx%), I can just trim off any trailing zeros.

[Walter Roberson]

Unless you need the % to distinguish something then use the regexp match that I posted to extract the initial string of printable characters and ignore what is after. You might still need to trim off the % if you happen to receive a packet with no binary garbage. If % is not valid earlier in the string you can adjust the pattern I posted to '[ -$&-~]'

[avram alter]

That fix seems to be working. By trimming off any weird Unicode as well as the 0 and % off of everything. It all seems to be working for now, I just need to change some indices lengths for the new shorter code. Thanks for all the help with this

[avram alter]

one last question. I define sheets_found and rows_found in this part of the gui:

for K = 1 : length(sheets)
    this_sheet = sheets{K};
    [~, ~, raw] = xlsread(filename, this_sheet);
    [rowNum, colNum] = find( strcmp(extracted_data, raw));
    if ~isempty(rowNum)
        rows_found = [rows_found; rowNum];
        sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
    end
end

later in the same gui, but in a different panel of the gui, I would like to use sheets_found and rows_found to define a new variable. how do I declare a global variable in a gui?

[Walter Roberson]

http://matlab.wikia.com/wiki/FAQ#How_can_I_share_data_between_callback_functions_in_my_GUI.28s.29.3F

[avram alter]

thanks man, much appreciated