Alternative to xlsread from website data on mac: matlab R2012a
9 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Simon
am 17 Jul. 2012
Beantwortet: Simon Woodward
am 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 Kommentare
Akzeptierte Antwort
Ken Atwell
am 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:
- Open the XLS file in Excel (you have this on your Mac, I hope) and resave it in the newer XLSX file format.
- 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.
- 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.
Weitere Antworten (2)
Leah
am 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 Kommentare
Leah
am 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.
Simon Woodward
am 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.
0 Kommentare
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!