Merging Exported data into one CSV file

[Felix]

Sorry for missing this earlier,
Not really sure without trying it myself. You want the net result of that to be a string formula that excel will interpret correctly.
Apparently excel interprets "1/1/1970" as = 25569. So replace your magic string with that. The -5/24 represents the timezone offset, if you log locally (your data is where you are) then you can eliminate that.
So your formula would be:

ts / (1000*60*60*24)+25569

Another thing is you are not logging this to the dbLog, instead this is done on this line in index.html:

Code: [Select]

dataString = logItem.t + ',' + logItem.v;

So to add a new column with the excel date, modify it to this:

Code: [Select]

dataString = logItem.t + ',' + (logItem.t/(1000*60*60*24)+25569) + ',' + logItem.v;

Try that, let me know what you get...

[EnMon]

Hello Felix,

Sorry for my delayed response, been working to get myself ready for a milestone.

Thanks for your response, I tried the suggested edit and it works!!!

However, it only works for individual metric and doesn't work for the new 'Export All Data" feature you recently added.

I'm fine with this for now, but just wanted to let you know the result of my implementation.

Thank you so much.

[Felix]

My bad, my head's in the clouds and I forgot this is for the aggregated CSV not the individual one. BUT once I put this in I will definitely add the excel TS to both places to make it easy for people.

Anyway you will need to update one line and add one line, indicated line below, in the code I attached to this thread (it's not released yet so add it to your local index.html):

Code: [Select]

      socket.on('EXPORTNODELOGSCSVREADY', function(rawData) {
      var sets = rawData;
      var csv = 'data:text/csv;charset=utf-8,unix_timestamp(ms),excel_timestamp'; //header start       <<<< update this line

      var mergedData = {};
      var setCount=0;

      rawData.sets.forEach(function(set, setIndex) {
        set.data.forEach(function(point, index){
          var nullPaddedArray = []; //left padding
          var i=0;
          while(i++ < setIndex) nullPaddedArray.push(null);
          (mergedData[point.t] = mergedData[point.t]||nullPaddedArray).push(point.v);
        });
        Object.keys(mergedData).forEach(function(key) {
          if (mergedData[key].length-1<setIndex) mergedData[key].push(null); //right padding
        });
        csv+= ','+set.label;
      });
      csv+='\n'; //header end

      Object.keys(mergedData).forEach(function(key) {
        csv += key;
        csv += ',' + (key/(1000*60*60*24)+25569);              <<<< add this line
        for(var i=0; i<mergedData[key].length; i++)
          csv += ','+(mergedData[key][i]||'');
        csv+='\n';
      });

Tested to produce something like this, you still need to format the new column as DATE in excel: