match two excel files by date and time
2 views (last 30 days)
Show older comments
I have two files K_ship.xlsx (with data collected nearly every min) and K_ETR.xlsx (data collected maybe every 40 min). Both are attached.
I want to match the two files by data and time (in 2 columns, not combined) so that I can get 3 parameters from the larger file K_ship.xlsx that correspond to K_ETR.xlsx
I have written a script (also attached) used readtable and tried to subtract the two dates and the two times but I am a complete novice and I cant get the date format right.
I had posted a very messy post yesterday which I deleted and now I am only attaching my script and two data files and hopefully somone will help me!
Any other suggestions as to how to accomplish this are very welcome as i am here to learn!
0 Comments
Accepted Answer
Tanmay Das
on 19 Jun 2020
Hi,
I have the understanding that you know how to import tables from excel file and you are facing problem in merging two tables based on date and time variables. The following code may be useful for your understanding:
A = outerjoin(A,B,'Type','Right','Keys',{'date','time'},'MergeKeys',true);
where A is the table imported from K_ship.xlsx and B is the table imported from K_ETR.xlsx.
Basically, outerjoin function with 'Type' as 'Right’, right joins two tables keeping all the values in the right table and removing those values in the left table whose key values do not match with the key values of right table. You can also specify the variables that you want to keep in left table by using 'LeftVariables’ Name-Value pair argument.
Hope that helps!
More Answers (0)
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!