How to extract the timestamp value and an integer value from an excel file?
4 views (last 30 days)
Show older comments
Hi
I am trying to extract the timestamp,TIME and the integer values,PFL found in an excel and store in an array. Please check my code. I am getting lots of empty commas.
fid = fopen('ACTUAL_testing1.csv');
C1 = textscan( fid,'%s%f','Headerlines',1,'Delimiter',',');
vec = datevec( C1{1}, 'mm/dd/yyyy HH:MM' );
Find attached the excel sheet
0 Comments
Answers (3)
Azzi Abdelmalek
on 23 Jun 2015
Edited: Azzi Abdelmalek
on 23 Jun 2015
[~,b]=xlsread('actual_tgw2639true.csv')
M=b(2:end,:)
a=regexp(M,',','split');
date=cellfun(@(x) x{1},regexp(M,',','split'),'un',0);
idx=cellfun(@isempty,date);
date(idx)=[];
a(idx)=[];
data=cell2mat(cellfun(@(x) str2double(x(2:end)),a,'un',0));
date
data
0 Comments
Peter Perkins
on 23 Jun 2015
If you're using R2014b or later, you can read dates and numbers into a table:
>> readtable('ACTUAL_testing1.csv','Format','%D%f%*s%*s%*s%*s%*s')
Warning: The DATETIME data was created using format 'MM/dd/uuuu HH:mm' but also matched 'dd/MM/uuuu HH:mm'.
To avoid ambiguity, use a format string. e.g. '%{MM/dd/uuuu HH:mm}D'
> In table/readTextFile>textscanReadData (line 365)
In table/readTextFile (line 175)
In table.readFromFile (line 36)
In readtable (line 135)
ans =
Time PFL
________________ ___
05/02/2015 18:57 350
05/02/2015 19:28 350
05/02/2015 19:40 350
...
Note the warning, because 05/02/2015 could be May or Feb. It chose May for me because I'm in the US. If you had at least one date like 05/14/2015 in your file, you would not get any warnings. You can read the file without specifying a format, but afterwards you'd have to convert string->datetime and clean up those null columns.
See Also
Categories
Find more on Dates and Time 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!