Assign names to excel sheets with respet to the original filenames?

Hello. I hope everyone is safe and healthy during these hard times.

I have the codes lines below which help me combine 8 different .xlsx into one, in different sheets. Attached you can find one of the 8, as an example. I want now to assign names each excel sheet according to the name of the weather station the data came from, in order to keep track easily. Eg., for the attached .xlsx the respective sheet name should be "Airport".

Could anyone help me? Thank you in advance!

filenames = {'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Airport\Monthly RH stats Airport 2015.xlsx','C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Eptapurgio\Monthly RH stats Eptapurgio 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Malakopi\Monthly RH stats Malakopi 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Martiou\Monthly RH stats Martiou 2015.xlsx', 'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Paparrigopoulou\Monthly RH stats Paparrigopoulou 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Parko\Monthly RH stats Parko 2015.xlsx', 'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Pedio Arews\Monthly RH stats Pedio Arews 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Rooftop Zanis\Monthly RH stats Rooftop Zanis 2015.xlsx'};
 outfile = [output_path,'\','Monthly RH stats All stations 2015.xlsx'];

for fidx = 1:numel(filenames),
    fTable = readtable(filenames{fidx});
    writetable(fTable,outfile,'Sheet',fidx);
end

Answer 1

[Ameer Hamza]

You can also specify the name of the sheet to the writetable function instead of the sheet number.

First extract the name of weather station from filenames

[names, ~] = regexp(filenames, 'stats\s([\sa-zA-Z]*)\s2015\.xlsx', 'tokens', 'match');
names = string(names);

This regular expression will work as long as the filename end with 2015.xlsx. Then use these names in writetables

for fidx = 1:numel(filenames),
    fTable = readtable(filenames{fidx});
    writetable(fTable,outfile,'Sheet',names(fidx));
end

[Daphne PARLIARI]

Ameer thank you so much!

Is there a way to extract the part of the filename between the word stats and 2015.xlsx? Some of the weather stations have names with 2 words and with the lines you provided me, only the last one is extracted.

Plus, it would be nice to be able to delete the first 3 sheets automatically produced in the excel file, which are empty...

[Ameer Hamza]

This should work in that case

[names, ~] = regexp(filenames, 'stats\s([\sa-zA-Z]*)\s2015.xlsx', 'tokens', 'match');
names = string(names);

[Daphne PARLIARI]

I'm sorry but it didn't work...

[Ameer Hamza]

What is the issue? When I run the following code

filenames = {'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Airport\Monthly RH stats Airport 2015.xlsx','C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Eptapurgio\Monthly RH stats Eptapurgio 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Malakopi\Monthly RH stats Malakopi 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Martiou\Monthly RH stats Martiou 2015.xlsx', 'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Paparrigopoulou\Monthly RH stats Paparrigopoulou 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Parko\Monthly RH stats Parko 2015.xlsx', 'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Pedio Arews\Monthly RH stats Pedio Arews 2015.xlsx',...
'C:\PhD\Projects\LIFE ASTI\C.3\WRF_main_TS_2015_ALL\WRF_Times_ALB+EMISS_FINAL2015\WRF_Thessaloniki_stations_2015\Evaluation_Output_2015(1st layer)\Rooftop Zanis\Monthly RH stats Rooftop Zanis 2015.xlsx'};

[names, ~] = regexp(filenames, 'stats\s([\sa-zA-Z]*)\s2015.xlsx', 'tokens', 'match');
names = string(names);
disp(names')

I get the following output

    "Airport"
    "Eptapurgio"
    "Malakopi"
    "Martiou"
    "Paparrigopoulou"
    "Parko"
    "Pedio Arews"
    "Rooftop Zanis"

[Daphne PARLIARI]

Oh my mistake, sorry... Thanks so much!!

[Daphne PARLIARI]

Can I please ask something else? Is there a way to perform the above lines of code if the filenames are .csv and not .xlsx?

[Ameer Hamza]

Yes, you just need to make an appropriate regular expression. If your filename follow same format, and only xlsx is replaced by csv, then you can use the following regular expression

'stats\s([\sa-zA-Z]*)\s2015.csv'

[Stephen23]

Note that in a regular expression the period character matches any character. If you want to match a literal period character, then it must be escaped:

'stats\s([\sa-zA-Z]*)\s2015\.csv'
%                          ^^ actually matches period character.

In this situation it is unlikely to make much difference, but it is worth knowing.

[Ameer Hamza]

Stephen, thanks for pointing out. I was a bit sloppy there.