MATLAB Answers

0

readtable does not allow 'Format' option

Asked by Xiaoyu Xu on 13 Jan 2018
Latest activity Commented on by Walter Roberson
on 1 Jan 2019
I was trying to import a spreadsheet with specified variable formats. The code I used was:
Cfile = 'D:\mypath\controls.xlsx';
formatSpec = '%C%f%{yyyymmdd}D%f%f%f';
C = readtable(Cfile,'Format',formatSpec);
However, I got the following error:
Error using readtable (line 198)
Invalid parameter name: Format.
What's wrong with my code? Thanks!

  0 Comments

Sign in to comment.

2 Answers

Answer by Jan
on 13 Jan 2018
Edited by Jan
on 13 Jan 2018

If you import an XLSX file the format is specified by the contents of the Excel file. The format specification works for text files only.
See:
help readtable
and read the section about ".xls, .xlsx, .xlsb, .xlsm, .xltm, .xltx, .ods: Spreadsheet file." There is no 'Format' option for this file type.

  2 Comments

Thanks! If I want to set one variable imported from the xlsx file to be yyyymmdd date format, how can I achieve that?
Jan
on 13 Jan 2018
What exactly is "one variable"? Did you import the data to different variables? What does "yyyymmdd date format" exactly mean? Is this a string or a numerical value?

Sign in to comment.


Answer by Jeremy Hughes on 15 Jan 2018

Hi Xiaoyu,
If you're using R2016b or later, you can specify details like this with spreadsheet import options.
opts = detectImportOptions(filename)
opts = setvartype(opts,'MyDateVar','Datetime')
opts = setvaropts(opts,'MyDateVar','DatetimeFormat','yyyyMMdd');
T = readtable(filename,opts)
However, if the dates are being imported already, you could just modify the format on the MATLAB side.
T.MyDateVar.Format = 'yyyyMMdd';
The format controls how the datetime is displayed, but not what data is in the array.
Goodluck,
Jeremy

  3 Comments

Hi,
based on your suggestion, I am trying the following. But it doesn't seem to work. The dates remain unchanged in 'mmddyy' formatting.
Data = readtable('yield.xlsx')
Date.MyDateVar.Format = 'ddmmyy';
any ideas?
Raf
One issue, you might be having is that MATLAB datetime format 'ddmmyy' means "day number", "minutes", "two-digit year". See: https://www.mathworks.com/help/matlab/ref/datetime.html#d120e223080
What happens when you set:
Date.MyDateVar.Format = 'ddMMyy';
You may need to upload an example file, as I get expected results when I try this on my end.
Sohrab Rafiq, which MATLAB version are you using? And are you importing on MS Windows with Excel installed?

Sign in to comment.