Alternative to xlsread from website data on mac: matlab R2012a

조회 수: 3 (최근 30일)
Simon . 2012년 7월 17일
답변: Simon Woodward . 2014년 9월 23일
I am operating Matlab version R2012a on mac OS.
I am downloading data from a publicly accessible website that comes in the form of a .xls file. This file is updated regularly and contains a text header, and then date/time with corresponding data measurements.
My code:
I then wish to manipulate the .xls file in matlab so I read it in to the workspace:
My code:
[num,txt,raw] = xlsread('BPA_BRD_data');
I get the following command window output:
Warning: XLS File contains unrecognized string header - skipping remaining text - (1041:0x20 65532).
It seems that at line 424 in the excel file, the date string causes an error, and xlsread stops reading the date strings. The remaining strings come out as NaN in the 'raw' parameter.
When I simply copy and paste the date strings into a matlab cell array from excel, no issues arise. The problem is that I need to input this time data into matlab directly from the website, and I need the script to work on both mac and window operating systems.
Is there a way to manually read in values from a .xls spreadsheet without using xlsread, or is it possible to convert a .xls file in script to another form (say .csv) without using actxserver? Any other ideas?
Thanks for your help.
  댓글 수: 2
Simon 2012년 7월 17일
Yes. I have the statistics toolbox.

댓글을 달려면 로그인하십시오.

채택된 답변

Ken Atwell
Ken Atwell 2012년 7월 18일
I see this on my Mac. You are in a tough spot. Renaming the file won't changing its encoding (it would be "mv" and not "move" on a Mac, BTW), and you've already discovered that dataset is using xlsread under the hood. XLS is a binary file format, nearly impossible to massage with MATLAB or your Mac's Unix command-line tools.
I would try the following:
  1. Open the XLS file in Excel (you have this on your Mac, I hope) and resave it in the newer XLSX file format.
  2. See if the XLSX file opens in MATLAB. If it does (yeah!), write a short Applescript to automate step 1. I'm not an Applescript user, so I can't give you specific guidance on how to this do, but I believe it is possible. Invoke this script after the URLWRITE, but before XLSREAD.
  3. If the XLSX file still refuses to open (unlikely, but possible), use trial and error to figure out the specific cells that give MATLAB trouble and delete/replace these. It sounds like you have a good sense of where the problem lies already. Then write, an Applescript that automates this.
Google "AppleScript Excel" for pointers on creating the automation routines.
  댓글 수: 1
Simon 2012년 7월 18일
Looks like this will be the route I will be taking.
Thanks Ken!

댓글을 달려면 로그인하십시오.

추가 답변 (2개)

Leah 2012년 7월 17일
To change the file type you can use a dos command
dos('move BPA_BRD_data.xls BPA_BRD_data.csv')
I prefer to bring my excel files in as datasets. Might avoid the error and they are easier to work with inside of matlab. (this requires stats toolbox)
  댓글 수: 2
Leah 2012년 7월 17일
Sorry I guess I'm not much help with macs :(. I think you might have to do an actxserver. You could use it to delete the first 15 rows of the file. Hopefully the don't change their format often.

댓글을 달려면 로그인하십시오.

Simon Woodward
Simon Woodward 2014년 9월 23일
I am having a very similar problem under windows. Matlab 2009b, Excel 2010 Professional. Using xlsread 'basic' mode, some of the text in the worksheet get read in as NaNs.


Help CenterFile Exchange에서 Data Import from MATLAB에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by