Read multiple excel sheets and read a particular column from each and list them in a separate Excel file

I have 10 excel sheets with similar structure (same column and row headings, but with different readings). I need to read the excel files and extract one column from each sheet and save into another separate excel sheet.

Eg.

Sl. No. Name Date

1 xxx yyy

2 xxx yyy

If this is the structure of the input sheets, I need only the Name column saved in the output sheet as seen below;

Name Name Name

xxx xxx xxx

xxx xxx xxx

I used this to open multiple excel files: [file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');

Answer 1

[Ameer Hamza]

Try something like this

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
output_file_name = 'output_file.xlsx';
column_name = {'T3x'}; % <--- column name here
for i=1:numel(file)
    t = readtable([filepath file{i}]);
    writetable(table(t.(column_name{:}), 'VariableNames', column_name), output_file_name, 'Range', [char(64+i) '1'])
end

t.Name in last line of for loop specify which column is selected from the original files.

For multiple columns, the following code will work

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
output_file_name = 'output_file.xlsx';
column_name = {'Hx', 'Hy'}; % <--- columns name here
for i=1:numel(file)
    t = readtable([filepath file{i}]);
    writetable(t(:, ismember(t.Properties.VariableNames, column_name)), output_file_name, 'Range', [char(65+numel(column_name)*(i-1)) '1'])
end

[Ameer Hamza]

I guess this error happened because you just selected a single file with uigetfile. I wrote the code under the assumption that you are going to select multiple files. The following code take care of this edge case.

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
output_file_name = 'output_file.xlsx';
file = string(file);
filepath = string(filepath);
for i=1:numel(file)
    t = readtable(strcat(filepath, file(i)));
    writetable(table(t.Var1), output_file_name, 'Range', [char(64+i) '1']) % <--- replace "var1" in t.Var1 with the name of your column
end

[Wayne]

Hi, the first code was implemented without errors when I selected three excel files, but the output excel sheet gives output of only one input file.

And the second code throws this error for single and multiple input files:

Input must be a row vector of characters.

Error in Test (line 6)

t = readtable(strcat(filepath, file(i)));

Thank you!

[Ameer Hamza]

Which MATLAB release are you using?

[Ameer Hamza]

Thes functions should work on these release. Can you paste the code from your script so that I can see if there is some issue in your implementation.

[Wayne]

Code 1:

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');

output_file_name = 'output_file.xlsx';

for i=1:numel(file)

t = readtable([filepath file{i}]);

writetable(table(t.T3x), output_file_name, 'Range', [char(64+i) '1'])

end

Code 2:

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');

output_file_name = 'output_file.xlsx';

file = string(file);

filepath = string(filepath);

for i=1:numel(file)

t = readtable(strcat(filepath, file(i)));

writetable(table(t.T3x), output_file_name, 'Range', [char(64+i) '1']) % <--- replace "var1" in t.Var1 with the name of your column

end

[Ameer Hamza]

I guess the second solution does not work because the string class was added in the R2016b release, and it may not have full support for all functionality.

As for first solution, you told that it only adds one column. Which column is that? Is it the A1 column? Can you see how many times does the loop runs by adding a breakpoint inside the for loop.

[Wayne]

When the code is run once the values of the required column from one random input file is output, only when the code is run twice or thrice (changes each time), values from all the input files are shown in the output file.

[Ameer Hamza]

Thats a strange behaviour. Can you give a second sample file of your dataset? Maybe I can try to replicate the issue.

[Ameer Hamza]

If the file does not exist, then the function writetable will create the file. If it already exists, it will just overwrite the current content. However, it will not remove the previous content. For example, If I add new data to 2nd column, it will overwrite it but leave the other columns unchanged.

[Wayne]

The output of all the input files appear in the first run only if output_file.xlsx is created before the run.

And is there anyway the data from the different input files be named differently in the output instead of the current Var1 for all columns?

Thank you!

[Ameer Hamza]

For me, the file is automatically created, whether it is already present or not. Maybe, in R2016b, there was some bug in the implementation of writetable.

The second issue was an oversight on my part. The following code will give the same column name as the original files.

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
output_file_name = 'output_file.xlsx';
column_name = {'T3x'}; % <--- column name here
for i=1:numel(file)
    t = readtable([filepath file{i}]);
    writetable(table(t.(column_name{:}), 'VariableNames', column_name), output_file_name, 'Range', [char(64+i) '1'])
end

[Wayne]

The reason why all the data are not written the first time seems to be because, when the data of the first input file is written, the output_file.xlsx automatically opens and because its open the script is not able to append the data from the following input files.

[Ameer Hamza]

Yes, it seems to be difference between releases. In my case, the file does not open automatically.

[Wayne]

The solution works fine, thank you!

This might be a silly question, but can you tell me the purpose of these two 'Range', [char(64+i) '1'] in writetable.

What changes needs to be done if multiple columns are to be extracted from the input files.

[Ameer Hamza]

'Range' option is used with writetable to specify the range of excel cells in which the data will be inserted. If it is a single cell, the data will be pasted, started from that cell. I defined it as

[char(64+i) '1']

Which will become

'A1' % for i=1
'B1' % for i=2

and so on. This is just a convenient way to specify that i^th file should be inserted to i^th column.

[Wayne]

What does specifically char(64+i) mean.

[Ameer Hamza]

char() takes the ASCII number and outputs the corresponding character. Since ASCII code for A is 65, so char(64+1) will output 'A'

[Ameer Hamza]

Check the code in updated answer for reading multiple columns.

[Wayne]

Is there anyway we can plot the results of the output excel file. Like, since the same data is extracted from all the input files, can we plot them as y axis with a common x axis.

[Ameer Hamza]

Try following code to see how it can be done.

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
output_file_name = 'output_file.xlsx';
column_name = {'Hx', 'Hy'}; % <--- columns name here

fig = figure();
ax = axes();
l = legend(ax);
hold(ax);
ax.ColorOrder = rand(numel(file), 3); % random colors that will repeat
drawnow;
for i=1:numel(file)
    t = readtable([filepath file{i}]);
    writetable(t(:, column_name), output_file_name, 'Range', [char(65+numel(column_name)*(i-1)) '1']);
    p = plot(t{:, column_name});
    l.String(end-numel(column_name)+1:end) = strcat(column_name, '-', num2str(i));
    drawnow;
end

[Wayne]

Is there any alternative to the solution that you gave yesterday for reading only single input file rather than multiple files without the string error.

[Ameer Hamza]

Here is a quick fix. Just change the beginning of the code like this

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)', 'MultiSelect', 'on');
if ~iscell(file)
    file = {file};
end
output_file_name = 'output_file.xlsx';