MATLAB Answers

Keeping Headers When Import Excel Then Running Code and Exporting

8 views (last 30 days)
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.

Accepted Answer

Rishabh Mishra
Rishabh Mishra on 4 Sep 2020
Edited: Rishabh Mishra on 4 Sep 2020
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.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by