how to import CSV file (problem with wrong written file)

Hello everyone!

I am using sensor CHR UM7 from CH Robotics, which can maklogs of data. Unfortunately it safe this in CSV file but the type of record is wrong.. why?

comma divide numbers from number but in my file I have:

0,12334,0,0323233 (look in atteched file).

In fact these are not for numbers but two numbers (but with comma). Matlab can recognise this unfrtunately as a 4 numbers..

Does anyone of you has any ideas how to solve this problem?

[dpb]

Star has given workaround, who in their right mind would use the same character as field delimiter and decimal point!!???

I'd just ask if the vendor has a way to configure either the delimiter or (preferably) the decimal point character to fix the problem on the generation side and thus make it go away from the Matlab/user end.

Or, is it using the local locale setting for you region and can you possibly change that if so at least while creating these files?

[Matthew Jameson]

Unfortunately, settings of this file cannot be changed. I was also totally shocked (especially because the price for this sensor is 200$-300$ - so it is not cheap) how the producent can prepare software for saving data in CSV without difference between field delimiter and decimal point.. I am really disappointed but need to work with this data...

[dpb]

Well, that is a bummer, indeed!

I'm no regexp guru, what's the regular expressions pattern to change every other ',' to '.' in the file? Then, as long as it doesn't break the rule of there always being a value with a decimal in each field you could convert the file directly after the fixup.

That assumption, of course, is the same one Star has made in writing the string he then parses; just different way to get to that same text file.

[Stephen23]

@Matthew Jameson: are the files created on a computer which is set to use the decimal comma (e.g. most non-english European languages)? It is possible that the problem is caused by a hard-coded comma being used as the field separator, together with the computers locale setting used to generate the numeric strings.

If so then the whole problem could be easily avoided by switching the computer's locale settings to use the decimal point instead of the decimal comma.

[Matthew Jameson]

Thank you very, very much! That was a language bug in Windows 7! After changing local settings on USA everything works fine!

Thank you so much! It was impossible to solve this problem without your knowledge! :)

Answer 1

[Star Strider]

One approach:

D = xlsread('blaa.csv');
Dc = sprintf('%.0f.%.0f\t%.0f.%.0f\t%.0f.%.0f\t%.0f.%.0f\n', D');           % Formatted Print
Dt = textscan(Dc, '%f%f%f%f', 'Delimiter','\t');                            % Read 
Dm = cell2mat(Dt);                                                          % Convert To Matrix (Output)

This is not as efficient as I would like it to be. It does however have the advantage of working.

[Matthew Jameson]

Thanks for you quick response.

I have turned on this script, but matrixes are empty.

[Star Strider]

My code worked with the file you posted in R2017a.

I have attached ‘Dm’ as a ‘.mat’ file to demonstrate that my code works with your file.

What version of MATLAB are you using?

Answer 2

[per isakson]

I've done an experiment with regular expression on R2016a. I encountered a problem. (?m), 'lineanchors' doesn't work as documented for a string, in which CRLF is used as new line character. It's works with LF as new line character. That's confusing. My solution is to replace fileread by txt2str , which returns a string with LF as new line character.

str = txt2str('blaa.csv');  % read file to string. Ensure LF is used for newline.
xpr = [ '(?m)'              ... match the '^' and '$' at head and tail of a line
    ,   '^'                 ... beginning of line
    ,   '([+-]?\d+)'        ... group dec part of 1st number 
    ,   ','                 ... decimal separator
    ,   '(\d+,[+-]?\d+)'    ... group frac part of 1st number, list sep 
                            ...     and decimal part of 2nd number
    ,   ','                 ... decimal separator
    ,   '(\d+,[+-]?\d+)'    ... group frac part of 2nd number, list sep 
                            ...     and decimal part of 3rd number
    ,   ','                 ... decimal separator
    ,   '(\d+,[+-]?\d+)'    ... group frac part of 3rd number, list sep 
                            ...     and decimal part of 4th number
    ,   ','                 ... decimal separator
    ,   '(\d+)'             ... group frac part of 3rd number, list sep 
    ,   '[ ]*$'             ... trailing blanks and end of line
    ];                         
buf = regexprep( str, xpr, '$1.$2.$3.$4.$5' );   % insert decimal points
%
cac = textscan( buf, '%f%f%f%f', 'Delimiter',',', 'CollectOutput',true, 'Headerlines',1 );
num = cac{1,1};

inspect the result

>> num(1:3,:)
ans =
   12.9086   -0.0277   -0.0320   -0.8833
   12.9668   -0.0275   -0.0316   -0.8838
   13.0281   -0.0270   -0.0315   -0.8842

>> whos num
  Name        Size            Bytes  Class     Attributes
  num       234x4              7488  double

[Walter Roberson]

per:

fileread() and then delete the CR would work.

It is true that lineanchors only expect newline. But you could use '\r?$' or '(?=\r?)$'

[per isakson]

Walter

Yes and yes, but

• "delete the CR" adds clutter to the code and takes a bit of time, since the size of the string changes.
• "use '\r?$' or '(?=\r?)$'" adds clutter

"It is true that lineanchors only expect newline" Yes, that's true. And the same is true with 'dotexceptnewline'. IMO: this is a design mistake, since

• Matlab users typically don't want to bother about new line characters.
• PCRE (e.g. https://regex101.com/) handles CRLF and LF the same way (in these cases).
• I cannot think of any situation, in which the Matlab way offers an advantage.

The difference between txt2str and fileread is

[fid, msg] = fopen( filespec, 'rt' );

in place of

[fid, msg] = fopen(filename);

Answer 3

[Matthew Jameson]

THANK YOU ALL SO MUCH FOR YOUR HELP!

I will test this and let you know.

I am so happy that people here are so helpful and so clever in matlab issues.