清空筛选条件
清空筛选条件

Preserving dates when importing a XLSX file in matlab.

5 次查看（过去 30 天）
Tom Craven
Tom Craven 2018-9-17
评论： Walter Roberson 2018-9-19
I am wanting to import a table into Matlab which looks like the following
when I try using the readtable() function I get a result that looks like this
Is there a hacky way to preserve the date strings in the first column, I'm looking at doing some regression and lines of best fit on the data, and would like to preserve the date labels, and prevent the addition of extra columns with nan values.

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

回答（3 个）

KSSV
KSSV 2018-9-17
data = readtable(myfile);
data.Time = datetime(datevec(data.Date),'Format','dd/mm/yyyy');
  5 个评论
显示 3更早的评论
Walter Roberson
Walter Roberson 2018-9-17
You need to arrange to skip the first two rows of the xlsx because the extra header is confusing the parsing. This would be easier if you upgraded to at least R2016b.
Are you using MATLAB on Windows with Excel installed?
Tom Craven
Tom Craven 2018-9-17
Hi, there i'm using matlab on a mac so I have numbers as the excel equivalent.

请先登录，再进行评论。

Walter Roberson
Walter Roberson 2018-9-17
Okay on Mac or Linux to handle that file, xlsread the file and record the first output. Then use datetime() with 'convertfrom' excel on the first column to get the dates. If you still want table objects then grab the first line of the second output of xlsread, skip the first entry, and use the rest as the variableName property on array2table of columns 2 to end of the num data.
To emphasize: on Linux and mac, you will get a numeric first column which will be excel date format. This is not the same as you would get for Windows with Excel installed.
Peter Perkins
Peter Perkins 2018-9-19
I would think that in recent versions of MATLAB, all you need to do is add 'HeaderLines',1 to the original readtable call. What's happening in the original post is that it's trying to treat the first row as variable names, and seeing the row as the start of the data, all text. Skip that first row, treat the second as var names, and Bob's your uncle -- a table with one datetime variable and a buynch of numerics. In recent versions.
  2 个评论
Walter Roberson
Walter Roberson 2018-9-19
HeaderLines is only permitted for text files not excel if I recall correctly.
Walter Roberson
Walter Roberson 2018-9-19
I have confirmed you cannot use HeaderLines with .xlsx or .xls files.
You can use Range, but you have to already know how large the file is, or you have to be able to set a maximum size and then trim out the trailing rows with omitted data afterwards.
When you do specify a range, then the first row of the range is the row that becomes eligible for having the variable name.
Unless you have a new enough version and use detectImportOptions and adjust some of the settings, there is no way to handle the common pattern
name1 name2 name3
description1 description2 description3
data1_1 data2_1 data3_1
or
name1 name2 name3
units1 units2 units3
data1_1 data2_1 data3_1
detectImportOptions will generally properly detect that the data starts from line 3 in this situation, but it will not necessarily get the right data type for the columns, thinking it needs strings for some because it saw text in the row it is skipping.
The user's file follows this pattern: the XJO.ASX and so on are the variable names, and the ''ASX 200 Industrials' and so on is description.
detectImportOptions is not available to the user as they are using R2015a.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

产品


版本

R2015a

Community Treasure Hunt

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

Start Hunting!

Translated by