download excel from web, webread doesn't work

Hi there,

I am trying to download university of michigan survey data from website https://data.sca.isr.umich.edu/tables.php, especially the data I want to get is

https://data.sca.isr.umich.edu/get-table.php?c=RB&y=2019&m=6&n=35&f=xls&k=fb5221ba39d97ad50de7f2bd48abc946

And it looks like

I tried the following Matlab code:

url='https://data.sca.isr.umich.edu/get-table.php?c=RB&y=2019&m=6&n=35&f=xls&k=fb5221ba39d97ad50de7f2bd48abc946';
options = weboptions('ContentType','table');
webread(url,options);

But only got some random stuff:

What should I do? Thank you so much for your help

[dpb]

I've never messed with this stuff, but don't you want to webread, not webwrite?

[RONG FU]

Hi dpb, sorry I did use webread copied the wrong code...I also tried other options but also with this mess...

Answer 1

[dpb]

Ah! That's actually a link to an Excel spreadsheet file, not web service data. Use

websave('UMichSurvey.xls',url)                                  % save spreadsheet locally
opt=detectImportOptions('UMichSurvey.xls');                     % figure out how it's structured
t=readtable('UMichSurvey.xls',opt);                             % read it in...
t.Properties.VariableNames(1:2)={'Month','Year'};               % fix up missing variable name/bad label
t.Month=categorical(t.Month,cellstr(datetime(2019,1:12,1),'MMMM').');  % convert month strings to categorical
t.Date=datetime(t.Year,double(t.Month),1,'Format','MMM-uuuu');  % create datetime date variable
t=[t(:,end) t(:,1:end-1)];                                      % rearrange column order

Above leaves you with...

>> t(1:10,:)
ans =
  10×9 table
      Date       Month      Year    GoodTime    Uncertain    BadTime    Total    Relative    Cases
    ________    ________    ____    ________    _________    _______    _____    ________    _____
    Feb-1951    February    1951    33          18           49         100       84         1925 
    May-1951    May         1951    22          25           53         100       69          999 
    Nov-1951    November    1951    27          31           42         100       85          957 
    Feb-1952    February    1952    23          26           51         100       77         2820 
    May-1952    May         1952    33          26           41         100       92          929 
    Nov-1952    November    1952    26          34           40         100       86         1714 
    Feb-1953    February    1953    34          28           38         100       96         3097 
    Nov-1953    November    1953    36          26           38         100       98         1023 
    May-1954    May         1954    43          24           33         100      110         1365 
    Nov-1954    November    1954    45          27           28         100      117         1139 
>> 

to show first 10 records...

[RONG FU]

Thank you so much! It works perfectly!

[dpb]

Glad to help on a hot Sunday afternoon... :)

First time I've tried this...works more better than would have thunk...I may have to try more of this with some other data I've always just manually downloaded. Could be big help.