How to extract the timestamp value and an integer value from an excel file?

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

Answer 1

[Jan]

Skip the trailing lines with the %*[^\n] specifier:

fid  = fopen('ACTUAL_testing1.csv');
Data = textscan(fid, '%s%f%*[^\n]', 'Headerlines', 1, 'Delimiter', ',');
fclose(fid);
vec  = datevec(C1{1}, 'mm/dd/yyyy HH:MM');

[Azzi Abdelmalek]

You mean

vec  = datevec(Data{1}, 'mm/dd/yyyy HH:MM')

[yashvin]

Data =

    {0x1 cell}    [0x1 double]

Error using dtstr2dtvecmx Failed on converting date string to date number.

Error in datevec (line 117) y = dtstr2dtvecmx(t,icu_dtformat);

[yashvin]

So why does for this one it does not work. It is the same data structure file with 2 additional columns.

fid  = fopen('actual_tgw2639true.csv');
Data = textscan(fid, '%s%f%f%f%*[^\n]', 'Headerlines', 1, 'Delimiter', ',');
fclose(fid);
Latitude=Data{3}';
Longitude=Data{4}';

Find attached the file.

Answer 2

[Azzi Abdelmalek]

[~,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

Answer 3

[Peter Perkins]

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.

[yashvin]

Thats awesome!! I faced this problem!! but when i specified the date format, solves everything! Thanks!!