Reading all values as a string from excel file

42 次查看（过去 30 天）
Sarah R
Sarah R 2022-1-24
评论： Voss 2022-1-24
I have an excel file that includes, strings, numbers and time in the format "01:59.00"
When I use xlsread in Matlab to read in the sheet I am currently using [~, ~, alldata] = xlsread... however, my time variables are getting converted to decimals.
Is there any way I am able to keep the time variable or even read it as a string?
Thank you in advance
  1 个评论
Stephen23
Stephen23 2022-1-24
编辑：Stephen23 2022-1-24
"my time variables are getting converted to decimals."
Nothing is "getting converted", in fact Excel stores dates as serial date numbers:
https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252
Ultimately all data stored on an Excel worksheet are either text or numeric: dates are numeric, so XLSREAD is giving you the actual data saved in the workbook. Formatting in Excel that interprets that serial date number and displays it in particular ways is purely an artifact of your OS's locale settings or the cell formatting.

请先登录，再进行评论。

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

回答（2 个）

Voss
Voss 2022-1-24
Ran in:
Maybe you can just convert the decimal times to datetimes using the datetime() function, after reading them in.
now()
ans = 7.3855e+05
datetime(now(),'ConvertFrom','datenum')
ans = datetime
24-Jan-2022 01:24:14
  2 个评论
Stephen23
Stephen23 2022-1-24
编辑：Stephen23 2022-1-24
How does this account for the different epochs used by MATLAB's and Excel's serial date numbers ?

请先登录，再进行评论。

Stephen23
Stephen23 2022-1-24
Ran in:
In Excel dates are stored as serial date numbers, so what you are getting is the raw data.
The best solution is to use READMATRIX or READCELL which will (either automagically or with some hints from you) import Excel's serial date numbers as DATETIME objects.
The next option, if you insist on using outdated XLSREAD, is to do that conversion yourself. There are many threads on this forum showing how, the simplest approach is to use DATETIME which has this conversion built in:
N = 39448.25 % Excel serial date number
N = 3.9448e+04
D = datetime(N,'convertFrom','excel')
D = datetime
01-Jan-2008 06:00:00

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by