Preserving dates when importing a XLSX file in matlab.

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.

Answer 1

[KSSV]

data = readtable(myfile);
data.Time = datetime(datevec(data.Date),'Format','dd/mm/yyyy');

[Tom Craven]

Hi there KSSV, thank you for helping

when I run the first line of code I get this warning

I think that just means it renames in the first column to Var1 like the above screenshot does it not?

when I then try to run the modified code data.Time = datetime(datevec(data.Var1),'Format','dd/mm/yyyy');

I get the following error message

what could I be doing wrong in this case?.

[KSSV]

Check the exam format which you need and which the data has.

[Tom Craven]

the dates are in the form like 9/26/2000 say, would that be like mm/dd/yyyy say ? when I try that option though I get the error message "failed to look up the month of year".

[Walter Roberson]

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]

Hi, there i'm using matlab on a mac so I have numbers as the excel equivalent.

Answer 2

[Walter Roberson]

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.

Answer 3

[Peter Perkins]

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.

[Walter Roberson]

HeaderLines is only permitted for text files not excel if I recall correctly.

[Walter Roberson]

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.