Why does "writecell" not write certain cell array data to an excel file?

Hello,

i have a 1x1 cell with char data (numerical values joined together with a delimiter, like '12.12;34.34;...'). In Matlab this data is somehow displayed as '''12.12;34.34;...''' after I save this data in another cell array. Where do the extra apostrophes come from? How can i prevent this from happening?

This is how the data is shown in the cell array inside Matlab. The triple apostrophes start at row 6 in the second column and this is exactly the data that is not written to the Excel file.

My guess is that due to the multiple apostrophes in the cell array the data is interpreted as an empty string and therefore everything after the first two apostrophes is ignored/not written but i don't know how to get rid of the unnecessary ones.

This is how i join the numerical values:

for m=1:size(timeseries,2)
    timeseries{m} = num2str(timeseries{m}); % i haven't found a way to join numerical values together
end                                         % without converting them to a string or char first
timeseries_demand = strjoin(timeseries,";");

I then save the data with:

result(end+1,[1,2]) = [id, timeseries_demand];

and write an excel (.xlsx) file with:

writecell(result(:,1), filename, "Sheet", "data", "Range", "A1");
writecell(result(:,2), filename, "Sheet", "data", "Range", "B1");

I'm using 2020b.

Thanks in advance.

[Kumar Pallav]

Is timeseries a variable you created, could you share the whole code and data so that I can reproduce the issue at my end.

[Image Analyst]

What is the extension in filename? csv or xlsx or something else?

[Patrik Schorn]

@Image Analyst The extension of filename is xlsx.

@Kumar Pallav Unfortunately i cannot share the data but i can share the code:

The file read into "timeseries_de_load" contains a value for each timestep in the year in each cell of a row. For example the first value is in cell B1, the next value is in C1, then in D1 and so on. This is the data i want to join into one cell with a delimiter but which is not written to the excel file.

The file read into "importantdata" is there to match the timeseries to certain other data.

The file read into "scenario_load" contains the header of the excel file and example data (row 1 to 5 in the picture in my original post). I want to add the timeseries data to the already existing data in there.

importantdata = readcell("Wichtigste_Datei_Ergebnis_HSKnoten_RegioErgebnis_Matching_Stringsbearbeitet.xlsx", "Sheet", "Matching_Ergebnis");
timeseries_de_load = readcell("Zeitreihen/RegionalDataGERMANY2030.xlsx", "Sheet", "Load");
scenario_load = readcell("ScenarioFile_RC3.xlsx", "Sheet", "loads");
for k=2:1:10%size(importantdata,1)
   EHV_node = importantdata{k,5};
   gemeinsame_knoten = importantdata{k,6};
   node_id = importantdata(k,1);
   load_id = strcat(node_id, '_', EHV_node, '_Load');
   for n=1:1:size(timeseries_de_load, 1)
      if strcmp(timeseries_de_load{n,1}, EHV_node)
          timeseries = timeseries_de_load(n,2:end);
          for m=1:size(timeseries,2)
              timeseries{m} = num2str(timeseries{m}/gemeinsame_knoten);
          end
          timeseries_demand = strjoin(timeseries,";");
          break
      end
   end
   scenario_load(end+1,[1,2]) = [load_id, timeseries_demand];
end
filename_s = "ScenarioFile_RC3_Test.xlsx";
writecell(scenario_load, filename_s, "Sheet", "loads");

Answer 1

[Patrik Schorn]

I found that an excel cell can contain up to 32767 characters. Since the data is almost 70000 characters long it is not written. If i shorten the amount of characters in the data it works.

I guess i'll have to save it to a .csv file and work with that.