Export many data into the same excel file.

Hello Community,

I have about a thousand audio files in this directory and I want to export them to the same excel, without overwriting the previous one.

I have this code, is anyone able to improve it? Or another solution? Thank you in advance!

samp_rate = 44000;
m = 1;

cd 'some folder'
Extracted_files = dir;
nExtracted_files = length(Extracted_files);
filename = 'reference.xlsx';

for i = 1 : nExtracted_files
[data,samp_rate] = audioread(Extracted_files(i).name);
A(m) = data;
save([Extracted_files(i).name)
m = m + 1;
end

Answer 1

[Peng Li]

for i = 1 : nExtracted_files
    [data,samp_rate] = audioread(Extracted_files(i).name);
    
    % different files may have different length, so the following sentence
    % might give you an error
    % A(m) = data;
    % And if they are with the same length, better way is to preallocate
    % matrix A to speed up
    
    % this only saves to mat
    % save([Extracted_files(i).name)
    
    % you don't need this m, as you have the loop variable i
    % m = m + 1;
    
    % below will export the data to a column of the spreadsheet starting
    % from A, and then B, and so on.
    writematrix(data(:), 'youExcelFileName', ...
        'FileType', 'spreadsheet', 'Sheet', 1, 'Range', [char('A'+(i-1)) '1']);
    
    % note, you may need to change the value for Range 
    % [char('A'+(i-1)) '1'] if you have more than 26 files. This only works
    % if you have less than 26 files.
end

[whalelady]

Thank you for the reply. I apologise as I am still considered new to Matlab.

Could you aid me with one more? Thank you in advance!

If I have 3070 audio files, do I write it this way in the code below?

Btw inside these 3070 audio files there is only one column like the picture shown,

but yes they are at different lengths/depth.

samp_rate = 44000;

cd 'some folder'
Extracted_files = dir; 
nExtracted_files = length(Extracted_files); %% with nExtracted_files = 3070
filename = 'reference.xlsx'; %%create excel file

for i = 1 : nExtracted_files
[data,samp_rate] = audioread(Extracted_files(i).name);

%% I am extremely confused of the line below... 
%% how should I allocated 3070 within [char('A'+(i-1)) '1']? is there other ways to write this? 
%% how should I write range?
writematrix(data(:), 'reference.xlsx', ...
        'FileType', 'spreadsheet', 'Sheet', 1, 'Range', [char('A'+(i-1)) '1']);

end

[Peng Li]

This won't be working; you'd need to work out a better way to define Range properties as [char('A'+(i-1)) '1'] goes to Z1 after 26th file and would be [1 which doesn't make sense for Excel.

I think the first thing you need to think is do you necessarily need to write them to Excel. They have different length, and after that you are up to a weird Excel file which is difficult to handle. What you will have to do is probably to load these columns one by one, which is equivalent to what you have done with audioread.

If you want to extract an equal length of segment from each of these audio files, you can concatenate these segments into an array and write the array to file together once, which is more efficient and you don't need to worry about the Range stuff in Excel.