Mixed character Tab delimited csv file read in

23 visualizzazioni (ultimi 30 giorni)
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 Commenti
Stephen23
Stephen23 il 18 Mag 2021
@radha soni: please upload a sample file by clicking the paperclip button.
radha soni
radha soni il 19 Mag 2021
Sorry i've inserted the full file. thank you

Accedi per commentare.

Risposta accettata

Stephen23
Stephen23 il 19 Mag 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 Commenti
Stephen23
Stephen23 il 19 Mag 2021
Modificato: Stephen23 il 19 Mag 2021
"i've tried to run another file with the same formatting through as is unchanged ..."
Nope, they are different. Do NOT rely on Excel to compare file formats.
"... and it is getting stuck in the loop for some reason, do you have any idea why at all?"
Because the format is not the same. Your original file Lunghypo.csv has data lines that are actually saved as strings in double quotes (which is incredibly awfully bad file creation by whatever app that created it):
..
"0.000 100.000"
"0.000 100.000"
"0.000 99.500"
..
Because of this very very very poor hiding of numeric data inside string delimiters I added the literal double quotes to the format string so that TEXTSCAN could correctly handle them.
Now your new file LtBreastRS.csv has data lines without double quotes:
..
0.000 100.000
0.000 100.000
0.000 99.500
0.000 99.000
0.000 98.500
..
Note:
  • use an actual text editor (e.g. notepad++) to look at the format of CSV files, as Excel does a lot of data mangling before showing you anything. It appears that every time you open in Excel and save you are changing the formatting.
  • requiring that you can import data from files with different formats using the same code makes your code more complex. The best solution is to actually stick to just one format. I recommend that numeric data should not be hidden inside strings,then you can simply remove the double quotes from the format string:
fm2 = '%f%f';
radha soni
radha soni il 20 Mag 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.

Accedi per commentare.

Più risposte (2)

Jeremy Hughes
Jeremy Hughes il 19 Mag 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 il 18 Mag 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 Commenti
radha soni
radha soni il 19 Mag 2021
Hi Jeremy thanks for your resposnse, yes sorry it's not really a CSV it's just saved as .csv so that it's matlab accessible. When I use the text to columns wizard in excel it indicates that the data is tab delimited
i've just inserted the full file. that's right the headers do all start with # so it seems like readmatrix should work but when i use the function, A contains NaN for all cells
Jeremy Hughes
Jeremy Hughes il 19 Mag 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)

Accedi per commentare.

Community Treasure Hunt

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

Start Hunting!

Translated by