Import files into Matlab and compare values

So I have a spreadsheet with lots of data on it, and I want to compare the dates and times (of experiments) on this document to the dates and times of files produced by the experiments. I use:

dir('filepath');

to obtain the struct with the info of the files and I have imported the spreadsheet. The issues arise here:

• The dates and times of the spreadsheet are in separate columns, and I can't import the times as times (they are imported as numbers), is there a way of combining these values or am I going about it in the wrong way?
• I can't extract the entire individual field from the struct which has the datetime info, and the field is in string format. Is there a way of converting this to datetime?
• How would I compare the info?

The spreadsheet contains info from all experiments and the file contains files produced by a smaller number of recent experiments (if that helps).

Please let me know if my (theoretical) procedure is correct or not as I can't find anything online suggesting you can do the above. I can't attach any files unfortunately. Apologies if anything is unclear please let me know if I can expand more.

[KSSV]

Attach a file so that we cann see how your data is.

[Dean Kennedy]

Here are the files being used in my workspace. ShotTime and ShotDate are the times and dates from the big spreadsheet, and Files is the file info from using dir. Filetimes is what happens when I have tried to get the date field using

FileTimes = Files.date;

Answer 1

[Guillaume]

Your question is not really clear. Does it matter that your data is in a spreadsheet? As far as I can tell, at the moment you're only looking at the output of dir. You're not looking at the content of the files.

It looks to me that the problem is that you don't know how to work with non-scalar structures efficiently. Unfortunately, matlab's doc is not very well organised on this topic. Here and, particularly for this context, here are two relevant pages.

To convert the date from the dir structure to datetime:

FileTimes = datetime({Files.date}).';  %Use {} to convert the comma-separated list returned by Files.Date into a cell array

What I don't really understand is why you actually care about that information. It's the modification time of the files, not the creation time, so does not really reflect the date of your experiment. Relying on file modification time is very iffy.

How would I compare the info

What is the definition of compare in this context. I would think (hope) you don't mean exactly equal as that would mean that the two datetimes you compare would have to be the same down to the fractions of second, which is very unlikely.

[Dean Kennedy]

I didn't think it was clear which is my point. The output of dir gives me the creation time of those files as they haven't been modified.

When I use any form of indexing on the struct it only gives me the top result and not the entire list.

By compare, I have a list of all experiments performed (the big lists of dates and times which are separate which I can't combine to then compare, which is why I was asking whether that's the correct method) and I need to search for the 'recent group' of experiments performed within that list and make sure the two data sets match up. The times may not match up exactly but I can use conditional statements for that. The main issue is manipulating the data.

[Guillaume]

gives me the creation time of those files as they haven't been modified

It may work in your case, but I'd recommend writing a big warning in your code that it is very fragile. Modification time can obviously change after the file creation and there are some OS options that can even make it meaningless. Note that you could get the actual creation time by delegating to Java, or .Net on Windows. It's unfortunate that dir does not give you the creation time. Even relying on creation can be iffy (if the files are copied onto another drive, the creation time will be the copy time).

When I use any form of indexing on the struct it only gives me the top result and not the entire list.

As I answered:

FileTimes = datetime({Files.date}).';  %Use {} to convert the comma-separated list returned by Files.Date into a cell array

converts all the modification times into a datetime column vector.doc between.

With regards to the comparison, I'm still not clear exactly what part of the datetime you want to compare. Do you just want to find the intersection between FileTimes and ShotDate based just on day-month-year? (so you get true for all the 22 March 2019 in FileTimes, and false for the rest)

[Guillaume]

I need to match the dates and times (the times will vary by a small bit)

It's difficult to give you a proper answer without a concrete definition of what is or isn't a match.

Also, how is the time encoded in ShotTime? Seems to be a value from 0 to 1. Is that fraction of a day?

[Dean Kennedy]

I'm assuming I'll have to play around with it to see what works and what doesn't.

Yes I believe it is something along those lines. It happens when you import a time from excel, but can be converted using

ShotTime = datetime(ShotTime,'ConvertFrom','excel');
ShotTime.Format = 'HH:mm:ss';

Answer 2

[Bob Thompson]

The dates and times of the spreadsheet are in separate columns, and I can't import the times as times (they are imported as numbers), is there a way of combining these values or am I going about it in the wrong way?

I can't extract the entire individual field from the struct which has the datetime info, and the field is in string format. Is there a way of converting this to datetime?

You can convert strings and numbers to datetime class using datetime. If necessary, you can concatenate multiple elements together to have a single input. I have not looked at your data directly, but the following is a quick example.

spreadsheet = [01 01 2000 00 00 00]; % Midnight of January 1, 2000. Input sample, you can adjust as needed
filedates = '01/01/2000 00:00:00'; % Same time in string with different format
tmp = [spreadsheet(:,3),spreadsheet(:,1),spreadsheet(:,2),spreadsheet(:,4:6)];
    % Adjusting column order of spreadsheet dates and times to match y m d h m s order.
ssdt = datetime(tmp); % Spreadsheet datetime class conversion
fdt = datetime(filedates,'InputFormat','mm/dd/yyyy hh:mm:ss'); % File information datetime class conversion

%% Note:
% Because your file date information is in a structure you will need to concatenate the information before feeding it into datetime. Instead of putting 'filedates' into the datetime input you will likely end up with something like 'vertcat(files(:).datetime'.

How would I compare the info?

The actual function of this depends on what exactly you are looking to compare, and what you want to do with the results. Generally, some for of logic indexing is what you are probably looking for. Very basic example shown below:

comparison = ssdt(ssdt == fdt);

[Dean Kennedy]

This doesn't work for my data. If I converted my times to datetime, how could I then concatenate with the date info? Or would I need to compare separately? Or would it make more sense to convert the datetimes to strings and then compare to the structure? Is it possible to do that with a structure?