How to import Excel data in MATLAB in Mac
55 views (last 30 days)
Show older comments
I need to import Excel sheets that mainly contain dates and financial instrument prices. I have read that xlsread does not work on Mac and I would like to know if someone could explain why and if there are work arounds.
I have tried readtable but it stores the dates in numbers and also it gives me errors when trying to convert these into arrays. I have also tried converting the excel into CSV but that seems to work. I also get errors whe trying to apply MATLAB functions like yearfrac and datenum on manually imported data.
Is there any way to circumvent a manual import or installing a windows virtual machine?
Thank you
2 Comments
the cyclist
on 29 Apr 2020
I suggest that you post the Excel file, or a small representative sample, so that we can test some code on it rather than give you abstract answers.
Accepted Answer
Guillaume
on 29 Apr 2020
"I have read that xlsread does not work on Mac and I would like to know if someone could explain why and if there are work arounds."
This is not exactly true. xlsread works on a Mac if use the 'basic' option (which is on by default on Macs I believe). With the 'basic' option xlsread parses the excel file directly instead of communicating with excel to extract the data. However, with sufficiently complex excel files, xlsread may not parse the file correctly.
Even if you can use xlsread on a mac, you're still better off using readtable, readmatrix, and co. They're improvement over xlsread. By default in R2020a, readtable parses the excel file directly and works on a Mac. Just like with xlsread, there may be instances where this process fails but in general it shouldn't.
If you want to know the nitty-gritty, xlsread when not in basic mode, and readtable when 'UseExcel' is true, start Excel, ask Excel to open the file, then ask excel for the content of the spreadsheet it has read. This guarantees that the file is read correctly since it's excel doing it but the communication with excel is is only possible on Windows since the mechanism it uses (called COM or ActiveX) is only available on Windows. It would require cooperation between Apple and Microsoft to be implemented on Macs, I wouldn't hold my breath...
"I have tried readtable but it stores the dates in numbers"
That shouldn't be the case but if it happens you should be able to convert the numbers back to date using the 'ConvertFrom', 'excel' option of datetime.
"it gives me errors when trying to convert these into arrays"
You've done something wrong then. Without details of what you're doing nor the text of the error message, it's hard to help you.
" I have also tried converting the excel into CSV but that seems to work. I also get errors whe trying to apply MATLAB functions like yearfrac and datenum on manually imported data."
Again, not enough details about what you're doing or the errors.
"Is there any way to circumvent a manual import or installing a windows virtual machine?"
Use readtable, readmatrix, or readcell. In your case, I'd use readcell to read the whole spreadsheet in then read the required data out of the cell array. Note that the design of your spreadsheet is great for a human reader, but really not ideal for processing by a program.
allcontent = readcell(filename); %should work the same on a Mac as on windows
dates.settlement = allcontent{8, 5}; %should already be a datetime which is much better than a datenum. Don't convert to datenum!
dates.depos = cell2mat(allcontent(11:18, 4)); %already datetime
dates.futures = cell2mat(allcontent(12:20, 17:18)); %again nothing more to do
%etc. for the rest of the file. Use cell2mat to extract the relevant portion of the cell array
5 Comments
Guillaume
on 29 Apr 2020
There are many advantages to datetime over datenum, it covers a much larger range of dates, it takes into account timezones and leap seconds, you can change the display format without affecting the underlying stored date, maths with datetime are easier.
datetime was created because there were many problems with datenum. datetime is also easier to use. I strongly recommend you don't use datenum.
If you do insist to convert datetime into datenum, then you do not need to and mustn't specify a format:
asdatenum = datenum(datetimearray); %format can't be specified since datetime stores the true time regardless of the DISPLAY format.
"Later in the project I will need datenum"
Whatever you're doing with datenum, you can do with datetime, most likely more easily.
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!