need to read a mixed csv file

I would like to read the csv file attached and generate the following variables

MAIN_FOLDER ="V:\OPTIMIZATION_DEPOT\AntGod\"

SUB_FOLDER="V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\"

MACHINE="V:\ "

PROJECT_NAME="20220110_0614_Yaqing_Optimization"

Variable_Names =[ "sk_outer_polyrod" "sk_inner_polyrod" "sk" "n_offset" "k15" "k14" "k13" "k12" "k11" "k10 "]; (strings)

Variable_Units =[ "Nan" "Nan" "mm" "mm" "mm" "mm" "mm" "mm" "mm" "mm" ]; (strings)

Initial_Value =[0.75 0.85 0.08 5 15 32 61 91 86 80 ]; (floating numbers)

Minimum_Value =[ 0.7 0.7 0.06 3.75 11.25 24 45.75 68.25 64.5 60 ]; (floating numbers)

Maximal_Value =[ 1.5 1.4 0.3 7 20 42 80 120 120 110 ] (floating numbers)

I really would like to have this in csv format so I can easily edit it with either microsoft excel or Libreoffice calc. It's supposed to be an input file for an optimization setup. I tried many options (readcsv, readxlsx,csv2struct, etc) but none of them allowed me to achieve this

Thank you

Answer 1

[Voss]

Try this. It will create a struct (called 'vars') with fields corresponding to the variables you specified. (If you really want to make those variables in your workspace, you can do that using eval() or assignin() on each field of the struct vars.)

It may be flexible enough to work on variations of the file you posted.

c = readcell('TEST.csv','TextType','string');
c(cellfun(@(x)any(ismissing(x)),c)) = {""};

idx = find([c{:,1}] == "Variable_Names",1);
data_exists = ~isempty(idx);
if ~data_exists
    idx = size(c,1)+1;
end

vars = struct();

for i = 1:idx-1
    try
        vars.(c{i,1}) = c{i,2};
    catch ME
        disp(ME.message);
    end
end

if data_exists
    for j = 1:size(c,2)
        try
            vars.(c{idx,j}) = [c{idx+1:end,j}];
        catch ME
            disp(ME.message);
            continue
        end
        if isstring(vars.(c{idx,j}))
            vars.(c{idx,j})(arrayfun(@(x)x == "",vars.(c{idx,j}))) = "Nan";
        end
    end
end

vars

vars = struct with fields:
       MAIN_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\"
        SUB_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\"
           MACHINE: "V:\"
      PROJECT_NAME: "20220110_0614_Yaqing_Optimization"
    Variable_Names: ["sk_outer_polyrod"    "sk_inner_polyrod"    "sk"    "n_offset"    "k15"    "k14"    "k13"    "k12"    "k11"    "k10"]
    Variable_Units: ["Nan"    "Nan"    "Nan"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"]
     Initial_Value: [0.7500 0.8500 0.0800 5 15 32 61 91 86 80]
     Minimum_Value: [0.7000 0.7000 0.0600 3.7500 11.2500 24 45.7500 68.2500 64.5000 60]
     Maximal_Value: [1.5000 1.4000 0.3000 7 20 42 80 120 120 110]

[NAFTALI HERSCOVICI]

looks good.

this is one step in the developing of an optimization routine

I want to use this csv file as

1. input file (this step was just achieved with your suggestion)

2. journal file. For each iteration in the optimization, a set of variables (generated by the optimizer) and a set of goals functions (generated by the application used in the optimization)

The journal section of the spreadsheet would include one row for titles, and below the title row the iterations output would be added

Something like

Iter Date time v1 v2 v3 v4 v5 v5 gf1 gf2 gf3

1 1/30/2022 13:47 1.8 -3.2 5.9 1.1 -0.4 7.9 0.2 -9.8 0.1

2 1/30/2022 14:47 4.8 -9.2 5.9 1.1 -0.4 7.9 3.0 4.5 2.8

3 1/30/2022 15:47 -0.5 -4.2 0.0 1.3 3.12 3.9 0.001 -0.001 0.13

So, when the optimization starts, the TEST.csv file I provided is read and the initial values of the variables (plus the limits) are used by the optimizer to generate the first iteration (set of variables) that are used by the application. Another MATLAB script is using the output of the application and calculates the goal functions

At the end of each iteration, the journal would document the set of variables used in iteration n and the goal functions it produced

All in one file that can be easily used later in excel or LibreOffice Calc for further processing.

My problem is that I am old (from the FORTRAN generation) and kind of slow with MATLAB. The huge variety of types makes it tough for me to understand what I can do with one conversion function or another and what method I need to use to achieve the goals stated above.

I hope this explains it all

Thank you

[Voss]

One problem you might run into when using a single file for input and output is that you may have to know when you are reading an input file that has already been used (i.e., it has outputs that need to be ignored when re-running and reading the inputs again) and adjust your input-file reading code to handle that. Storing inputs and outputs in two different sheets would avoid that situation, though, and you'd just have to make sure the output sheet is cleared when you start a new run (but it looks like readcell() doesn't support reading a specified sheet in a .csv file, but you could use an .xslx file). In general, I'd say it's a good idea to keep your input files and output files separate, and you can combine them later (by hand or with some post-processing code) if necessary.

But let's say you really have to have your inputs and outputs in one sheet of one .csv file. I've modified the code I posted earlier to handle that and posted it below as a function. Then an outline of your simulation process (at least as far as reading input and writing outputs) might look like this:

vars = read_inputs('TEST.csv')

vars = struct with fields:
       MAIN_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\"
        SUB_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\"
           MACHINE: "V:\"
      PROJECT_NAME: "20220110_0614_Yaqing_Optimization"
    Variable_Names: ["sk_outer_polyrod"    "sk_inner_polyrod"    "sk"    "n_offset"    "k15"    "k14"    "k13"    "k12"    "k11"    "k10"]
    Variable_Units: ["Nan"    "Nan"    "Nan"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"]
     Initial_Value: [0.7500 0.8500 0.0800 5 15 32 61 91 86 80]
     Minimum_Value: [0.7000 0.7000 0.0600 3.7500 11.2500 24 45.7500 68.2500 64.5000 60]
     Maximal_Value: [1.5000 1.4000 0.3000 7 20 42 80 120 120 110]

% copy input file to input/output file, for testing/demonstration:
copyfile('TEST.csv','TEST_with_output.csv');

% write header to input/output file:
header = {'Iter' 'Date' 'time' 'v1' 'v2' 'v3' 'v4' 'v5' 'v6' 'gf1' 'gf2' 'gf3'};
writecell( ...
    [repmat({''},10,numel(header)); header], ...
    'TEST_with_output.csv', ...
    'WriteMode','append');

% mimic 10 iterations of writing outputs, writing random numbers:
for iter = 1:10
    writecell( ...
        num2cell(randn(1,numel(header))), ...
        'TEST_with_output.csv', ...
        'WriteMode','append');
end

% show the contents of the input/output file (scroll down to see it all):
fid = fopen('TEST_with_output.csv');
data = fread(fid);
fclose(fid);
disp(char(data.'));

MAIN_FOLDER,V:\OPTIMIZATION_DEPOT\AntGod\,,,,,
SUB_FOLDER,V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\,,,,,
MACHINE,V:\,,,,,
PROJECT_NAME,20220110_0614_Yaqing_Optimization,,,,,
,,,,,,
Variable_Names,Variable_Units,Initial_Value,Minimum_Value,Maximal_Value,,
sk_outer_polyrod,,0.75,0.7,1.5,,
sk_inner_polyrod,,0.85,0.7,1.4,,
sk,,0.08,0.06,0.3,,
n_offset,mm,5,3.75,7,,
k15,mm,15,11.25,20,,
k14,mm,32,24,42,,
k13,mm,61,45.75,80,,
k12,mm,91,68.25,120,,
k11,mm,86,64.5,120,,
k10,mm,80,60,110,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
,,,,,,,,,,,
Iter,Date,time,v1,v2,v3,v4,v5,v6,gf1,gf2,gf3
-0.834053049471247,0.0638237099326068,-1.69535595402121,-0.254238657419945,0.0859509912011616,-0.459212306442383,1.16746803167465,-1.20206560202796,2.25547831723073,-0.415617326595633,0.141179439868623,0.332184938926179
0.284312005511423,-0.0453599806512146,-0.380722649714853,-0.276843516815978,1.23010107539515,1.36800507806754,-0.598467515486648,-0.149397051785839,-0.192950878933991,-0.776062511116543,-0.403553605229395,-0.386424783216702
0.90171751170409,-1.62956101867028,-0.386532173789433,-0.176309707977616,-0.814168072916685,0.107743379014478,0.22617373240187,0.446316714858819,-0.473429909045889,-0.689954845151894,-0.64111743742924,-0.384834874295103
0.120407219935611,0.318851257622773,-0.544667760127628,-0.335068835937289,3.35491170000793,-1.31474923573744,-1.50917731273698,0.595204343492618,-1.37765844839568,-2.04384848565967,0.935932065050823,-0.0392050695792043
-0.350386797107595,-1.62178007127423,0.891730894974466,-0.692232210007168,0.715426401354193,0.0860234627544556,-1.89764069556632,0.118118849320953,0.442436380260756,0.212628880673655,-1.51805148273909,0.813390085831209
-0.494943829560729,-0.114328410713556,-0.706944554696057,-0.18214840124783,-0.581827560438934,0.727333243095451,-0.481308019887806,-0.980725987513444,-0.437793224032052,1.37121134224633,0.997882576916888,0.154613057817653
1.57810127225245,0.584035418010269,1.8958089875988,0.105405169879687,-0.738281562434177,-0.549540423049101,-0.095659817783595,-0.451206465783693,0.0138039606549887,1.1207852567085,1.07981911095009,-0.00644637165064911
-0.373073608706137,-0.111275833998756,-0.313640951926427,0.159368895842088,0.62043712715852,0.193363405070605,-0.622025614554004,-0.179217235558253,0.323867110872536,-0.388437230935154,1.30245776544177,-0.0393790292718932
0.901451520201897,-0.227565686500662,-1.52863915653523,-1.15016823718505,0.0450647192863002,3.18736199211887,0.0515326976806573,-0.938557063923623,-0.2395836289594,0.630360907175292,-1.09264826192784,-1.13518917509033
-0.815438920578495,0.786047198570141,-0.727711207957483,-0.0278172057319772,-0.375978946843539,0.729488317956566,0.0275569742847131,0.701424708642837,-0.462988724878518,-0.854872311437148,0.824987885434029,1.32511277413196

% make sure inputs can still be read, ignoring outputs:
read_inputs('TEST_with_output.csv')

Invalid field name: ''.

ans = struct with fields:
       MAIN_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\"
        SUB_FOLDER: "V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\"
           MACHINE: "V:\"
      PROJECT_NAME: "20220110_0614_Yaqing_Optimization"
    Variable_Names: ["sk_outer_polyrod"    "sk_inner_polyrod"    "sk"    "n_offset"    "k15"    "k14"    "k13"    "k12"    "k11"    "k10"]
    Variable_Units: ["Nan"    "Nan"    "Nan"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"    "mm"]
     Initial_Value: [0.7500 0.8500 0.0800 5 15 32 61 91 86 80]
     Minimum_Value: [0.7000 0.7000 0.0600 3.7500 11.2500 24 45.7500 68.2500 64.5000 60]
     Maximal_Value: [1.5000 1.4000 0.3000 7 20 42 80 120 120 110]

Honestly, it was kind of a pain just now to adjust the input reading code to handle and ignore outputs in the file, and I wouldn't want to have to go back and change it again when something else changes in a few months or whatever, you know what I mean? So I really advise against combining input and output files.

In fact I would avoid relying on any files at all as much as possible; just kep your stuff in RAM. I mean, set up your code such that it reads your input file once, performs your simulations, and then writes your output file(s) once. That will just avoid a bunch of bad things that cannot be anticipated now that could happen with storing inputs and outputs together and appending existing files. Anyway, it's worth considering, especially at this point, if you are not yet tied to a particular setup.

function vars = read_inputs(input_file)

c = readcell(input_file,'TextType','string');
c(cellfun(@(x)any(ismissing(x)),c)) = {""};

idx = find([c{:,1}] == "Variable_Names",1);
data_exists = ~isempty(idx);
if ~data_exists
    idx = size(c,1)+1;
end

vars = struct();

for i = 1:idx-1
    try
        vars.(c{i,1}) = c{i,2};
    catch ME
        disp(ME.message);
    end
end

if data_exists
    
    idx_end = find([c{:,1}] == "Iter",1);
    if isempty(idx_end)
        last_input_row = size(c,1);
    else
        last_input_row = idx+find(any(cellfun(@(x)~isstring(x) || x ~= "",c(idx+1:idx_end-1,:)),2),1,'last');
    end
    
    for j = 1:size(c,2)
        if ~isstring(c{idx,j}) || c{idx,j} == ""
            continue
        end
        try
            vars.(c{idx,j}) = [c{idx+1:last_input_row,j}];
        catch ME
            disp(ME.message);
            continue
        end
        if isstring(vars.(c{idx,j}))
            vars.(c{idx,j})(arrayfun(@(x)x == "",vars.(c{idx,j}))) = "Nan";
        end
    end
end

end

[NAFTALI HERSCOVICI]

thanks, will try

[NAFTALI HERSCOVICI]

just now read your comments and advices. thank you

my experience actually tells me that I have to rely on files because a simulation can crash.

I had cases in which after a few days of optimization I lost everything in a blip because of a crash. the simulation engines I use are not very stable.

This is why I need to keep a journal, which gives me the option to restart the optimization from the point before the crash. just reading the journal would allow me tosave the time of recalculating all the iterations lost.

The one file for input/output, in this case, seems to me OK because the input role is played ONCE at the begining of the process. the rest is all output.

Answer 2

[Image Analyst]

Try this:

data = readcell('test.csv')
% MAIN_FOLDER ="V:\OPTIMIZATION_DEPOT\AntGod\"
% SUB_FOLDER="V:\OPTIMIZATION_DEPOT\AntGod\AntGODorig_20211223_0528\O_20220110_0614_Yaqing_Optimization_OMEGA002\"
% MACHINE="V:\ "
% PROJECT_NAME="20220110_0614_Yaqing_Optimization"
% Variable_Names =[ "sk_outer_polyrod"  "sk_inner_polyrod"  "sk"  "n_offset"  "k15"  "k14"  "k13"  "k12"  "k11"  "k10 "]; (strings)
% Variable_Units  =[ "Nan" "Nan"    "mm"   "mm"   "mm"   "mm"   "mm"   "mm"   "mm"   "mm"  ]; (strings)
% Initial_Value  =[0.75  0.85  0.08  5  15  32  61  91  86  80 ]; (floating numbers)
% Minimum_Value =[ 0.7  0.7  0.06  3.75  11.25  24  45.75  68.25  64.5  60 ];  (floating numbers)
% Maximal_Value =[ 1.5  1.4  0.3  7  20  42  80  120  120  110 ] (floating numbers)
SUB_FOLDER = data{2,2}
slashLocations = strfind(SUB_FOLDER, '\')
MAIN_FOLDER = SUB_FOLDER(1:slashLocations(3))
MACHINE = data{3, 2}
PROJECT_NAME = data{4, 2}
Variable_Names = data(6 : 15);
Variable_Units = data(6 : 15, 2);
for row = 6 : 15
    Initial_Value(row - 5) = data{row, 3};
    Minimum_Value(row - 5) = data{row, 4};
    Maximal_Value(row - 5) = data{row, 5};
end
fprintf('Done!\n');

[NAFTALI HERSCOVICI]

thanks will do

[NAFTALI HERSCOVICI]

I get this error

Unable to perform assignment because the indices on the left side are not compatible with the size of the right

side.

Error in mixed_read (line 19)

Initial_Value(row - 5) = data{row, 3};

[Image Analyst]

I literally used the file you attached and it worked fine. So you must be trying it on a different file that has a different format, like the things are in different locations, or there are extra things in the file, or things missing. Attach that file and we'll see how to make the code more flexible, within reason.

The error tells me that what's in data(row, 3) is not a single number like the example you uploaded. What is it? A vector? A matrix?

[NAFTALI HERSCOVICI]

You are right. it was a slightly different file.

Thank you, it works perfect

Answer 3

[NAFTALI HERSCOVICI]

Following question:

I was trying to update the file (for now just writing to another file) with

data(1,99)=textscan('gaga','%s','Delimiter',' ')';

data(2,99)=textscan("beach",'%s','Delimiter',' ')';

data(3,99)=num2cell(17.4);

writecell(data, 'C:\Users\tulih\OneDrive\OneDriveDocuments\MATLAB\TEST2.csv');

and I got this error:

Error using writecell (line 153)

Unsupported cell element of type 'missing'. Convert the element to numeric, logical, string, datetime,

duration, or categorical before writing.

not sure how to fix this

[Image Analyst]

There is at least one cell in the data cell array that contains null. writecell() doesn't like that. Scan the array and if the contents are empty, assign it to something, like a space or whatever you want.

for k = 1 : numel(data)
    if isempty(data{k})
        data{k} = ' ';
    end
end

[NAFTALI HERSCOVICI]

apparently isempty was not enough to replace the "Missing cells"

I have this now running wiht the error on the last line

Error using writecell (line 153)

Nested cell arrays are not supported.

Error in test_read_csv_matlab_central_submition_T (line 35)

writecell(data, 'C:\Users\tulih\OneDrive\OneDriveDocuments\MATLAB\TEST2.csv');

data(1,99)=textscan('gaga','%s','Delimiter',' ')';

data(2,99)=textscan("beach",'%s','Delimiter',' ')';

data(3,99)=num2cell(17.4);

for k = 1 : numel(data)

if ismissing(data{k})

data{k} = ' ' ;

end

if isempty(data{k})

data{k} = ' ';

end

end

writecell(data, 'C:\Users\tulih\OneDrive\OneDriveDocuments\MATLAB\TEST2.csv');

fprintf('Done!\n');

[Image Analyst]

I can't do anything unless you upload the file, right? Did you forget to attach it?

[NAFTALI HERSCOVICI]

i though you gave up on me. let me prepare the files specifics to the question.

I appologize about the confusion.

Will follow up in a few hours

[Image Analyst]

Not sure I'll get to it today. Some football games to watch and guests coming over soon.

[NAFTALI HERSCOVICI]

np, thank you for your patience