How to read csv file containing text and numbers in the same cells
20 views (last 30 days)
Show older comments
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!
0 Comments
Accepted Answer
dpb
on 25 Jan 2021
Edited: dpb
on 25 Jan 2021
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... :)
More Answers (0)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!