MATLAB Answers

Extracting Unknown Amount of Columns from Excel Data

8 views (last 30 days)
Boegs
Boegs on 19 Apr 2019
Commented: Boegs on 22 Apr 2019
So I am trying to sort datasheets I'm given in excel into rows of 7 columns. And the datasheet I am given could have any amount of columns for any row. So I was hoping for some help.
This is what I have so far, but it can only handle up to column Y in excel. I want to be able to write something that can handle any amount of columns, just given the input file.
L = length(xlsread('Input')); %number of rows
N = 7; %desired number of columns
X = NaN(1,7); %first reorganized row is empty
for i=1:L %reads all input rows
xlRange = sprintf('A%d:Y%d',i,i); %range of columns from A to Y
A = xlsread('Input',xlRange); %brings in input data one row at a time
Z = reshape([A(:);nan(mod(-numel(A),N),1)],N,{}); %shapes the data into rows of 7
Z = Z.'; %switches rows for columns
X = cat(1,X,Z); %desired output in a matrix
end
xlswrite('Output',X) %prints to excel sheec

Answers (1)

Jeremy Hughes
Jeremy Hughes on 19 Apr 2019
I recommend using READMATRIX if you have access to R2019a, or READTABLE for earlier releases.
A = readmatrix(filename)
Once you have the matrix in MATLAB, you can loop over the rows of the data for processing.
  3 Comments
Boegs
Boegs on 22 Apr 2019
So the data I recieve is in the format of the "Pre-Code Data" excel file.
And what I want matlab to make it look like is in the format of the "Post-Code Data" excel file.
But the columns I recieve can vary and so can the amount of rows. So I was hoping to create something to be able to format my data no matter the amount of rows or columns.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!