Import of mixed string and numerical data from excel file

Question

Saeid 2016-11-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/310297-import-of-mixed-string-and-numerical-data-from-excel-file

回答： Mostafa 2016-11-2

I am currently trying to import data from an Excel file. The data typically looks like this:

So far I have been dealing with only numerical tables with a limited number of arrays, but now I need to find certain data in large tables and prefer to automate the process a bit. In this regard I have a few problems with the import from Excel: 1. How can I read the column titled "Date & Time" as a string and convert its values into a numerical values? I already know that MATLAB can handle specific date and time formats and this seems to be outside of that format. 2. I want MATLAB to identify each column with the title stated in the first line (power, T, rpm etc.) so it can e.g. always know where the "force" data is stored and treat it accordingly? 3. How can I tell MATLAB to recognize the command * "Start Here"* in location (A11) so that I can e.g. start a certain calculation from this point on?

Thanks Saeid

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Mostafa 2016-11-2

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/310297-import-of-mixed-string-and-numerical-data-from-excel-file#answer_241720

在 MATLAB Online 中打开

I believe that the easiest (not the fastest) way is to import all the data at once in matlab environment then process it there.

DataSet = xlsread(fileName);

Analyze the first row of the imported data set: Search for certain strings, assign variables to the indicies of each data column (Power, T, rpm, force,..), and use these variables to deal with the rest of the data set.

IdxDate = find(cellfun(@(X) strcmp(X,'Date & Time'), DataSet(1,:)));
IdxPower = find(cellfun(@(X) strcmp(X, 'power'), DataSet(1,:)));
IdxForce = find(cellfun(@(X) strcmp(X, 'force'), DataSet(1,:)));
...

Analyze the first column of the imported data set: Search for the string "Start here" and start accessing the data set from this point.

IdxStart = find(cellfun(@(X) strcmp(X,'Start Here'), DataSet(:,1)));
%%Data of concern inside the power column
DataPower = DataSet(IdxStart:end, IdxPower);
%%Data of concern inside the force column
DataForce = DataSet(IdxStart:end, IdxForce);
%%Simple calculation
SomeValue = cellfun(@(X,Y) str2double(X)*str2double(Y), DataForce, DataPower);
%%Or if the data is parsed as numbers and not strings: 
%%SomeValue = cellfun(@(X,Y) X*Y, DataForce, DataPower);
...