Urlread and write to an Excel sheet

2 vues (au cours des 30 derniers jours)
charles atlas
charles atlas le 6 Oct 2014
I have the following code which reads the data off of this website http://www.aviationweather.gov/adds/metars/?station_ids=KMCO&std_trans=translated&chk_metars=on&hoursStr=past+18+hours&submitmet=Submit This website is basically the weather data from the last 18 hours around a certain location. What I do now is use urlread to read the data into “metarinfo”. What I want to do is write all of the data in "metarinfo" to an excel file. The problem is that it doesn’t seem to be working right. If I just go to the website manually and highlight all of the data on the web page, then paste it into excel, it looks all nice and neat. However, if I try to have Matlab do it, it doesn’t really work? Is there anyway to do this?
I can easily generate the text file, but the code won’t seem to write the data to Excel.
userDir1 = uigetdir('C:\','Browse to directory');
filePath = [userDir1,'\metarinfo_',datestr(now,30),'.txt'];
fid = fopen(filePath,'w');
If anyone knows how to do this, help is greatly appreciated

Réponse acceptée

Guillaume le 6 Oct 2014
When you pass a character array to xlswrite, it writes one character per cell. As your array is longer than the number of column allowed by Excel, it fails. To write a character array to a cell, first convert it into a cell array:
xlswrite('file1.xlsx', {metarinfo});
However, what you get with urlread and what is displayed by the browser that you then paste into excel are two very different thing. The first one, is the raw html of the page, the second is the output of that html after it's been processed by your web browser.
Therefore, if you want your metarinfo to display neatly in Excel, you will have to process it first in matlab.
Another option, is to use excel data import from web. This actually gives you an even nicer output. You could just bypass matlab and do it directly from excel, but if you want to do it from matlab:
url = 'http://www.aviationweather.gov/adds/metars/?station_ids=KMCO&std_trans=translated&chk_metars=on&hoursStr=past+18+hours&submitmet=Submit';
excel = actxserver('Excel.Application'); %start Excel COM server
wb = excel.Workbooks.Add; %create new workbook
r=wb.ActiveSheet.Range('$A$1'); %create destination range
qt = wb.ActiveSheet.QueryTables.Add(['URL;' url], r); %create data connection to web page
qt.Refresh; %run query to web page
wb.SaveAs(fullfile(pwd, 'file1.xlsx')); %save workbook
wb.Close; %close workbook
excel.Quit; %quit excel
delete(excel); %stop COM server
  3 commentaires
Guillaume le 8 Oct 2014
Hum, yes, I didn't get that error message when I wrote my answer because I wrote each line one at a time at the command line with a pause in between.
The reason you get that message is that qt.refresh returns as soon as the query is started, by default. Thus the query is still running when the call to wb.SaveAs is issued.
you can either set the BackgroundQuery of the query to false:
qt.BackgroundQuery = false;
before calling refresh, or disable background query in the refresh call:
As for the other error, most likely you already have a 'file1.xlsx' open by another program. That prevents excel from writing a file with the same name. Choose another filename or close the open file.
charles atlas
charles atlas le 8 Oct 2014
That worked. Thank you!

Connectez-vous pour commenter.

Plus de réponses (1)

Ken Atwell
Ken Atwell le 7 Oct 2014
Modifié(e) : Ken Atwell le 7 Oct 2014
websave, which is brand new as-of R2014b, might serve you better than urlread and fwrite/xlswrite.


Community Treasure Hunt

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

Start Hunting!

Translated by