MATLAB Answers

Combining Spreadsheets to Create a Table in Matlab

2 views (last 30 days)
CMatlabWold
CMatlabWold on 29 Jun 2020
Commented: CMatlabWold on 2 Jul 2020
Hello,
I have 4 spreadsheets, which have different data for zip codes. I want to create one spreadsheet for each zip code, where there the data from each spreadsheet is represented in the column for the zip code's own spreadsheet.
For instance, I have these spreadsheets, "WeeklyBreakZip.xlsx", "WeeklyManholeZip.xlsx", "WeeklyCatchZip.xlsx" and "WeeklyBackupZip.xlsx", "WeeklyStreetZip.xlsx" .
Each spreadsheet has zipcodes as the column headers, with the data listed. For example, one Zip Code is 10010.
For each Zip Code, I want to extract the columns from the four worksheets, and create its own worksheet.
So, for example, with zip code 10010, the columns of this new compiled worksheet would be:
Catch Back Break Manhole Street
Then, the data would be listed below.
How would I get Matlab to do this?Thank you. I very much appreciate any help.

Accepted Answer

bharath pro
bharath pro on 29 Jun 2020
I have written an approach which takes care of multiple excel files but requires that all of them have the same number of rows. I also assume that 'Weekly' and 'Zip.xlsx' appear in all the filenames.
Steps:
1) Put all the excel files into a folder
2) Explore the directory and get all excel files using fullfile
3) Store the data in the files to matlab using xlsread
4) Go through all the files
5) Go through all the column names in the files, add the first name in each column to a container ( the pin code ) and the remaining elements of the column are appended with the filename and stored as the value for the pin code in the container. If the pin code is already present in the container append the column to the already existing value for that pincode
6) Go through the container and write each value ( here a 2D matrix ) corresponding to the pincode in to a excel file whose filename is the pincode.
D = 'path_to_folder';
S = dir(fullfile(D,'*.xlsx'));
for k = 1:numel(S)
F = fullfile(D,S(k).name);
[S(k).num,S(k).txt,S(k).raw] = xlsread(F);
end
c = containers.Map;
for i=1:length(S)
k=S(i).name;
k=erase(k,'Zip.xlsx');
k=erase(k,'Weekly');
t1=S(i).num(1,:);
for i1=1:length(t1)
if i1==1
continue;
end
ty=S(i).num(:,i1);
ty=ty(2:end);
ty=ty.';
te=[k,num2cell(ty)];
te=te.';
size(te)
if isKey(c,num2str(t1(i1)))==0
c(num2str(t1(i1)))= te;
else
c(num2str(t1(i1)))=horzcat(c(num2str(t1(i1))),te);
end
end
end
o=c.keys;
v=c.values;
for i=1:length(o)
temp=char(strcat(o(i),'.xlsx'));
xlswrite(temp,v{i});
end

More Answers (1)

Cris LaPierre
Cris LaPierre on 29 Jun 2020
There are a couple things that make this problem challenging
  1. MATLAB does not like having variable names be numbers (the zip codes)
  2. Different amounts of data are recorded in your spreadsheets, meaning you need to have a plan for how to handle missing/extra zip codes between the data.
  3. You have an extra table in WeeklyStreetZip.xslx (two tables with 530 rows).
Forgive the approach here, but it's what I had to do to get something that worked. Feel free to modify. It does take a while to run.
BackupZip = readtable("WeeklyBackupZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
BackupData = readtable("WeeklyBackupZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
BackupData.Properties.VariableNames = BackupZip.Properties.VariableNames;
BreakZip = readtable("WeeklyBreakZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
BreakData = readtable("WeeklyBreakZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
BreakData.Properties.VariableNames = BreakZip.Properties.VariableNames;
CatchZip = readtable("WeeklyCatchZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
CatchData = readtable("WeeklyCatchZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
CatchData.Properties.VariableNames = CatchZip.Properties.VariableNames;
ManholeZip = readtable("WeeklyManholeZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
ManholeData = readtable("WeeklyManholeZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
ManholeData.Properties.VariableNames = ManholeZip.Properties.VariableNames;
StreetZip = readtable("WeeklyStreetZip.xlsx","Range",'1:1',"ReadRowNames",true,"ReadVariableNames",true,"PreserveVariableNames",true);
StreetData = readtable("WeeklyStreetZip.xlsx","NumHeaderLines",1,"ReadRowNames",true);
StreetData = StreetData(1:height(BackupData),:);
StreetData.Properties.RowNames = BackupData.Properties.RowNames;
StreetData.Properties.VariableNames = StreetZip.Properties.VariableNames;
zips = categorical([BackupZip{:,:},BreakZip{:,:},CatchZip{:,:},ManholeZip{:,:},StreetZip{:,:}]);
zips = categories(zips);
r = BackupData.Properties.RowNames;
for z = 1:length(zips)
try
Backup = BackupData{r,zips(z)};
catch
Backup = NaN([height(BackupData),1]);
end
try
Break = BreakData{r,zips(z)};
catch
Break = NaN([height(BreakData),1]);
end
try
Catch = CatchData{r,zips(z)};
catch
Catch = NaN([height(CatchData),1]);
end
try
Manhole = ManholeData{r,zips(z)};
catch
Manhole = NaN([height(ManholeData),1]);
end
try
Street = StreetData{r,zips(z)};
catch
Street = NaN([height(StreetData),1]);
end
zipTable = table(Backup,Break,Catch,Manhole,Street,'RowNames',r);
writetable(zipTable,"WeeklyZipData.xlsx","Sheet",string(BackupZip{1,z}),"WriteRowNames",true);
end
  3 Comments

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by