readtable does not allow 'Format' option

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!

Answer 1

[Jan]

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.

[Xiaoyu Xu]

Thanks! If I want to set one variable imported from the xlsx file to be yyyymmdd date format, how can I achieve that?

[Jan]

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?

Answer 2

[Jeremy Hughes]

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

[Sohrab Rafiq]

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

[Jeremy Hughes]

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]

Sohrab Rafiq, which MATLAB version are you using? And are you importing on MS Windows with Excel installed?