Urlread and write to an Excel sheet

All,

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.

metarinfo=urlread(['http://www.aviationweather.gov/adds/metars/?station_ids=KMCO&std_trans=translated&chk_metars=on&hoursStr=past+18+hours&submitmet=Submit']);
userDir1 = uigetdir('C:\','Browse to directory');
filePath = [userDir1,'\metarinfo_',datestr(now,30),'.txt'];
fid = fopen(filePath,'w');
xlswrite('file1.xlsx',metarinfo);

If anyone knows how to do this, help is greatly appreciated

Charles

Answer 1

[Guillaume]

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

[charles atlas]

Guillaume, For some reason when I ran the code, I got: "This action will cancel a pending refresh data Command. Continue? I clicked "ok" and the Excel sheet was populated with only one cell saying "ExternalData_1" I kept trying to run the code make a few minor adjustments. Finally I ran the code and it worked but I am not sure what I did. I deleted the file which was populated with the correct data and then tried to run it again. Same error, same data populated in the first cell of "file_1". Do you know what the issue could be?

Also in the command window, I got the following error:

??? Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: Cannot access 'file1.xlsx'.

Help File: xlmain11.chm

Help Context ID: 0

[Guillaume]

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:

qt.Refresh(false);

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]

That worked. Thank you!

Answer 2

[Ken Atwell]

websave, which is brand new as-of R2014b, might serve you better than urlread and fwrite/xlswrite.