Combine two excel files with different row and column lengths into one excel file
16 次查看(过去 30 天)
显示 更早的评论
Good day, everyone.
Attached are two excel files that I plan to combine the selected data into one excel file.
From Dummy_A, I only want: Day, Hour, Time. Basically, Dummy_A will serve as the base data.
From Dummy_B, I only want: Data_A, Data_B and Data_C. But the data must be based on their time in Dummy_B.
At the end, I expect my final excel file will be like Dummy_Example.
I tried to use xlsread and xlswrite but it can't be done because the dimension of array each excel file is not the same. I don't want to do manually copy paste from excel because attached are only one day data and I need to manage 365 days data. Hopefully can get some helps from the community. Thank you in advanced.
0 个评论
采纳的回答
Cris LaPierre
2022-2-14
编辑:Cris LaPierre
2022-2-14
Using readtable and outerjoin, I was able to create the table in MATLAB. Now you can just write it back to Excel using writetable. When joining tables, I find it easiest to use the Join Tables live task in a live script to figure out the correct settings. Once obtained, I just turned the task into editable code.
Here is what I came up with.
% Load the data
dataA = readtable("Dummy_A.xlsx");
dataA.TIME = timeofday(datetime(dataA.TIME,'ConvertFrom','datenum'));
dataA.TIME.Format = 'hh:mm'
dataB = readtable("Dummy_B.xlsx");
dataB.TIME = timeofday(datetime(dataB.TIME,'ConvertFrom','datenum','Format','HH:mm'))
% Merge the two tables
joinedData = outerjoin(dataA,dataB,"Type","left","Keys",["DAY","TIME"],...
"MergeKeys",true,"RightVariables",["Data_A","Data_B","Data_C"])
writetable(joinedData,'Dummy_C.xlsx')
Once comment. The NaN values don't get written to Excel. The one difference to point out, then, is that your example file does not contain values for Data_A while this example code does.
4 个评论
Cris LaPierre
2022-2-15
编辑:Cris LaPierre
2022-2-15
It looks like you should use the 'excel' option rather than 'datenum'. Currently those that look like duplicates are read in as 00:17:59 and 00:20:59, but the code only displays 'hh:mm' (it doesn't round when setting display format).
See below for how to update the code:
dataA = readtable("Dummy_A.xlsx");
dataA.TIME = timeofday(datetime(dataA.TIME,'ConvertFrom','excel'));
% visualize the table using the updated option
dataA(15:25,:)
更多回答(1 个)
KSSV
2022-2-14
T1 = readtable('Dummy_A.xlsx') ;
T2 = readtable('Dummy_B.xlsx') ;
T3 = readtable('Dummy_Example.xlsx') ;
DAY = T1.DAY ;
HOUR = T1.HOUR ;
TIME = T1.TIME ;
Data_A = interp1(T2.TIME,T2.Data_A,T1.TIME) ;
Data_B = interp1(T2.TIME,T2.Data_B,T1.TIME) ;
Data_C = interp1(T2.TIME,T2.Data_B,T1.TIME) ;
T = table(DAY,HOUR,TIME,Data_A,Data_B,Data_C) ;
writetable(T,'test.xlsx')
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!