download excel from web, webread doesn't work
조회 수: 9 (최근 30일)
이전 댓글 표시
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
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
댓글 수: 2
채택된 답변
dpb
2019년 8월 11일
편집: dpb
2019년 8월 11일
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...
댓글 수: 2
dpb
2019년 8월 11일
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.
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Standard File Formats에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!