Urlread and write to an Excel sheet
5 views (last 30 days)
Show older comments
charles atlas on 6 Oct 2014
Edited: Walter Roberson on 31 Oct 2016
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
Guillaume on 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:
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
More Answers (1)
Ken Atwell on 7 Oct 2014
Edited: Ken Atwell on 7 Oct 2014
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!