readtable does not allow 'Format' option

44 views (last 30 days)
Xiaoyu Xu
Xiaoyu Xu on 13 Jan 2018
Commented: 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!

Answers (2)

Jan
Jan on 13 Jan 2018
Edited: 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
Xiaoyu Xu
Xiaoyu Xu on 13 Jan 2018
Thanks! If I want to set one variable imported from the xlsx file to be yyyymmdd date format, how can I achieve that?
Jan
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.


Jeremy Hughes
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
Jeremy Hughes
Jeremy Hughes on 1 Jan 2019
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.
Walter Roberson
Walter Roberson on 1 Jan 2019
Sohrab Rafiq, which MATLAB version are you using? And are you importing on MS Windows with Excel installed?

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!