Alternative to xlsread from website data on mac: matlab R2012a

1 次查看（过去 30 天）
Simon
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:
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 个评论
Leah
Leah 2012-7-17
编辑：Leah 2012-7-17
Do you have the statistics toolbox? Also I couldn't recreate the error. I'm on 2010a windows OS.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

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.

更多回答（2 个）

Leah
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)
data=dataset('xlsfile','BPA_BRD_data.xls','range','A16:D52420');
  2 个评论
Simon
Simon 2012-7-17
I tried your first suggestion and got the following output:
-----------------------------------------------
dos('move BPA_BRD_data.xls BPA_BRD_data.csv')
/bin/bash: move: command not found
ans =
127
---------------------------------------
For the second suggestion, I think that I am constrained to 'basic' mode for xlsread because I am using a mac operating system. The documentation states that 'basic' is the default mode for systems without excel for windows.
Anyways, this is what I get:
-------------------------------------
data=dataset('xlsfile','BPA_BRD_data.xls','range','A16:D52420');
Warning: Range cannot be used in 'basic' mode. The entire sheet will be loaded.
In xlsread at 199
In dataset.readXLSFile at 14
In dataset.dataset>dataset.dataset at 344
Warning: XLS File contains unrecognized string header - skipping remaining text - (1041:0x20 65532).
Error using setvarnames (line 28) NEWNAMES must be a nonempty string or a cell array of nonempty strings.
Error in dataset/readXLSFile (line 49) a = setvarnames(a,varnames(:)',[],true,true);
Error in dataset (line 344) a = readXLSFile(a,xlsfileArg,otherArgs);
------------------------
Am I able to switch the default mode?
Leah
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 Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by