Loop on changing databases

Hi,

We run a code where part of it depends on variables we import from excel.

We want to run a loop on the code for it to run 20 times importing the variable from different excel files to create scenarios. Each scenario has to be saved automatically. The name of the variables never change.

Can we do this?

Thank you very much

Answer 1

[Abhiram Bhanuprakash]

Hi Alexandra,

Yes you can do this using xlsread to read from different Excel files. The structure of the code would look like this:

excelFilenamesCellArray = {Array of Excel file names/paths as strings};
for i=1:20
    [num,txt,raw] = xlsread(excelFilenamesCellArray{i});
    %Create scenario
    %Save as separate file using (may be) xlswrite?
    xlswrite(filename,<scenario_variable>);
end

I'm not sure what you mean by 'scenario', so the above code is not complete. You can modify it as per your requirement.

Doc for xlsread and xlswrite are at:

- xlsread: http://www.mathworks.com/help/matlab/ref/xlsread.html

-xlswrite: http://www.mathworks.com/help/matlab/ref/xlswrite.html

Hope this helps,

Cheers!

Abhiram

[Alexandra]

Thank you very much Abhiram, I am trying to apply your suggestion. On the array of excel names I get: Undefined function or variable. Could you exemplify the row? I am also trying to save the results as .mat file that would have all the variables and as excel files using: filename3 ='XX.xlsx'; C = [Y, Z, W]; sheet3 = 1; xlRange3 = ('A4:D13'); xlswrite(filename3,C,sheet3,xlRange3) But all of this must have an increasing index to differentiate scenarios (a piece of code that evaluates results depending on a unique monte carlo simulation and the assumptions from excel). I am trying to link the i to the filename.

It would also be great if I could do this using 20 rows in an unique excel file (1 row the name of variables, the other rows the scenarios) instead of multiple excel files). Thanks a lot

[Abhiram Bhanuprakash]

Hi Alexandra,

The code which I shared was not meant to be the exact code but a sample demonstrating how you can approach this.

For your latest questions, I have the following suggestions:

1. Say if you have two files named 'filename1.xlsx' and 'filename2.xlsx'. You can define:

excelFilenamesCellArray = {'filename1.xlsx', 'filename2.xlsx'};

2. If you need an increasing index, you can make it depend on the loop counter variable and make the filename, sheet and xlRange cell arrays.

For example, you can do something like:

for cnt=1:20
    %Earlier code
    filename{cnt} = ['XX', num2str(cnt), '.xlsx'];
    sheet{cnt} = ['sheet', num2str(cnt)];
    xlRange{cnt} = ['xlRange', num2str(cnt)];
end

3. If you want to do this using 20 rows in a unique excel file, you can use xlsread only only outside the for loop. If you check the doc for xlsread, you can see that, if you use this kind of syntax:

[num,txt,raw] = xlsread(___)

You will get the raw data (both text and numbers) from the Excelsheet. You can use index on this raw data to extract rows/columns of your choice.

Hope this helps,

Cheers!

Abhiram

[Alexandra]

Hi Abhiram, it helps. Thanks a lot. I did manage the xlsread but it doesn't insert the variables in the workspace, so the next lines of code don't recognize the variables.