How to import Excel data in MATLAB in Mac
조회 수: 28 (최근 30일)
이전 댓글 표시
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
the cyclist
2020년 4월 29일
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.
채택된 답변
Guillaume
2020년 4월 29일
"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
Guillaume
2020년 4월 29일
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.
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Calendar에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!