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

19 次查看（过去 30 天）
Anthony Santana
Anthony Santana 2021-11-4
评论： Steven Lord 2021-11-9
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')

请先登录，再回答此问题。

采纳的回答

Rik
Rik 2021-11-4
编辑：Rik 2021-11-8
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);
  7 个评论
显示 5更早的评论
Walter Roberson
Walter Roberson 2021-11-8
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?

请先登录，再进行评论。

更多回答（1 个）

Anthony Santana
Anthony Santana 2021-11-8
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
  11 个评论
显示 9更早的评论
Anthony Santana
Anthony Santana 2021-11-9
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
Steven Lord 2021-11-9
Ran in:
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

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by