Collate data from Excel

조회 수: 1(최근 30일)
Oliver Hancock
Oliver Hancock 2021년 2월 11일
댓글: Jeremy Hughes 2021년 2월 18일
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!

답변(1개)

Pranav Verma
Pranav Verma 2021년 2월 18일
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
  댓글 수: 1
Jeremy Hughes
Jeremy Hughes 2021년 2월 18일
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);

댓글을 달려면 로그인하십시오.

제품


릴리스

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by