Keeping Headers When Import Excel Then Running Code and Exporting

I have a code which will scale the data I have from each excel files in a folder director. Then, after the data is scaled, the code allows for the creation of the scaled spreadsheets in a different outfile folder.

The problem I have is that I need the header information to transfer to the new created spreadsheets. This is important.

This is my code:

indir = 'C:\Users\Maki\Desktop\PoissonWeek\ForScale';            %path to input directory
outdir = 'Scaled';     %path to where to put the results
if ~exist(outdir, 'dir'); mkdir(outdir); end

dinfo = dir(fullfile(indir, '*.xlsx'));
filenames = {dinfo.name};
nfiles = length(filenames);

for K = 1 : nfiles
    thisfile = filenames{K};
    infile = fullfile(indir, thisfile);
    outfile = fullfile(outdir, thisfile);
    Z = readtable(infile)
    A = Z{:,:}
    X = std(A)
    outdata = (A - mean(A)) ./ X
    writematrix(outdata,outfile);  

    disp('An error occurred while retrieving information from the internet.');
    disp('Execution will continue.');
    end

To illustrate, I've attached a spreadsheet that would be an infile ("10001_infile.xlsx"). And, I've attached a sample spreadsheet of the processed outfile of that spreadsheet ("10001_outfile.xlsx")

On the infile, there are the following headers:

BackUp Break Catch Manhole PRCP Street

On the outfile, the first row begins with values. There are no headers

I need my code to run where the outfile includes headers, yet also ensures that the headers are matching the columns. Since not all spreadsheets will have 6 columns. Some have 5 columns.

Maybe there is an "if and" statement needed... I've been searching. I just can't find the right method.

I would appreciate any help with the this. Thank you.

Answer 1

[Rishabh Mishra]

Hi,

Based on my understanding of the issue you described. I would suggest a solution. Consider the points below.

>> Z = readtable(infile)
>> A = Z{:,:}

The line of code above, loads a table into ‘Z’ variable. The table stores data along with respective column names (or headers). But ‘A’ variable stores a double array extracted from the table ‘Z’, due this extraction, the column names/headers are lost.

>> outdata = (A - mean(A))./ X

‘outdata’ is also a double array(or matrix) . So, after saving it, the headers are not included in the excel file.

The solution to this problem is to convert the ‘outdata’ matrix to a table ‘outtable’ with headers’ same as that of the ‘Z’ table.

The headers of ‘Z’ table are obtained through:

>>Z.Properties.VariableNames

This is done through following code:

% Convert 'outdata' double array to 'outtable' table
% Column/variable names of 'outtable' are specified by 'Z.Properties.VariableNames'  
outtable = array2table(outdata,'VariableNames',Z.Properties.VariableNames)
% save the table in excel file along with the Column names
writetable(outtable,outfile)

The complete solution code is written below:

SOLUTION:

mat = readtable('infile.xlsx')
A = mat{:,:}
X = std(A)
outdata = (A - mean(A)) ./ X
outtable = array2table(outdata,'VariableNames',mat.Properties.VariableNames)
writetable(outtable,outfile)
 
indir = 'C:\Users\Maki\Desktop\PoissonWeek\ForScale';            %path to input directory
outdir = 'Scaled';     %path to where to put the results
if ~exist(outdir, 'dir'); mkdir(outdir); end
dinfo = dir(fullfile(indir, '*.xlsx'));
filenames = {dinfo.name};
nfiles = length(filenames);

for K = 1 : nfiles
    thisfile = filenames{K};
    infile = fullfile(indir, thisfile);
    outfile = fullfile(outdir, thisfile);
    Z = readtable(infile)
    A = Z{:,:}
    X = std(A)
    outdata = (A - mean(A)) ./ X
    
    % Modified code
    
    % Convert 'outdata' double array to 'outtable' table
    % Column/variable names of 'outtable' are specified by 'Z.Properties.VariableNames'  
    outtable = array2table(outdata,'VariableNames',Z.Properties.VariableNames)
    % save the table in excel file along with the Column names
    writetable(outtable,outfile)
    
    disp('An error occurred while retrieving information from the internet.');
    disp('Execution will continue.');
end

Hope this helps.