Collecting and rearranging excel data
8 次查看(过去 30 天)
显示 更早的评论
Hi
I have a bit of a nightmare of collecting and rearranging some excel data. The excel data is spread into different worksheets, but have the same numbers of coloums but not the same number of rows.
I need to identify a persons name, and pluck the row of data from each worksheet in the excel file, and combine them into a table arranged by the date, that I then can write into a new excel file.
My problem is that the names are not always to be found in the worksheet, because it shows if they were there or not.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/284957/image.png)
The player names that needs to be made a excel data file for:
Spillere = ({'Mikkel Ibsen ';'Rasmus Skram ';'Jacob Bjarkam ';'Kristoffer Bendixen ';...
'Rune Ott ';'Søren Bjørn Nedergaard ';'Rasmus Hansen ';'Anders Thode ';'Malthe Lund Mortensen ';'Martin Winther Larsen ';'Kresten Mosbæk Gravesen ';...
'Tonny Jensen ';'Thomas Gøtke ';'Mikkel Skov Christensen ';'Magnus Leth Nielsen ';'Kasper Ørkild ';'Anders Fugmann ';'Andreas Laursen ';'Thomas Gaardsøe '});
Spillere = strtrim(Spillere);
If you need to remove Æ, Ø and Å which is danish Ive done it with this:
for h = 1:size(Spillere,1)
Spillere_2{h,1}= Spillere{h,1}(~isspace(Spillere{h,1}));
Spillere_2 = strrep(Spillere_2,'ø','oe');
Spillere_2 = strrep(Spillere_2,'Ø','oe');
Spillere_2 = strrep(Spillere_2,'æ','ae');
Spillere_2 = strrep(Spillere_2,'å','aa');
end
Spillere = strtrim(Spillere);
Hope someone can help me.
Best Regards Mikkel
回答(1 个)
darova
2020-4-18
Try this
clc,clear
A = []
[s1,info1] = xlsfinfo('Season 2017.xls');
for i = 1:numel(info1)
A1 = readtable('Season 2017.xls','sheet',i);
ix = ~cellfun(@isempty,table2array(A1(:,1)));
A = [A;A1(ix,1:26)];
end
B = sortrows(A);
Do you know why MATLAB imports this trash sometimes?
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/285639/image.png)
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!