Alternative to xlsread from website data on mac: matlab R2012a

5 views (last 30 days)
Simon
Simon on 17 Jul 2012
Answered: Simon Woodward on 23 Sep 2014
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:
urlwrite('http://transmission.bpa.gov/business/operations/wind/ReservesDeployedYTD_2012.xls','BPA_BRD_data.xls');
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.
Simon
  2 Comments

Sign in to comment.

Accepted Answer

Ken Atwell
Ken Atwell on 18 Jul 2012
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 Comment
Simon
Simon on 18 Jul 2012
Looks like this will be the route I will be taking.
Thanks Ken!

Sign in to comment.

More Answers (2)

Leah
Leah on 17 Jul 2012
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)
data=dataset('xlsfile','BPA_BRD_data.xls','range','A16:D52420');
  2 Comments
Leah
Leah on 17 Jul 2012
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.

Sign in to comment.


Simon Woodward
Simon Woodward on 23 Sep 2014
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.

Community Treasure Hunt

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

Start Hunting!

Translated by