Import data file with comma decimal point

Hi !

I have a problem with importing a data file, and I hope someone in here can help resolve it. The file I'm trying to import looks like this (headers not included!):

01-11-2012 00:00:00  52,0837766666504  -2752,54346603125  0  -4280  1  1  2  2  0  0
01-11-2012 00:00:10  55,0510711669922  -2052,33987375  0  -9240  1  1  2  2  0  0
01-11-2012 00:00:20  58,0332733895508  -2952,54345703125  0  -3240  1  1  2  2  0  0

A total of 11 columns separated by tab, stored in a .txt file.

The problem is:

• 1st column has date and time, which Matlab seems to have troubles with
• 2nd and 3rd uses comma decimal instead of a dot, which makes Matlab see it as text

I've tried to load the file with the function "load" - which isn't possible because of the mix of data and text. Then I tried using "textscan" which results in an array inside an array, with completely messed up data (all headers in a 1x1 array fx).

Using the following code:

filename = 'test.txt';
delimiterIn = ' ';
headerlinesIn = 1;
A = importdata(filename,delimiterIn,headerlinesIn);

Produced a 1x1 array with all the headers and nothing else (I also tried /t for the delimiter, which didn't work at all).

So this is the point where I hope someone in here can help :) Thanks in advance, and a Merry Christmas to you all! :)

Answer 1

[Azzi Abdelmalek]

fid = fopen('file.txt');
out=textscan(fid,'%s  %s  %s  %s   %s  %s  %s  %s %s  %s  %s  %s ')
fclose(fid);

Then you can arrange your data

[J.]

Thanks for the reply. It seems to almost be what I need. I had already tried textscan, but with the added: 'delimiter', '/t' One problems left tho - date and time are separated into two arrays. I'm not familiar with how Matlab handles time stamps.

[Azzi Abdelmalek]

You have just to concatenate them

[Azzi Abdelmalek]

a=out{1};b=out{2};
for k=1:numel(a)
  dat{k}=datenum(strcat(a(k,:), b(k,:)))
end

Answer 2

[per isakson]

This functions replaces all "," by ".". Make a copy of your file first. Import in next step.

    function    comma2point_overwrite( filespec )
    % replaces all occurences of comma (",") with point (".") in a text-file.
    % Note that the file is overwritten, which is the price for high speed.
        file    = memmapfile( filespec, 'writable', true );
        comma   = uint8(',');
        point   = uint8('.');
        file.Data( transpose( file.Data==comma) ) = point;
        %%%delete(file)
    end

Not sure whether it works with unicode:(

.

Correction:

The statement

    delete(file)

is in error. Remove it. memmapfile is a value class and has no delete method. Now it works here (R2012a,Win7). Demo:

    >> comma2point_demo
    M =
      123.4000  567.8000
      123.4000  567.8000
      123.4000  567.8000
    >>

where

    %%comma2point_demo
    copyfile( 'cssm.txt', 'comma2point.txt' )
    comma2point_overwrite( 'comma2point.txt' )
    M = dlmread('comma2point.txt')

and cssm.txt contains

    123,4 567,8
    123,4 567,8
    123,4 567,8

[J.]

Couldn't get it to work, thanks for helping tho. I think I get it fixed by using strrep and writing the new string directly to the array. Converting it to double did I do with str2double and then writing it to a new array.

[per isakson]

Fixed! See above.

[J.]

Superb! It's ages faster than doing strrep for each column! Thanks for the help.

[Rafael Tavares]

worked as a charm! greetings!

[Beatriz Ruiz]

Hello, I've tried this code and it works perfectly but I don't understand this line:

file.Data( transpose( file.Data==comma) ) = point;

Why do you need to use transpose? Thanks!

[Anna K. Turhal]

Thank you so much, per isakson. This is such an elegant solution, works really well!

[Angela Schmidt]

Fantastic: thanks a bunch Mr. Per Isakson !

[per isakson]

@Beatriz Ruiz

"Why do you need to use transpose?"

With R2018b transpose() is obviously superfluous now and most likely it was superfluous back in 2012. However, it doesn't hurt. Had I for some reason wanted to reshape file.Data to a row I ought to have used reshape().

[Mustafa Calcuttawala]

@per isakson You the real mvp my man. Your code is a charm. However, when you try and open the file in excel while the memmapfile is still defined and present in the workspace, excel shows an error that the file can't be edited and forces you to open the file as read only. This is probably because the memmapfile has not been removed from the workspace. The statement

delete(file)

doesn't help removing the file. This is the error that comes up:

Error using delete

Name must be a string scalar or character vector.

To remove the memmapfile variable, simply use the command

clearvars file

And problem solved.

[per isakson]

Thanks for your comment, however I don't fully understand your description.

"[...] while the memmapfile [object] is still defined and present in the workspace" Which workspace do you refer to?

The data file is obviously locked for writing as long as an memmapfile object of that data file exists. (I fail to find a page in the documentation to link to.) Thus, when comma2point_overwrite() is running one cannot open the data file for writing in another program, e.g. Excel. The lifecycle of the memmapfile object ends when comma2point_overwrite() ends and at this point in time the data file is released.

Your description makes me think that you have copied the body of the function comma2point_overwrite() to a script. In that case clearvars is needed to destroy the memmapfile object and thereby release the data file.

Answer 3

[Walter Roberson]

In fairly recent versions of MATLAB, readtable() can now process text files that use comma as decimal point. If it does not automatically detect the comma, then use the option 'decimal', 'comma'

[Johannes Kalliauer]

Thanks the correct syntax is:

T = readtable(filename,'decimal', ',')

or as explained on https://www.mathworks.com/help/matlab/ref/readtable.html

T = readtable(filename,'DecimalSeparator',',')

however 'comma' does not work. I noticed it does not work in combination with opts .

[Jon]

This is a fantastic development! Thanks so much for providing the update

Answer 4

[Hemanth Kumar Padmanaban]

thank you so much for These codes per isakson

you helpe me solve a huge data conversion Problem in my University Project

thank you so much