Mixed character Tab delimited csv file read in

17 views (last 30 days)
radha soni
radha soni on 18 May 2021
Commented: radha soni on 20 May 2021
Hi I have a CSV file which has two tab seperated columns (according to excel).
I'm looking for a way to read this in. The problem is that there are a number of 'sections' within the file that follow on from one another, each with their varying number of header lines (please see screenshots).
I'd like to just read in the numeric data and be able to seperate these into columns as at the moment because of the text portions I'm unable to successully use importdata/readtable etc functions. I have also tried [ndata, text, alldata] = xlsread(filename); but the numeric data is read in as text as it is delimited.
  2 Comments

Sign in to comment.

Accepted Answer

Stephen
Stephen on 19 May 2021
fm1 = '#%s%[^\n\r]';
op1 = {'CollectOutput',true, 'Delimiter',':'};
fm2 = '"%f%f"';
op2 = {'CollectOutput',true};
hdr = {};
dat = {};
[fid,msg] = fopen('Lunghypo.csv','rt');
assert(fid>=3,msg)
while ~feof(fid)
hdr(end+1) = textscan(fid,fm1,op1{:});
dat(end+1) = textscan(fid,fm2,op2{:});
end
fclose(fid);
Checking:
hdr{1}
ans = 6×2 cell array
{'PatientName' } {'LungHypo005' } {'PatientId' } {'LungHypo005' } {'Dosename' } {'Plan dose: 30Gy in 5 (CT 1)'} {'RoiName' } {'External' } {'Roi volume fraction outside grid'} {'0%' } {'Dose unit' } {'cGy' }
dat{1}
ans = 402×2
0 100.0000 0 100.0000 0 99.5000 0 99.0000 0 98.5000 0 98.0000 0 97.5000 0 97.0000 0 96.5000 0 96.0000
hdr{end}
ans = 3×2 cell array
{'RoiName' } {'PRVcord'} {'Roi volume fraction outside grid'} {'0%' } {'Dose unit' } {'cGy' }
dat{end}
ans = 402×2
0 100.0000 0 100.0000 0 99.5000 0 99.0000 0 98.5000 0 98.0000 0 97.5000 0 97.0000 0 96.5000 0 96.0000
  9 Comments
radha soni
radha soni on 20 May 2021
Thank you - that's VERY helpful I've learnt a lot. Unfortunately the export is from a piece of commercial software I have no control over, But i can make a note on the matlab script for users to not open export files in excel ensure consistent formatting, Thanks again for all your help.

Sign in to comment.

More Answers (2)

Jeremy Hughes
Jeremy Hughes on 19 May 2021
Ahhh, that's because the data is in quotes.
readmatrix assumes quoted data is meant to be preserved, so the only way to get that data out would be as text:
A = readmatrix(filename,"OutputType","string");
Then parse the lines separately with textscan as in Stephen's answer. (you'd remove the double-quote from the format)

Jeremy Hughes
Jeremy Hughes on 18 May 2021
Just a note: CSV means comma-separated-values, but if this is TAB separated, it's not really CSV.
If the data in the sections are important, I suggest you use READCELL, as that will bring everything in as the right type.
C = readcell(filename,"Delimiter","\t")
If you don't care about the headers, then readmatrix might be better, and you can skip those headers since they all seem to start with '#':
A = readmatrix(filename,"CommentStyle","#","Delimiter","\t")
  2 Comments
Jeremy Hughes
Jeremy Hughes on 19 May 2021
Ahhh, that's because the data is in quotes.
readmatrix assumes quoted data is meant to be preserved, so the only way to get that data out would be as text:
A = readmatrix(filename,"OutputType","string");
Then parse the lines separately with textscan as in Stephen's answer. (you'd remove the double-quote from the format)

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by