이 질문을 팔로우합니다.
- 팔로우하는 게시물 피드에서 업데이트를 확인할 수 있습니다.
- 정보 수신 기본 설정에 따라 이메일을 받을 수 있습니다.
Read excel data but keep formatting? Is this possible?
조회 수: 8 (최근 30일)
이전 댓글 표시
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?
답변 (2개)
Sara
2014년 5월 6일
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
matlabuser12
2014년 5월 6일
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
2014년 5월 6일
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
2014년 5월 6일
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
2014년 5월 6일
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
2014년 5월 6일
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
2014년 5월 6일
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
2014년 5월 7일
편집: matlabuser12
2014년 5월 7일
I tried that code but received the following error:
Error using Textscan Invalid field identifier. Use fopen to generate a valid field identifier
Sara
2014년 5월 7일
I get the answer I think you wanted...can you attach your csv so I can check what's different?
Sara
2014년 5월 7일
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
2014년 5월 8일
Still getting an error:
Cell contents reference from non-cell array object
Error in findmyentries line21
if(isempty(var(1))),break,end
Sara
2014년 5월 8일
I'm attaching the file so that I don't forget anything this time...modify as it pleases you.
matlabuser12
2014년 5월 8일
편집: matlabuser12
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 8일
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
2014년 5월 20일
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
2014년 5월 20일
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
Sara
2014년 5월 20일
Can you attach a draft of the code you are using? I suppose you started modifying things.
matlabuser12
2014년 5월 20일
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.
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!오류 발생
페이지가 변경되었기 때문에 동작을 완료할 수 없습니다. 업데이트된 상태를 보려면 페이지를 다시 불러오십시오.
웹사이트 선택
번역된 콘텐츠를 보고 지역별 이벤트와 혜택을 살펴보려면 웹사이트를 선택하십시오. 현재 계신 지역에 따라 다음 웹사이트를 권장합니다:
또한 다음 목록에서 웹사이트를 선택하실 수도 있습니다.
사이트 성능 최적화 방법
최고의 사이트 성능을 위해 중국 사이트(중국어 또는 영어)를 선택하십시오. 현재 계신 지역에서는 다른 국가의 MathWorks 사이트 방문이 최적화되지 않았습니다.
미주
- América Latina (Español)
- Canada (English)
- United States (English)
유럽
- 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)
아시아 태평양
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)