How to view a mat file that says preview too large to display Properly in Import Wizard

Hi, I want to view a mat file that is 3X3X1,500,000. I doubleclick on the file folder and it shows up in import wizard. There I doubleclick on the file and is too big to view. Same if I click open variablecommand on matlab.

Ideally I would export it to excel. I tried this below while my setpath is set to the right source directory but all I get is an excel file with the name of my mat file across the top cells (one letter per cell). what is my naive error?

xlswrite('QQ.xls', 'QQ_full.mat')

Answer 1

[Rik]

A mat file is a file, not a variable. It may contain variables. Judging from your description it only contains a single variable.

You could export it to an excel file by loading the variable, reshaping to a 2D array (instead of the current 3D), and writing that variable to an excel file.

S=load('QQ_full.mat');
name=fieldnames(S);
data=S.(name{1});
data=reshape(data,size(data,1)*size(data,2),size(data,3));
xlswrite('QQ.xls',data)

This will probably not work due to the limitations of the xls format. You're better off trying to find a way to plot your data to visualize it, instead of looking at a small numeric portion. If you want to do so anyway:

small=data(:,:,10);

[Anthony Santana]

This seems like a complex answer and I appreciate the time spent. But my question is rather simple. How to export a large file to excel (or any file to excel) that is in mat format. Xlswrite doesn't seem to work anymore, it gives me in cells A1 B1 C1 and D1 this: "Q" "Q" "full" "-" "Mat" the words in my file name, i.e. nonsense. It is a set of Beta's that have been rotated 1.5MM times at random, so there are many results. But even if it was only a 2 X 2 matrix of covariances, all I want to do is export the file to excel so I can deal with it there. I do not want to resize it or anything like that but thanks.

[Rik]

You have a 3D array. Excel only works with 2D. How were you planning to deal with that?

Also, you still seem to be confused about what you have. A mat file contains variables. Variables contain data. What you attempted is to write the name of the mat file to excel, not its contents. Your question is based on the wrong premise, just as if you would ask how to print a zip file. You can print the documents inside a zip file, but not the zip file itself.

[Anthony Santana]

Thanks Rik. I understand now. I tried yourcode and got this:

S=load('QQ_full.mat');
name=fieldnames(S);
data=S.name{1};
data=reshape(data,size(data,1)*size(data,2),size(data,3));
xlswrite('QQ.xls',data)

Unrecognized field name "name".

As I tried to say before, all these files are matrices full of data. This matrix is too large but the others I can see are simply rows and columns of numbers. There are no headers. When I tried to eliminate the fieldnames I got another error.

S=load('QQ_full.mat');
data=S.QQ{1};
data=reshape(data,size(data,1)*size(data,2),size(data,3));
xlswrite('QQ.xls',data)

Brace indexing is not supported for variables of this type.

I am not seeing the 3D aspect but I will take your word for it. Any other code I can try?

I need to get the matrix into excel and sort it and find maxima and minima so looking at a plot not ideal.

Thanks again.

[Steven Lord]

Looking at Microsoft's website, your data set is too large to fit in a worksheet without rearranging. For versions of Microsoft Office newer than Office 2010 the maximum limit on the number of rows and columns a worksheet can have is 1,048,576 (2^20) by 16,384 (2^14). The bottom right cell is XFD1048576. Even if you reshaped your 3-by-3-by-1.5e6 into a 1.5e6-by-9 that's still too many rows.

In addition, even if you were able to open your 13.5 million element array in the Workspace browser or the preview in the Import Wizard, are you really planning to read through or even skim 13.5 million numbers?

You said "I need to get the matrix into excel and sort it and find maxima and minima so looking at a plot not ideal." Why use Microsoft Excel? Why not use the sort, min, max, etc. functions in MATLAB?

[Rik]

I was on mobile, so editing my answer (adding the missing parentheses) was tricky.

Steven's question still stands. What do you actually want to do? Why not do that in Matlab? Excel has more limitations when in comes to data.

[Walter Roberson]

S = load('QQ_full.mat');
name = fieldnames(S);
data = S.(name{1});

data = reshape(data, [], size(data,3));
writematrix(data, 'QQ.xlsx')

However, this will fail: it would create a matrix with 9 rows and 1500000 columns, but Excel can never have more than 16384 columns.

You might do

writematrix(data.', 'QQ.xlsx')

which would try to write as 1500000 rows and 9 columns, but Excel can enver have more than 2^20 = 1048576 rows.

You would have to split into multiple sheets, which is what I suggested when you asked much the same question before; https://www.mathworks.com/matlabcentral/answers/47118-how-to-convert-mat-to-xls#comment_1818359

For example, you could split into 9 sheets to account for the 3 x 3 part. Then each sheet would be responsible for 1500000 items, which you could do be reshaping into multiple columns -- as long as you use 2 or more columns, it would fit. Most natural might be to use 1500 rows and 1000 columns -- though sorting might be more difficult in that case. What dimension do you need to sort across?

[Anthony Santana]

Thanks, see the follow on thread.

Answer 2

[Anthony Santana]

Hi,

P.S. Walter I did not ask this question before, I am someone else leveraging that question, but multiple sheets is an option, thx!

So many great responses! This query is just the tail of a much longer dragon so I was trying to keep it simple. Let me rephrase in a way to answer your responses.

1. First, there must be a way to simply write a matrix from the mat file to an excel file, even if it were a 2 X 2 but in a mat file. The matlab documentation says, hey make your own matrix, then put it in excel. Not useful. I need a mat file to excel.

Q1: I do not understand why writematrix doesn't work for me. But I will try your code. Must be the length of 1.5mm rows I gather.

Note: I did it a brute force way by copy paste to excel and found the 1MM+ row limit I did not know about. And took forever.

I was able to sort each column and find the maxima I needed, but then found multiple ones and a new issue!

I have a data file where I need to find a particular number used in a paper in the 75th percentile.

I used the 75th percentile command but not sure how to focus that on ONE given column.

Q2. So that would be helpful to do that in matlab. Do you know the code to get 75th percentile in ONE column of a matfile?

Q3: Why excel? You may be sorry you asked! The "dragon":

If anyone can help me with the actual coding question, the underlying issue is based on this code I am replicating:

1. I have 3 variables selected in a VAR for t= 528 months and six lags:

dates = 1960 + 7/12 : 1/12 : 2015+4/12;

Uf = UX = [Um,ip,Uf];

X_var = X(lag+1:end,:);

X_exp = [];

for jjj = 1:lag

X_exp= [X_exp, X(lag+1-jjj:end-jjj,:)];

3. Set data = 2 other variables and those are then the restrictions somehow.

Q4: I am not sure how setting "data = var 4, var 5" makes them restrictions?

T = size(X_var,1);

data = [Vs(lag+1:end), Gold_o(lag+1:end)];

4. Then we have a VAR and VEC regression:

reg31 = regstats(X_var(:,1), X_exp);

reg32 = regstats(X_var(:,2), X_exp);

reg33 = regstats(X_var(:,3), X_exp);

eta_m_OLS = reg31.r;

eta_y_OLS = reg32.r;

eta_f_OLS = reg33.r;

eta = [eta_m,eta_y,eta_f];

eta_vec = eta;

vec_A = [reg31.beta(2:end);reg32.beta(2:end);reg33.beta(2:end)];

const_var = [reg31.beta(1), reg32.beta(1), reg33.beta(1)];

5. Now the hard part. Supposedly one column is rotated by a 3X3 orthonormal matrix of random numbers 1.5MM times.

QQ =[ ];

NQ= 1.5*10^(6); %Number of Random Rotation

randn('state',123456) %Fixed seeds

parfor iii = 1:NQ

%generate a random rotation

v = randn(3,3); %First Generate a totally random 3x3 matrix v

[q, r]=qr(v,0); %Obtain the orthonormal matrix of v, call it q

QQ(:,:,iii)=q*diag(sign(diag(r))); % this makes the diagonal of r positive

end

Q's. Somehow in this monster QQ file the authors find the figure for the 75th percentile of 10/1987 and 12/1970.

This QQ monster file is the covariances amounting to 1.5 mm rows by 9 columns.

Q3. I am not sure how to interpret the 9 columns or 1.5 MM rows. Are rows 1-528 lag one? What are the columns, the 3X3 covariances of the 3 variables? Or some of them are rotated? Lost there. The rotations really throw me. Very modern tech.

Q4. How am I supposed to find a 75th percentile of any given month, being as I have no idea what the rows signify or how to understand them as ranges of months? 6 lags X a (3 X 3) matrix X 528 is not 1.5 mm, so hard to see how to date or number the rows to align with the 528 lags.

OK so that's a lot to chew. If anyone can answer any part of these, very grateful.

T

[Anthony Santana]

P.S. Typo here. need to shave the OLS ending off the variables sorry.

[Anthony Santana]

PPS I left out some code AFTER the 75th percentile numbers are hardcoded. Not sure if this helps. I nned to find the Ebar values in the QQ file or the beta file.

lo_combine = [];
hi_combine = [];
for ind_spec = 1:5
    if ind_spec == 1
        Ebar = [4.1634,4.0475,4.5672,4.7314];
    elseif ind_spec == 2
        Ebar = [4.4415,4.3915,4.5672,4.7314]; 
    elseif ind_spec == 3
        Ebar = [2.7744,2.3317,4.5672,4.7314]; 
    elseif ind_spec == 4
        Ebar = [4.1634,4.0475,4.9305,5.0442];
    elseif ind_spec == 5
        Ebar = [4.1634,4.0475,1.9461,2.3317];
    end
    
    % Generate B
    B_out= gen_B(vec_A, QQ, X_var, X_exp,const_var,data,dates_lag,lag,Ebar);
    % Gen IRF
    [lo,hi]=gen_lo_hi(vec_A, B_out,X_var, X_exp,const_var,data,lag);
    
    lo_combine(:,:,ind_spec ) = lo;
    hi_combine(:,:,ind_spec ) = hi;
end

[Anthony Santana]

Typo Sorry 658 lags. Not tha it will affect the 1.5MM row answer.

[Walter Roberson]

You did ask the question before, https://www.mathworks.com/matlabcentral/answers/47118-how-to-convert-mat-to-xls#comment_1818284

[Anthony Santana]

Whoops. Geez sorry I was just looking at this thread!

[Walter Roberson]

Q1:

First, there must be a way to simply write a matrix from the mat file to an excel file, even if it were a 2 X 2 but in a mat file.

I am 100% positive that Mathworks does not supply a function that takes that name of an input .mat file and the name of an output .xls or .xlsx file, and writes the data from the .mat to the excel file.

MATLAB supplies load() to load data from .mat files; you can even specify the exact variable name to load without loading any other variable from the .mat file.

MATLAB supplies writetable() and writematrix() and writecell() that can write to excel files.

writematrix() is documented as:

• writematrix writes out arrays that have more than two dimensions as two dimensional arrays, with the trailing dimensions collapsed.

So if you ask writematrix to write out an array that is 3 x 3 x 1500000, it will reshape() to 3 x 4500000 and try to write that out. Which is a problem for .xls (maximum 65536 rows and 256 columns) and which is a problem for .xlsx (maximum 1048576 rows and 16384 columns). These limits were designed by Microsoft: if you were to ask writematrix() to write a 3 x 3 x 1500000 to a csv file then it would reshape it to 3 x 4500000 and write that out.

You can create an enhancement request to Mathworks asking for a function that takes a .mat file and name of output excel file and copies... but when you do, please be sure to give ideas to Mathworks on what such a function should do for struct(), what it should do for objects (for example graphics objects, or transfer functions or Simulink components), what it should do for multidimensional arrays; whether it should treat char arrays as arrays in which each column is to go into a different excel cell or if instead only one output column should be used (whereas the output for string() arrays is more obvious, that those should go in individual cells -- but character arrays are arrays ...), what to do if an entry is too longer for a output cell, and should categoricals that happen to have the form of numbers be protected on output to be definitely text or should they be allowed to become numeric (which could result in them losing leading or trailing 0s)...

[Walter Roberson]

Q2:

I do not understand why writematrix doesn't work for me

No-one seems to know why Microsoft chose the limit of 2^20 rows and 2^14 columns. People speculate that it had something to do with 32 bit address registers, but since 2^20 * 2^14 is 2^34, no-one seems to quite know why more than 2^32 was permitted.

[Walter Roberson]

randn('state',123456)     %Fixed seeds
 parfor iii = 1:NQ

That is not going to work. See https://www.mathworks.com/help/parallel-computing/repeat-random-numbers-in-parfor-loops.html

[Steven Lord]

Do you know the code to get 75th percentile in ONE column of a matfile?

Since you're using regstats you have Statistics and Machine Learning Toolbox, which means you also have access to the prctile function (unless you're using a REALLY old release of the toolbox, since both functions were introduced before release R2006a.) You could use the dim input to operate on the columns, or you could use indexing to extract that column (to form a vector) and call prctile on that vector.

You've gone into a lot of detail about how you've chosen to implement your solution. Perhaps if you take a step back and give us the higher level overview of what you're trying to do we may be able to suggest functions available in MATLAB or in Statistics and Machine Learning Toolbox to help you more easily accomplish your goal.

If you described following a recipe as "Sift together flour, baking soda, baking powder, and salt. Then whisk together sugar, oil, and egg ..." we may or may not be able to guess what you're making. But if you told us you wanted to bake some muffins we may be able to offer more specific suggestions re: muffins rather than cookies, bread, cake, etc.

[Anthony Santana]

Hi,

Thanks for the very thorough answer. Just FYI- The radn function is working apparently, I do get the random matrix.

Steven, I was looking for what I must do to the percentile command to get it to pick say column 3? I have been using this one "Y = prctile(filename,[75],1)" and it returns the 75th percentile for all the columns. If I put 2 at the end I believe it will do all the rows. If you know I would like to get one column only, what goes in place of the 1? Maybe (range), 3?

I appreciate the cooking analogy, but the idea is we have 3 endogenous variables Uft Umt and Ip and two exogenous ones Gold and Volatility (G and Vx). The Uft is rotated 3X3X1.5MMto orthogoaloze it.

My only remaining questions are:

1. How to write the 75th percentile code to hit one column only,
2. How writing the command "data = gold and Vs" to input these 2 external variables differs from defining my endogenous variables by saying Uf = U_data(:,1); where U_data is my excel data filename.

Forget about trying to understand the Matrix dimensions. I traced it to a subroutine creating more rows than expected.

Again truly appreciative.

Code relevant to number 2 again here -'recipe': to produce reduced form VAR:

T = size(X_var,1);

data = [Vs(lag+1:end), Gold_o(lag+1:end)]; %%% Question How is this different from just uploading the data by "Uf = U_data(:,1)" and then merging that into X_var=Uf,Um,Ip)? %%%

reg31 = regstats(X_var(:,1), X_exp);

reg32 = regstats(X_var(:,2), X_exp);

reg33 = regstats(X_var(:,3), X_exp);

eta_m_OLS = reg31.r;

eta_y_OLS = reg32.r;

eta_f_OLS = reg33.r;

eta = [eta_m_OLS,eta_y_OLS,eta_f_OLS];

eta_vec_OLS = eta;

vec_A = [reg31.beta(2:end);reg32.beta(2:end);reg33.beta(2:end)];

const_var = [reg31.beta(1), reg32.beta(1), reg33.beta(1)];

Thx!

[Steven Lord]

X = rand(100, 3); % Sample data
P75 = prctile(X, 0.75) % If you're being particularly careful, specify DIM as well

P75 = 1×3

0.0072 0.0239 0.0029

P75_col2 = prctile(X(:, 2), 0.75)

P75_col2 = 0.0239

shouldBeSmallOrZero = P75(2) - P75_col2

shouldBeSmallOrZero = 0