Read csv files with double quotes and comma delimiter, containing doubles and strings (arbitrary number of rows and columns)

Question

I am reading csv files with the following format:

Header:,Date,Time,"MC2_Y241_TightnessPressValve","MC2_Y243_PressingValve""
Data,2015-09-16,15:41:52;781,"780.000000","0.0034"
Data,2015-09-16,15:41:52;791,"790.000000","0.1255"
Data,2015-09-16,15:41:52;801,"800.000000","1.5123"

Atm I am using fgetl(fid) to find the headers and all the dates and times. Then I use the knowledge of which rows and columns are containing doubles, to be able to use csvread() for fast reading. However, to use csvread(), I must first remove the quotes. I am currently doing this with a powershell script within matlab but it is too time consuming as I will need to read files of +200Mb.

Note: textscan with '%q' cannot be used for two reasons: 1) I want all doubles to be read as doubles immediately (converting is too time consuming). 2) The files contain various numbers of rows and columns.

This is for a standalone application.

I truly appreciate all help, I have spent countless hours on making this efficient.

various numbers of rows and columns? It's ok to have any number of rows. but you need to know the format of the columns. — Lee
I could use fgetl and check the string for the number of delimiters (commas) and findout how many columns I have. But where do I go from there? — jkazan
read the header line f = fopen(file,'r'); h = f.getl(f);, split by delimiter cols = regexp(h, ',', 'split');, then you get a cell string array containing the name of each column. you can know the number of column by ncol = length(cols); From the value of each item of cols, you can know what this column is as well as the datatype. then you can construct a format string according that. — Lee
read into a string s = fileread(you_file); then use textscan(s, format). if the quote is a trouble, use s = strrep(s,'"','') to remove the quotes. — Lee
read the help doc of textscan, especially the formatSpec part — Lee

jkazan jkazan · Accepted Answer · 2016-03-15T08:12:17

I though I should post an answer, in case anyone else has the same problem. Acknowledgements to Lee who helped me with this!

% Remove quotes
fid = fopen('temp.csv','w');
s = fileread('myFile.csv');
s = strrep(s,'"','');
fprintf(fid,s);
fclose(fid);

% Open new, quote free, file
fid = fopen('temp.csv','r');

% Get Headers
Headers{1} = fgetl(fid);
Headers = textscan(Headers{1},'%s','Delimiter',',');
Headers = Headers{1};
Headers = Headers(3:end);

% Get date and time
lineIndex = 1;
nextLine = fgetl(fid);
time{lineIndex} = nextLine(6:28);
lineIndex = lineIndex + 1;
nextLine = fgetl(fid);
while ~isequal(nextLine,-1) && ~isempty(nextLine)
    time{lineIndex} = nextLine(6:28);
    lineIndex = lineIndex + 1;
    nextLine = fgetl(fid);
end

% Get doubles
Data = csvread('temp.csv', 1,3);
fclose(fid);

% Convert time with datenum
Time = datenum(time,'yyyy-mm-dd,HH:MM:SS;FFF');

% Put all data in one matrix
Data = [Time, Data];

Read csv files with double quotes and comma delimiter, containing doubles and strings (arbitrary number of rows and columns)

1 Answers