Collate data from Excel

Hi guys, I'm a complete MATLAB begginer and I need to find a way to extract the X and Y positions in columns D and E for every unique timestamp in column A across multiple excel files (i.e. create an excel sheet for 10_19_00 which includes the X Y data for that time from loads of files). There are 49 timestamps but they're not in order, which you can see in the images below from two of the excel files. Any help would be much appreciated!

Answer 1

[Pranav Verma]

Hi Oliver,

From your question I understand that you need to read data from multiple sheets in MATLAB and extract columns 'D' and 'E' and finally create a sheet with data collected from both the files based on the timestamps. The approach you can follow is:

• Read the data from all the spreadsheets using readtable function in MATLAB and specifying the 'Sheet' name value pair. You can specify all the sheets from which you want to read the data.
• You can create a new table from the data read in the first step. For example,

%%Let T be the variable in which you read the data and T' be the new table you'll create
T' = table(T.AMImageName, T.AM_cmX, T.AM_cmY, 'VariableNames', {'AMImageName', 'AM_cmX', 'AM_cmY'});

• After the table is created, you can use sortrows function to sort the rows and the rows with same timestamp will occur in groups.
• This way you can create a table and then finally you can create an excel sheet using the writetable function.

I hope this helps!

Thanks

[Jeremy Hughes]

It would be much simpler just to subset the existing table than recreate a new one:

T2 = T(:,["AMImageName", "AM_cmX", "AM_cmY"])

(T' = ... is invalid syntax)

But if you're using readtable to get the table, you can avoid even importing the data you aren't interested in:

opts = detectImportOptions(filename);
opts.SelectedVariableNames = ["AMImageName", "AM_cmX", "AM_cmY"];
T = readtable(filename,opts);