Reading timestamp in ms from csv, showing in scientific format in Matlab

0
votes

I have a csv file including a timestamp column. In Notepad, in Excel and in Matlab the value appears in scientific format, e.g. '1,44532E+12'. As such, the value is highly inaccurate. In Excel I can calculate the difference between 2 values and get a reasonable difference. Thus, the scientific format in Excel is just the format. The actual cell contents are '1445322672767'.

I'm puzzeled why the value appears as '1,44532E+12' in Notepad. But that's not my problem. I'm trying to analyse the data in Matlab, but I am unable to. I've tried textscan, readtable and csvread. But I'm unable to get the value other than as a string. With str2num this can be changed to a double, but only with 6 digit accuracy (which is completely useless).

Any suggestions?

1
excelmatlabcsvscientific-notation
Could you share a link to your file? What is the original data format? Do you start in Excel and write out a csv file from there? Or do you have a csv file that you read into Excel? - Schorsch
It sounds like the original file comes from Excel and is exported in the limited notation you describe. Be advised, that once exported, precision information is permanently lost it is not a matter of textscan etc. or the program you use to open the exported file. You need to export with full precision from the start. See this question @ microsoft - Dev-iL
Thanks so far! The file comes from an Android app Wahoo Fitness. There is one for pedal cadence, and one for wheel revolutions. Here is the file of pedal cadence: dl.dropboxusercontent.com/u/40762003/… - Don Joe
Found it! I opened them with Excel, and then I suppose I saved them accidently in .csv format. That actually changed the contents from '1445322672767' to '1,44532E+12'. Thanks a lot! Your comments made me find it! :) - Don Joe

1 Answers

1
votes

I know this is an old thread but if someone need the info. On the csv files I get from Wahoo on HR training and GPS data the timestamp is Unix with as I suppose hundreds of a second added, 13 digits. So I extract the first 10 to convert to Windows time.

=LEFT(A1, 10) / 86400 + 25569 + 2/24

But Wahoo is moving away from TCX and CSV files and bugs in them would not be a priority to fix. .FIT files is what "everybody" use now. I've been in contact with them. I'd just finished making a automation for TCX to Excel but in the latest Android update HR and other data was messed up and they won't fix it.

"The CSV files and the TCX will still be around just not as supported like the Fit files will be but the CSV will do the trick and will stick around for quite some time I am sure."

But in the thisisant.com SDK (Java part) there is drag and drop batch files to convert .fit binary's to csv files. Require of course that Java is installed.