Import data file with comma decimal point

350 views (last 30 days)
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! :)

Accepted Answer

Azzi Abdelmalek
Azzi Abdelmalek on 23 Dec 2012
Edited: Azzi Abdelmalek on 23 Dec 2012
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
  3 Comments
Azzi Abdelmalek
Azzi Abdelmalek on 23 Dec 2012
a=out{1};b=out{2};
for k=1:numel(a)
dat{k}=datenum(strcat(a(k,:), b(k,:)))
end

Sign in to comment.

More Answers (3)

per isakson
per isakson on 23 Dec 2012
Edited: per isakson on 4 Aug 2020
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
  10 Comments
Mustafa Calcuttawala
Mustafa Calcuttawala on 22 May 2020
Edited: Mustafa Calcuttawala on 22 May 2020
@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
per isakson on 23 May 2020
Edited: per isakson on 23 May 2020
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.

Sign in to comment.


Walter Roberson
Walter Roberson on 4 Aug 2020
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'
  2 Comments
Johannes Kalliauer
Johannes Kalliauer on 20 Jul 2022
Thanks the correct syntax is:
however 'comma' does not work. I noticed it does not work in combination with opts .
Jon
Jon on 26 Sep 2022
This is a fantastic development! Thanks so much for providing the update

Sign in to comment.


Hemanth Kumar Padmanaban
Hemanth Kumar Padmanaban on 14 Dec 2015
Edited: Hemanth Kumar Padmanaban on 14 Dec 2015
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

Categories

Find more on Large Files and Big Data in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!