need to read a mixed csv file

Question

NAFTALI HERSCOVICI 2022-1-29

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1638780-need-to-read-a-mixed-csv-file

评论： NAFTALI HERSCOVICI 2022-1-31

采纳的回答： Voss

TEST.csv

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Voss 2022-1-30

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1638780-need-to-read-a-mixed-csv-file#answer_884980

编辑：Voss 2022-1-30

在 MATLAB Online 中打开

TEST.csv

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]

4 个评论
显示 2更早的评论隐藏 2更早的评论

NAFTALI HERSCOVICI 2022-1-30

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 2022-1-30

在 MATLAB Online 中打开

TEST.csv

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 2022-1-30

thanks, will try

NAFTALI HERSCOVICI 2022-1-31

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 2022-1-29

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1638780-need-to-read-a-mixed-csv-file#answer_884490

在 MATLAB Online 中打开

TEST.csv

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');

4 个评论
显示 2更早的评论隐藏 2更早的评论

Image Analyst 2022-1-29

编辑：Image Analyst 2022-1-29

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 2022-1-30

You are right. it was a slightly different file.

Thank you, it works perfect

请先登录，再进行评论。

Answer 3

NAFTALI HERSCOVICI 2022-1-30

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1638780-need-to-read-a-mixed-csv-file#answer_884785

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

6 个评论
显示 4更早的评论隐藏 4更早的评论

Image Analyst 2022-1-30

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

NAFTALI HERSCOVICI 2022-1-30

np, thank you for your patience

请先登录，再进行评论。

need to read a mixed csv file

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（2 个）

4 个评论
显示 2更早的评论隐藏 2更早的评论

6 个评论
显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

need to read a mixed csv file

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（2 个）

4 个评论 显示 2更早的评论隐藏 2更早的评论

6 个评论 显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

6 个评论
显示 4更早的评论隐藏 4更早的评论