need to read a mixed csv file
Show older comments
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
Accepted Answer
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
4 Comments
NAFTALI HERSCOVICI
on 30 Jan 2022
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')
% 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.'));
% make sure inputs can still be read, ignoring outputs:
read_inputs('TEST_with_output.csv')
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
on 30 Jan 2022
NAFTALI HERSCOVICI
on 31 Jan 2022
More Answers (2)
Image Analyst
on 29 Jan 2022
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 Comments
NAFTALI HERSCOVICI
on 29 Jan 2022
NAFTALI HERSCOVICI
on 29 Jan 2022
Image Analyst
on 29 Jan 2022
Edited: Image Analyst
on 29 Jan 2022
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
on 30 Jan 2022
NAFTALI HERSCOVICI
on 30 Jan 2022
0 votes
6 Comments
Image Analyst
on 30 Jan 2022
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
on 30 Jan 2022
Image Analyst
on 30 Jan 2022
I can't do anything unless you upload the file, right? Did you forget to attach it?
NAFTALI HERSCOVICI
on 30 Jan 2022
Image Analyst
on 30 Jan 2022
Not sure I'll get to it today. Some football games to watch and guests coming over soon.
NAFTALI HERSCOVICI
on 30 Jan 2022
Categories
Find more on Spreadsheets in Help Center and File Exchange
on 29 Jan 2022
on 31 Jan 2022
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
