Extracting Unknown Amount of Columns from Excel Data

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

Answer 1

[Jeremy Hughes]

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.

[Boegs]

Hey Jeremy,

I am not as familiar with READTABLE (since I have R2018a). How would I loop through the rows using the readtable function? I don't know the format of the function and the

help readtable

wasn't the most clear.

[Jeremy Hughes]

You shouldn't need the READ function (which ever one you're using) to do the looping.

Bring in all the data, then loop over the data. Even in your example:

L = length(xlsread('Input')); %number of rows

Could instead be:

data = xlsread('Input');
L = length(data); %number of rows

Since you're reading in everything already, then throwing it out, then reading it again row by row, you're really doing a lot of wasted work.

A = data(:,i)

I'm not sure what reading row-by-row is doing for your case since I don't have your file.

If you upload an example file, it might be more clear what you're trying to accomplish.

[Boegs]

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.