You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Read excel data but keep formatting? Is this possible?
13 views (last 30 days)
Show older comments
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?
Answers (2)
Sara
on 6 May 2014
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.
25 Comments
matlabuser12
on 6 May 2014
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')
Sara
on 6 May 2014
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
on 6 May 2014
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
on 6 May 2014
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
on 6 May 2014
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
on 6 May 2014
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
on 7 May 2014
Edited: matlabuser12
on 7 May 2014
I tried that code but received the following error:
Error using Textscan Invalid field identifier. Use fopen to generate a valid field identifier
Sara
on 7 May 2014
I get the answer I think you wanted...can you attach your csv so I can check what's different?
Sara
on 7 May 2014
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
on 8 May 2014
Still getting an error:
Cell contents reference from non-cell array object
Error in findmyentries line21
if(isempty(var(1))),break,end
Sara
on 8 May 2014
I'm attaching the file so that I don't forget anything this time...modify as it pleases you.
matlabuser12
on 8 May 2014
Edited: matlabuser12
on 8 May 2014
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
on 8 May 2014
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
on 8 May 2014
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
on 8 May 2014
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.
matlabuser12
on 8 May 2014
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.
matlabuser12
on 8 May 2014
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
on 8 May 2014
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
on 20 May 2014
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?
matlabuser12
on 20 May 2014
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?
2 Comments
Sara
on 20 May 2014
Can you attach a draft of the code you are using? I suppose you started modifying things.
matlabuser12
on 20 May 2014
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.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)