Extract very small numbers from a .DAT file with all its decimals and export them to an excel file

Hello everybody,

I'm very desperate already since I've been browsing through the community for days without finding a solution for my problem.

I want to only import the marked numbers (see attached png) from a .dat file (which I can't upload here). This means I need to get rid of the 16 headlines before the data and the first column of the data. The second column (red framed on png) is supposed to be turned into a cell array containing the numbers with all its decimals and export it to an excel spreadsheet.

The main problem I have is that matlab rounds a number like -3.999999999999999819e-06 to -4.000000...e-06 instead of keeping all its decimals. Find the code I've been using so far in the attached .m file.

I would appreciate a lot if you guys could help me out with the coding. I am not experienced but I need to automize this data extraction.

Thanks a lot!

Cheers,

Sven

[Stephen23]

"The main problem I have is that matlab rounds a number like -3.999999999999999819e-06 to -4.000000...e-06 instead of keeping all its decimals."

In fact when that text value is imported this is its exact value** in MATLAB memory:

>> num = dlmread('test.txt');
>> num2strexact(num)
ans =
-3.9999999999999998189924473035450347424557548947632312774658203125e-6

The main problem is most likely confusion about how numeric values are displayed versus how they are stored in memory.

** download here: https://www.mathworks.com/matlabcentral/fileexchange/22239-num2strexact-exact-version-of-num2str

Answer 1

[Ameer Hamza]

This issue is related to the finite precision of how the floating-point numbers are represented in computers. The double type in MATLAB can only hold numbers to 16 decimal places. Anything beyond that is theoretically impossible to store in a double variable accurately. In such a case, you can use variable precision arithmetic. Note that this can be many times slowers as compared to the numeric datatype, but if you want this level of accuracy, then variable precision is the only solution.

file = fopen('filename.dat');
data = textscan(file, '%s %s', 'HeaderLines', 16);
fclose(file);
 
result = vpa(data{2});

[Sven Krasel]

Hi Ameer,

thanks a lot! This works perfectly for me. Can you also show me how to export this to an excel file?

Best,

Sven

[Stephen23]

"Can you also show me how to export this to an excel file?"

Note that Excel also uses double type for its numeric values, so it will use exactly the same precision values in its calculations a MATLAB does. Those trailing digits are essentially meaningless noise, literally beyond the documented precision of the double class.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

Not only that, but I very much doubt that your data are anywhere near accurate to the precision that you are requesting: here is a highly amusing discussion on exactly the same topic: https://www.mathworks.com/matlabcentral/answers/322415-issue-in-reading-gps-info-from-jpg-images#comment_425323

[Ameer Hamza]

As pointed out by Stephen, exporting them to excel in numeric form is of no use. Excel does not support variable precision, and it also stores the numeric value in a double-precision format. If you just want to save these values in excel file in the form of characters, then you can try

file = fopen('temp.txt');
data = textscan(file, '%s %s', 'HeaderLines', 16);
fclose(file);

writecell(data{2}, 'test.xlsx');

Note that calculations beyond the double-precision will still not be possible. Maybe it is possible to write your own function using VBA to support variable precision arithmetic on strings, but I don't have any experience with it.

[Sven Krasel]

Hi Stephen,

first of all thank you for your replies! :-)

I see where you up to. Of course I don't need this extremely high degree of accuracy but I just want to make sure that I at least copy paste the exact values from one file to another.

For example: One file contains this number -3.999999999999999819e-06. When I copy paste it directly to an excel sheet excel returns this number -3.99999999999999E-06. When I first extract this number with matlab and then export it to excel it is rounded to -4.00000...e-06. Of course it doesn't make any difference but I just want to keep calculating with the same numbers.

Best,

Sven

[Stephen23]

Copy-and-paste is NOT a reliable way to echange numeric data if you want to maintain precision. Just like with MATLAB (and almost every other numeric computation application in existence), how Excel displays numeric data in NOT the same as what numeric data is stored (either in memory or in a file). I saved your example value in a .xlsx workbook, and this is what is stored in the XML file itself:

but when I copy-and-paste the value directly from the spreadsheet (which copies something what Excel displays) this is what I got:

"When I first extract this number with matlab..."

You did not explain exactly what "extract" means, but clearly it was not numerically robust.

Trying to "fix" how MATLAB imports/exports floating point numeric data is a red-herring. You time would be much better spent on reading about numeric error propagation and floating point numbers.

"Of course it doesn't make any difference but I just want to keep calculating with the same numbers."

Then you really need to avoid Excel and store your data in a reliable format which does not change the data precision, e.g. a binary format such as .mat or something similar.

[Sven Krasel]

Hello Stephen, hello Ameer,

@Stephen:

The solution Ameer provided is already satisfying enough for me. Your argumentation is totally right but unfortunately I don't have the time to read too much in detail about this. Thank you anyway for your time and your replies. :-)

@Ameer:

Thank you very much. It's a very simple and straight forward solution.

Cheers,

Sven