How to read csv file containing text and numbers in the same cells

Hi everyone,

This is my first question I ask here, as after looking at the other answers I did not find the solution to my problem.

I have a number of .csv files to be read and I attached one of them here as an example.

What I would like to do is the following:

• Open the csv files (I managed to do this, but I obtained a huge table containing all the information as a text)
• Ask the user in which rows and columns are necessary measurements to be stored in an array
• Read the required cells skipping the ' ' and the spaces.
• In the same cell, convert the mV into an e-3, the uV into a e-6 and so on ...
• Iterate this cycle for all the asked measurements and for all the .csv files

Thank you in advance for your help!

Answer 1

[dpb]

Wowsers! That's a nice file format for reading visually, but surely a pain to postprocess. Can you not set the machine up to output the data in engineering units normalized and in numeric fields to be able to import directly? Of course, you may have a bunch of data already collected that would be difficult or impossible to reproduce although perhaps the instrument also stores the data and could spit it out again in different format?

Anyway, it's possible, just quite a lot of effort...here's a start using the MATLAB table object...

optR01=detectImportOptions('R01.10.csv','Delimiter',',', ...            % tell it is a comma-delimited file
                                        'NumHeaderLines',4, ...         % skip the header lines 
                                        'ExtraColumnsRule','ignore');   % ignore all the trailing columns
optR01.VariableNames=regexprep(optR01.VariableNames,'_','');            % so don't have all the underscores to type
tR01=readtable('R01.10.csv',optR01);                                    % and bring in the table
VU=split(tR01.Mean);                                                    % split the means column
isMilli=startsWith(VU(:,2),'m')                                         % find the mV, mS locations
tR01.MeanVals=str2double(VU(:,1));                                      % convert to numeric, new variable
tR01.MeanVals(isMilli)=tR01.MeanVals(isMilli)/1000;                     % and scale the ones needed
tR01.MeanUnits=categorical(VU(:,2));                                    % and make units column

The above is specific operations on the Mean variable; the same set of operations needs to be carried out for all the numeric columns that have the combination of values and units together; making a function that has the basic operations in it that can be called passing each variable in turn is the obvious way to handle the whole file.

The units conversion also needs to be generalized to include all the scale factor ids and multipliers and use a lookup table to find and set a vector of multipliers based on index into which multiplier goes on each row.

One can then also either delete the original combined-data columns or save the new ones to a new file in the desired order; this right now just tacks them on to the end as new variables. One can rearrange the order inside a table with an indexing column array when done or pick them out by name in a specific order for final table.

The above operations on the sample file returned the following for the subset of variables worked on above to illustrate:

>> tR01(:,{'Mean','MeanVals','MeanUnits'})
ans =
  8×3 table
        Mean         MeanVals     MeanUnits
    _____________    _________    _________
    {'200.80 V' }        200.8       V     
    {'22.613 mV'}     0.022613       mV    
    {'200.99 V' }       200.99       V     
    {'3.7514 ms'}    0.0037514       ms    
    {'25.886 ms'}     0.025886       ms    
    {'22.987 mV'}     0.022987       mV    
    {'147.81 mV'}      0.14781       mV    
    {'1.4309 mV'}    0.0014309       mV    
>> 

ERRATUM: I just realized overloked the need to also fixup the Units value for the final units variable to remove the indicator leaving only the unit as 'V' or 's' I'll leave as "Exercise for Student" at this point... :)

[Federica Costa]

Dear dpb,

Thank you very much! :) That is really helpful and I will try soon to implement it and extend the code to all the other .csv files.