Alternative to xlsread from website data on mac: matlab R2012a

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

[Leah]

Do you have the statistics toolbox? Also I couldn't recreate the error. I'm on 2010a windows OS.

[Simon]

Yes. I have the statistics toolbox.

Answer 1

[Ken Atwell]

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.

[Simon]

Looks like this will be the route I will be taking.

Thanks Ken!

Answer 2

[Leah]

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');

[Simon]

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]

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.

Answer 3

[Simon Woodward]

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.