Problem using xlsread on .csv files

Hello,

When I try to read a .csv file with the function xlsread, it returns an array of only one column.

I use the following code:

[~,~,Data] = xlsread('C:\path\file.csv')

with MATLAB R2014a in windows 8.1, and a csv file like this:

User,Date,Time,Value1,Value2,Value3
1,2015/04/22,16:00:00.000000,,Blue,0
1,2015/04/22,16:00:00.000000,0,Red and yellow,-1

Matlab seems not to recognize the separator.

I have tried it with other configurations (R2013b in Windows 7, R2014a in Windows 8) and the same file and same code: it works correctly.

So I don't understand why it doesn't work on my computer.

Can someone help me to solve this problem ?

Thanks in advance,

Mickaël

[Guillaume]

Note that xlsread delegate the parsing of the file to Excel (even for csv files), so the first question is: Is the same version of Excel installed on all three machines?

If Excel is not installed, then it switch to basic mode and may result in a different output.

Answer 1

[Titus Edelhofer]

Hi,

adding to Guillaume's comment: could it be that your Windows versions are once english and once something else (German, French, ...)? Or your Excel? This would explain that once the "," is treated as separator correctly, once not ...

For a somewhat simple file like this textscan should be sufficient as well, something like

fid = fopen('C:\path\file.csv', 'rt');
data = textscan(fid,'%s %s %s %f %s %f', 'headerlines', 1, 'delimiter', ',');
fclose(fid);

Titus

Answer 2

[Mickaël BERNINI]

Thank you for these answers.

The problem is due to the language. I have a similar problem when I open the csv file with excel. My computer and excel are in French. If I change the separators to ';' the file is read correctly.

I will look for an anwser to that.

Otherwise, if I use textscan, the return value is a 1x6 cell where each is a 2x1 cell. The code is already done, and I don't want to modify it. I need to work on a 3x6 cell with the headers. I will adapt your code and tell you if it is good.

Mickaël

[Titus Edelhofer]

Add the line

[num2cell(data{1}) data{2} data{3} num2cell(data{4}) data{5} num2cell(data{6})]

to create one cell array again ...

Titus

[Mickaël BERNINI]

This solution is good but not adapted when we have many variables (41 in my case) I did a loop to have it.

But I have found how to solve the separator problem: I changed them in the Windows parameters and now it works.

If someone has the same problem, the path is: Control Panel > Languages > Modify date, hour, number formats > Formats > Extra parameters.

Thanks for your help.

Mickaël

Answer 3

[Babak Tootoonchi]

I am using Matlab 2017b. I have a .csv file, and I am able to read it using xlsread in windows. xlsread works well in windows for csv file.

When I tried to read .csv file in Linux, xlsread does not work. the error message is: Unable to read XLS file .csv. File is not in recognized format.

I tried to use csvread to read the .csv file in Linux, but csvread does not work in Linux.

Error using dlmread (line 147)
Mismatch between file and format character vector.
Trouble reading 'Numeric' field from file (row number 2, field number 1) ==> ff79 009d0055 fff30005 ffe3ffbc 0123ff73 0012008d 00f50020 ff9800b9
ff9dffa1 0034ffd9 00190014 0073ffce 002dff25 002f0014 ff27ff0e 0046ffaa\n

Error in csvread (line 48)
    m=dlmread(filename, ',', r, c);

Could you please help me on this error?

[Walter Roberson]

csv files that have blank delimited strings often need to use "" around the strings. There is no standard in this matter.

What you posted looks more like a blank delimited field than a comma delimited field, suggesting that dlmread with ' ' or readtable or textscan would be better for reading it.