How to save output of for loop (table) into excel sheet?

Good afternoon all,

I am working on Ascii files, I create a for loop (of 365 iterations = no of files) that reads a specific coulmn from ascii files, and because there are some files that has a coulmn legnth that differ from the others I couldn't save the output of the loop into matrix so I saved it as table, my problem now that I couldn't export this table into excel sheet (I used writetable function)

here is my code

clear
clc

for k=01:365
    try
        filename = sprintf('iqac%03d0.tro',k);
        fid=fopen(filename, 'r'); %#  open the file in this Path
        ZTD = textscan(fid,'%*s %*s %f %f %*[^\n]','HeaderLines',14);
        data = cell2mat(ZTD);
        
        zhd = data(:,1);
        zwd = data(:,2);
        ztd = zhd + zwd;
        
        mat{:,k} = ztd;
        mat1=cell2table(mat);
        
        fclose(fid);
        
    catch ME
        disp('An error occurred while processing the files.');
        disp('Execution will continue.');
        continue
    end
end

writetable(mat1,'ztd1.xlsx', 'Sheet', 'ztd');

every time I run the code, I had an error message said that the table size exceed the sheet boundary, although when I run it and save the output as matrix into excel I didn't have such error.

I attached sample of files as an example

Answer 1

[Sulaymon Eshkabilov]

Hi,

There are several errs in your code, k = 1:5 not 1:365. In your case writetable() is not the best option to employ with your data. It is easier to use writematrix() - see the whole code below.

RANGE =[{'A2:A3000', 'B2:B3000', 'C2:C3000', 'D2:D3000', 'E2:E3000'}];
for k=1:5
    try
        filename = sprintf('iqac%03d0.tro',k);
        fid=fopen(filename, 'r'); %#  open the file in this Path
        ZTD = textscan(fid,'%*s %*s %f %f %*[^\n]','HeaderLines',14);
        data = cell2mat(ZTD);
        zhd = data(:,1);
        zwd = data(:,2);
        ztd = zhd + zwd;
        fclose(fid);
        writematrix(ztd,'ZTD1.xlsx' , 'Sheet',1, 'Range',RANGE{k})  
    catch ME
        disp('An error occurred while processing the files.');
        disp('Execution will continue.');
        continue
    end
end

Good luck.

[Ebtesam Farid]

Hi,

I have 365 files for one year, I wrote this code for one year, so I couldn't use your line of Range as I have 365 not just 5 files ...

I couldn't use writematrix as I don't have the same coulmn length for each iteration, and matrix requires that all coulmns have the same length ...

any other suggestions, please?

Thanks

[Sulaymon Eshkabilov]

Here you are mistaken writematrix() writes all different size of data. In your zip file you have only five and not 365 that can be changed if you need 365. Run this code that gives all correctly. My provided code writes different sized data all correctly. You can test it.

Good luck.

[Ebtesam Farid]

it gives me this error message:

Index exceeds the number of array elements (5).

Error in test_1 (line 12)

writematrix(ztd,'ZTD1.xlsx' , 'Sheet',1,

'Range',RANGE{k})

that what I am saying, how to let it work for 365 files (01:365), what I attached is just a sample, I wanted to attach the whole year, but it exceeds 5 Mb so I just attached a sample

[Sulaymon Eshkabilov]

Did you test for five sample data files of yours as you have provided?

While providing 5 files only and running for 365, it will not work for sure.

[Ebtesam Farid]

sure, I tried for five files and it works ... my question for you: are there other suggestions instead of writing 365 Coulmn's letters in RANGE variable??

[Sulaymon Eshkabilov]

You don't have to type in all these which can be generated automatically using an addtional loop code, for instance.

[Ebtesam Farid]

could you tell me, How to write the additional loop to generate them??