Mixed character Tab delimited csv file read in
10 次查看(过去 30 天)
显示 更早的评论
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).
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/621713/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/621718/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/621723/image.png)
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 个评论
采纳的回答
Stephen23
2021-5-19
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}
dat{1}
hdr{end}
dat{end}
9 个评论
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';
更多回答(2 个)
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)
2 个评论
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 个评论
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!