Combining Spreadsheets to Create a Table in Matlab

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]

Can there be more than the 4 spreadsheets given in the question?

[CMatlabWold]

Hi. Yes.

Answer 1

[bharath pro]

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

[CMatlabWold]

Wow. It's like magic. Thank you!

Answer 2

[Cris LaPierre]

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

[CMatlabWold]

Thanks!

Although, I get this error:

Error using readtable (line 197)

Invalid parameter name: PreserveVariableNames.

[Cris LaPierre]

That is a newer setting, so if you are using an older versino of MATLAB, it is likely it's not available.

[CMatlabWold]

Yes, I downloaded 2020. It works . Thanks!