Read excel data but keep formatting? Is this possible?

I have a huge excel data file of data collected every second and the time column is in the format 12:12:56, But when i do an xlsread I do not get the same string. Is there a way to import this properly?

Answer 1

[Sara]

Use this form of xlsread, you'll find your variable into txt or raw:

[num,txt,raw] = xlsread(___)

From the help: [num,txt,raw] = xlsread(_) additionally returns the text fields in cell array txt, and the unprocessed data (numbers and text) in cell array raw using any of the input arguments in the previous syntaxes. If xlRange is specified, leading blank rows and columns in the worksheet that precede rows and columns with data are returned in raw.

[matlabuser12]

I just tried that but still got no raw data. I have attached my excel file, and I ran [num,txt,raw]=xlsread('C:\data.xlsx','A3:A20')

[Roberto]

I can't see your attached file! please try uploading it again...

[matlabuser12]

I just did, is that better?

[Sara]

You're right, since it's a custom format it does not read it as it shows it. Use:

datestr(num,'HH:MM:SS PM')

where num is just the first column in the excel file.

[matlabuser12]

That works great to give me the times, but the output is a single ans? Or maybe I am reading this wrong, how can i then go back and plot the rate vs time with the time displayed on the graph in that format? My overall plan is to have matlab import the data, then ask the user for specific times that events occured (they track it in the HH:MM:SS format so they can input that in) and then plot the rate vs time, with labels on teh graph indicating the times that the user put in for certain events with a label.

[Sara]

Let's see if I have understood. You read the data in, then do you want the user to select only specific times or a range?

[matlabuser12]

1)User clicks on matlab and gui pops up, has a browse for data file box, they browse and select the appropriate excel file (these all have the same structure, but varying amounts of data, identical to the attachment but bigger and has some extra columns we dont use just time and rate are plotted).

2) User fills text box with data start and stop time ( we record continuously but that actual run data happens when it happens, so a lot of the collected data is worthless) in HH:MM:SS format

3)User then looks at their list of recorded events, and inputs the event name, and the time is occurred into the gui (anywhere from 0-30 events could be recorded) it is different each run

4)User hits generate plot button and they get a graph which plots the run time on the X against the data, with vertical markers indicating the times that the events entered occurred with a text label at each. This is based off the user entry.

[Sara]

Save your file as csv and try the code below

% User input
  str = 'Rate';
  init_time = '12:45:41 ';
  end_time  = '12:47:32';

    [time,storage] = findmyentries(str,init_time,end_time);
    figure
    plot(datenum(time),storage)
    y = get(gca,'xtick');y = y(1:2:end);
    set(gca,'xtick',y,'xticklabel',datestr(y,'HH:MM:SS'))

    function [time,storage] = findmyentries(str,init_time,end_time)

    init_time = datenum(init_time);
    end_time = datenum(end_time);

    filename = 'ddd.csv'; %%REPLACE
    [fid,msg] = fopen(filename,'r');
    if(fid==-1),error(msg),end
    header = DivideFields(fid);
    n = find(~cellfun(@isempty,strfind(header,str))==1,1);
    if(isempty(n)),error('field not found');end
    fgetl(fid);

    max_el = 100;
    time = cell(max_el,1);
    storage = zeros(max_el,1);
    k = 0;
    while 1
        var = DivideFields(fid);
        if(isempty(var{1})),break,end
        isbetween = CompareTime(var{1},init_time,end_time);
        if(isbetween == 1)
            k = k + 1;
            if(k > max_el)
                max_el = max_el + 100;
                t = time;time = cell(max_el,1);time(1:k-1) = t;
                t = storage;storage = zeros(max_el,1);storage(1:k-1) = t;
            end
            time{k} = var{1};
            storage(k) = str2num(var{n});
        elseif(isbetween == 2)
            break
        end
    end
    time = time(1:k);
    storage = storage(1:k);
    fclose(fid);

    function out = DivideFields(fid)
    out = fgetl(fid);
    if(~isempty(out))
        out = textscan(out,'%s','Delimiter',',');
        out = out{1};
    end

    function isbetween = CompareTime(var,init_time,end_time)
    current_time = datenum(var);
    if(current_time >= init_time && current_time <= end_time)
        isbetween = 1;
    elseif(current_time > end_time)
        isbetween = 2;
    else
        isbetween = 0;
    end

[matlabuser12]

I tried that code but received the following error:

Error using Textscan Invalid field identifier. Use fopen to generate a valid field identifier

[Sara]

Did you save your excel as csv before running the code? It won't work otherwise.

[matlabuser12]

yes it is a csv

[Sara]

I get the answer I think you wanted...can you attach your csv so I can check what's different?

[matlabuser12]

Here it is!

[Sara]

My bad. Replace:

[header,~] = DivideFields(fid);
[var,eof] = DivideFields(fid);

and

function [out,eof] = DivideFields(fid)
out = fgetl(fid);
eof = 0;
if(out == -1)
    out = [];
    eof = 1;
elseif(~isempty(out))
    out = textscan(out,'%s','Delimiter',',');
    out = out{1};
end

Now it should work.

[matlabuser12]

Still getting an error:

Cell contents reference from non-cell array object

Error in findmyentries line21

if(isempty(var(1))),break,end

[Sara]

I'm attaching the file so that I don't forget anything this time...modify as it pleases you.

[matlabuser12]

It looks like this one works, thank you! One last question, if I have a larger file with several headers in it, and breaks in data collection, this program does not seem to work. For example, the excel sheet has a long list of test descriptions in the first 25 cells, and then all the data headers (TIme, rate, etc) followed by the actual data for however long the program was running. if the program is stopped and restarted, it recopies in on the same sheet the same headers, before collecting data again. But the time is always accurate based on the computer time.

I attached a csv with what I am referring to

[Sara]

If you know how many lines of headers you have, you can just skip them:

for i = 1:25
  fgetl(fid);
end

So, do you know that it will always be 25?

For the second point, do you mean that in between the data you could have "Time Rate Pressure" repeated if you stop and restart the data acquisition or also the 25 extra lines?

[matlabuser12]

also the 25 lines, so if you copy-pasted everything a second time under the last line of ata in that file essentially. it could be anything, the comments are in a text file and are added to the beginning of every data collection. They are edited by whoeever is doing the data collection that day and they can write more or less.

The one thing that is constant is time will always be in the first column and rate in the second. Can i write a line in the code you provided ( I owe you btw) to search for Time in column A or even search for the units that are under it and THEN start looking for the time slot?

[Sara]

That's what I would do:

1. Search for the string time (to know when start reading data)
2. While reading, check that suddenly there is no empty cell or char
3. do not stop (as the code does now) but rather keep going until you find "time" again
4. repeat

I would use a while loop and read a max of 500 lines with no numeric data, so that you have a way to stop the code when you actually reach the end of file. I suppose none would use a header of 500 lines! Take a shot at it, in case you can post again if you go nowhere :) As for the code, it's for you to modify as you please.

[matlabuser12]

Thank you for all your help! I have like a million questions on here, if you're bored and want to help me more :P. Would uyou mind if I sent you a private message on here once I write down all the steps I am trying to accomplish? Your advice on what to learn about to make it happen would be really helpful.

[José-Luis]

If you're going to be reading every second and the file is large, then you're better off using activeX.

[matlabuser12]

Hi jose, the data is collected separately, and then processed a week later. I am restricted to a labview program to collect the data and it puts out an excel or csv in this format. So i have to do this if i want to try and automate graphing/labeling.

[Sara]

Jose meant that you can use activex in matlab. that's a way to do it too, but I have never used it to read from excel, only to write. you may want to look into it anyway.

[matlabuser12]

That's what I would do:

    Search for the string time (to know when start reading data)
    While reading, check that suddenly there is no empty cell or char
    do not stop (as the code does now) but rather keep going until you find "time" again
    repeat

I would use a while loop and read a max of 500 lines with no numeric data, so that you have a way to stop the code when you actually reach the end of file. I suppose none would use a header of 500 lines! Take a shot at it, in case you can post again if you go nowhere :) As for the code, it's for you to modify as you please.

I am struggling to do this, where in the code does this go?

Answer 2

[matlabuser12]

I am struggling to do this:

That's what I would do:

    Search for the string time (to know when start reading data)
    While reading, check that suddenly there is no empty cell or char
    do not stop (as the code does now) but rather keep going until you find "time" again
    repeat

I would use a while loop and read a max of 500 lines with no numeric data, so that you have a way to stop the code when you actually reach the end of file. I suppose none would use a header of 500 lines! Take a shot at it, in case you can post again if you go nowhere :) As for the code, it's for you to modify as you please.

Where in the code does this go?

[Sara]

Can you attach a draft of the code you are using? I suppose you started modifying things.

[matlabuser12]

I haven't really modified the mainddd you posted earlier much at all.Tried to incorporate a find after the n = find(~cellfun(@isempty,strfind(header,str))==1,1); line but am not sure that is the right place for this.

Since then been stuck at trying to convert the inputted timespan to seconds since I was just told that the graph needs to have 0-6hrs on the X axis in seconds. The way that the person currently does this is have a data column of [1:.0003:6] and they just plot against that every time. They are using excel though so that means it doesnt matter if the X-Y data size doesnt match but in matlab if the user selects a time span that is not equal to that many seconds it will never plot and just crash.