How to read an xls file in matlab which has latitude (suffixed with N), longitude (suffixed with E), time (prefixed with the month), and a value corresponding to latitude and longitude in it?

Question

Abhishek Chakraborty 2021-4-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/790614-how-to-read-an-xls-file-in-matlab-which-has-latitude-suffixed-with-n-longitude-suffixed-with-e

评论： Abhishek Chakraborty 2021-4-8

The xls file looks like this... The first column is the longitude. I want to extract only the number from it. For example, if the array has a value of "84.375E", I want "84.375" in my corresponding array's value and so on. For the column named "time", I want to similarly extract the number (year). For example, if the cell is "Jun-Sep 1990", I want my corresponding array to have a value of "1990".

I tried to load the values myself. I tried this code:

T = readtable('Myxlsx.xlsx',2);

But I got this error:

Error using readtable (line 245)
All parameters must have an associated value.

Then I tried the xlsread command like this:

T = xlsread('Myxlsx.xlsx');

But then only the values from the fourth column got loaded but nothing from the first three columns appeared.

2 个评论
显示无隐藏无

Anmol Dhiman 2021-4-8

Hi Abhishek,

Can you share your file for better debugging

Abhishek Chakraborty 2021-4-8

Hi Anmol,

I have found the solution of this problem through another similar question I asked here. The link for the solution is :

https://in.mathworks.com/matlabcentral/answers/791464-how-to-convert-cell-array-containing-text-to-numbers?s_tid=srchtitle

请先登录，再进行评论。

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

Answer 1

Monika Jaskolka 2021-4-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/790614-how-to-read-an-xls-file-in-matlab-which-has-latitude-suffixed-with-n-longitude-suffixed-with-e#answer_670267

编辑：Monika Jaskolka 2021-4-8

You can use regexprep to clean up the values:

opts = detectImportOptions('Test.xlsx', 'NumHeaderLines', 2, 'VariableNamesRange', 'A1');
T = readtable('Myxlsx.xlsx', opts);	
T.Longitude = regexprep(T.Longitude, 'E$', ''); % Remove last E
T.Latitude = regexprep(T.Latitude, 'N$', ''); % Remove last N
T =
  4×4 table
    Longitude    Latitude           Time           Value  
    _________    _________    ________________    ________
    '84.375'     '8.57130'    'June-Sept 1990'    9.58e-06
    '86.25'      '8.57130'    'June-Sept 1990'    3.14e-05
    '88.125'     '8.57130'    'June-Sept 1990'    7.11e-05
    '90'         '8.57130'    'June-Sept 1990'    7.25e-05