MATLAB Answers

match two excel files by date and time

4 views (last 30 days)
Helga Gomes
Helga Gomes on 18 Jun 2020
Commented: Helga Gomes on 20 Jun 2020
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!

Accepted Answer

Tanmay Das
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.
You may refer to Outerjoin documentation for further information.
Hope that helps!
  1 Comment
Helga Gomes
Helga Gomes on 20 Jun 2020
Thank you very much, Tanmay, for this extremely valuable tip! I could use 'outerjoin' in so many of my data proceesing tasks! The only question I have is can i give a window for the time stamp to match - i.e. maybe match within 10 minutes? Thanks a lot!

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!