a script that joints multiple sheets in an Excel workbook

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.

Answer 1

[Guillaume]

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

[alpedhuez]

Thank you. Let me work on it.