a script that joints multiple sheets in an Excel workbook

2 views (last 30 days)
I have an Excel workbook with, let us say, 40 worksheets. Every worksheet has a format of
Date Sheet1Data
5/1/2019 10
Date Sheet2Data
5/1/2019 20
Then I want to have a script to outerjoin all the worksheets automatically. The output will be like
Date Sheet1Data Sheet2Data
5/1/2018 10 20
Please advise.

Answers (1)

Guillaume
Guillaume on 24 May 2019
readtable (or readtimetable) the first two sheets, outerjoin them. Then readtable (or readtimetable) the remaining sheets one by one in a loop, outerjoin'ing them to the previous result.
file = 'C:\somewhere\somefile.xlsx';
numsheets = 40;
result = outerjoin(readtable(file, 'Sheet', 1), readtable(file, 'Sheet', 2), 'Keys', 1, 'MergeKeys', true);
for sheet = 3:numsheets
result = outerjoin(result, readtable(file, 'Sheet', sheet), 'Keys', 1, 'MergeKeys', true);
end

Tags

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!