Mixed character Tab delimited csv file read in

17 次查看（过去 30 天）
radha soni
radha soni 2021-5-18
评论： radha soni 2021-5-20
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.

请先登录，再回答此问题。

采纳的回答

Stephen23
Stephen23 2021-5-19
Ran in:
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 个评论
显示 7更早的评论
Stephen23
Stephen23 2021-5-19
编辑：Stephen23 2021-5-19
"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 2021-5-20
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.

请先登录，再进行评论。

更多回答（2 个）

Jeremy Hughes
Jeremy Hughes 2021-5-19
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 2021-5-18
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 个评论
radha soni
radha soni 2021-5-19
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 2021-5-19
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)

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Text Files 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by