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

Question

yashvin 2015-6-23

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/224894-how-to-extract-the-timestamp-value-and-an-integer-value-from-an-excel-file

评论： yashvin 2015-6-24

ACTUAL_testing1.csv

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Jan 2015-6-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/224894-how-to-extract-the-timestamp-value-and-an-integer-value-from-an-excel-file#answer_183630

编辑：Jan 2015-6-23

在 MATLAB Online 中打开

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');

3 个评论
显示 1更早的评论隐藏 1更早的评论

yashvin 2015-6-23

在 MATLAB Online 中打开

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 2015-6-23

在 MATLAB Online 中打开

actual_tgw2639true.csv

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 2015-6-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/224894-how-to-extract-the-timestamp-value-and-an-integer-value-from-an-excel-file#answer_183632

编辑：Azzi Abdelmalek 2015-6-23

在 MATLAB Online 中打开

[~,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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

Peter Perkins 2015-6-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/224894-how-to-extract-the-timestamp-value-and-an-integer-value-from-an-excel-file#answer_183684

在 MATLAB Online 中打开

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.