Combining Spreadsheets to Create a Table in Matlab

2 次查看（过去 30 天）
CMatlabWold
CMatlabWold 2020-6-29
评论： CMatlabWold 2020-7-2
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.

请先登录，再回答此问题。

采纳的回答

bharath pro
bharath pro 2020-6-29
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

更多回答（1 个）

Cris LaPierre
Cris LaPierre 2020-6-29
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 个评论
显示 1更早的评论
Cris LaPierre
Cris LaPierre 2020-6-30
That is a newer setting, so if you are using an older versino of MATLAB, it is likely it's not available.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by