Author Topic: Merging Exported data into one CSV file  (Read 16074 times)

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Merging Exported data into one CSV file
« on: February 10, 2017, 06:33:44 PM »
Hello all,

Kudos to Felix for giving us such a wonderful Gateway app, it really fantastic!

Just curious, Is there a way to merge all the data coming from one node into a single CSV file as opposed to the current way of retrieving the parameters separately?
For instance, I'm using the weather shield to retrieve Temp, Humidity and battery voltage data and I want to be able to export all these information into one CSV file instead of manually sorting them out. Can i achieve this from tweaking the node.js code or is this impossible?

Please see attached image for graphical illustration of my question.

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #1 on: February 21, 2017, 12:39:14 PM »
Sorry for the late response here. Thanks for the feedback :)

This is not currently directly supported in the app.
I am sure it's not impossible but I haven't thought out a solution for this, but certainly would be a nice feature.
I plan to support multi graphs (multiple data sets on the same graph) in the future, it's being asked for in another thread. That's where this feature would come in handy.
However on thing that will need to reconcile is when the timestamps are close but not identical. Ie ... you are viewing data sets from 2 different nodes, not necessarily from the same one (ie sketch sending multiple data points per 1 timestamp). Not really sure how that would work but I think it should be separate time indexing. Where the data originates from the same node data could be indexed with the same timestamps, but those details have to be planned and maybe some more export options made available.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #2 on: February 21, 2017, 03:01:11 PM »
Thanks for your response Felix, almost convinced myself no one was going to respond.
So I tried intercepting the data coming in from the node, via the gateway.js code (picture attached) to include an "All" variable. I was hoping this would be logged as a ".bin" just like other parameters and then the the GETGRAPHDATA function would pull it up as a metric key for graph plot. Unfortunately, this wouldn't work because it a comma separated value data and only NUMERIC data can be stored in DB as a ".bin" file.

I understand you have a lot on your plate, but do you mind giving me an idea of what you'd likely do if you were to embark on doing this.
I seem to have run out of ideas on how to make this possible and I need it for a project with an approaching deadline.  :-[

Suggestions are highly welcome!

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #3 on: February 21, 2017, 03:23:08 PM »
The log you see there is simply a console output log that shows raw data and events etc. The real graph data is stored in binary files. A special algorithm is required to extract it from there, as seen in logutil.js.

I could hack together something in an alpha state, maybe a button on the node page. This would trigger a CSV download for all the graphed metrics, with a single timestamp index column and a separate column for each data point. Where data points are missing for some metrics would be blank cells. Would that work?

PS: So you're using my gateway app huh? In what capacity?

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #4 on: February 21, 2017, 04:16:14 PM »
That would be awesome. :)

Yes I am using your gateway app for a school project.

Basically, I'm trying to evaluate Moteino's Power consumption compared to other motes such as IRIS, MICA etc.

This will help with making a decision regarding what mote to use for an environmental monitoring system situated in a forest that would barely have supply of solar energy for recharging the batteries. I need a mote that consumes as little power as possible, and Moteino looks very promising.

The gateway app comes in handy because it will help to retrieve the required data. 

Thanks for making life easier for a lot of folks like me.  :D

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #5 on: February 21, 2017, 09:29:26 PM »
Basically, I'm trying to evaluate Moteino's Power consumption compared to other motes such as IRIS, MICA etc.

This will help with making a decision regarding what mote to use for an environmental monitoring system situated in a forest that would barely have supply of solar energy for recharging the batteries. I need a mote that consumes as little power as possible, and Moteino looks very promising.

If you are willing to do a little effort then you can get down to between 2-6uA. If you do a little more effort then you can do down to 0.1uA ;)

OK I will try to look into implementing something, won't promise anything great, just usable.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #6 on: February 21, 2017, 09:47:43 PM »
Thanks Felix, I really appreciate your effort  :)!!!!!!

0.1uA  :o ;D 8)

I'm definitely willing to do the little effort, what do I need to do to achieve this?

 

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #7 on: February 21, 2017, 09:59:12 PM »
0.1uA  :o ;D 8)

I'm definitely willing to do the little effort, what do I need to do to achieve this?

UPDATE: see the Moteino-8mhz for even lower power (eliminates the extra quiescent 2uA used by the LDO).

For starters I would look around in the forum, there are some active threads right now that talk about various ways to power a Moteino or Moteino derivations (all 328p based) from coin cells or supercapacitors, small solar panels, or combinations of those things.

You can easily achieve 6uA with the DeepSleep sample sketches. Well, actually those use the WDT for sleeping and waking every 8sec, which uses about 4uA. If you sleep "forever" and disable the WDT you can go to 2uA which is mostly the quiescent current of the MCP1703 regulator. If you want to remove the regulator from a Moteino and power it straight from a coin cell for instance, then you can achieve 0.1uA by disabling the WDT and sleeping "forever". Also in that scenario you'd have to set the fuses to run from the internal 8mhz oscillator rather than the onboard 16mhz one. And by "forever sleep" we mean you can still wake the atmega via a hardware interrupt rather than by its own clock source (the WatchDogTimer).
« Last Edit: February 06, 2018, 04:52:58 PM by Felix »

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #8 on: February 22, 2017, 10:48:48 AM »
Thanks, I'll try this out and let you know my results.

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #9 on: February 22, 2017, 11:31:57 AM »
I think I can implement a generic function to download all the CSV raw data of a node. There would be a default limit of about 100k data points.
The graph would look something like this, note the missing points and merged time index:


EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #10 on: February 22, 2017, 11:56:09 AM »
This would work.  ;) :)

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #11 on: February 23, 2017, 05:11:13 PM »
Hello Felix,
I know this belongs to a different thread, but this is a feedback to the low power suggestions you gave.
I am having issues with the DeepSleep_usingLowPowerlibrary, this gives me a whooping 5mA current consumption, while the  DeepSleep library gives as low as 6.9uA. I have nothing attached, just my Laptop+USB+FTDI Adapter+Moteino RFM69W
Is there something I'm doing wrong?

I have attached the two codes, though they are the sample codes from the RFM69 library.

DeepSleep_usingLowPowerlibrary
Code: [Select]
//***********************************************************************************************************
// Sample sketch that achieves the lowest power on a Moteino of ~6.5uA
// Everything is put to sleep including the MCU, the radio (if any) and the FlashMem chip
//**** SETTINGS *********************************************************************************************
#define WITH_RFM69              //comment this line out if you don't have a RFM69 on your Moteino
#define WITH_SPIFLASH           //comment this line out if you don't have the FLASH-MEM chip on your Moteino
//***********************************************************************************************************

#include <LowPower.h> //get library from: https://github.com/lowpowerlab/lowpower
                      //writeup here: http://www.rocketscream.com/blog/2011/07/04/lightweight-low-power-arduino-library/

#ifdef __AVR_ATmega1284P__
  #define LED           15 // Moteino MEGAs have LEDs on D15
  #define FLASH_SS      23 // and FLASH SS on D23
#else
  #define LED           9 // Moteinos have LEDs on D9
  #define FLASH_SS      8 // and FLASH SS on D8
#endif

#if defined(WITH_RFM69) || defined(WITH_SPIFLASH)
  #include <SPI.h>                //comes with Arduino IDE (www.arduino.cc)
  #if defined(WITH_RFM69)
    #include <RFM69.h>            //get it here: https://www.github.com/lowpowerlab/rfm69
    RFM69 radio;
  #endif
  #if defined(WITH_SPIFLASH)
    #include <SPIFlash.h>         //get it here: https://www.github.com/lowpowerlab/spiflash
    SPIFlash flash(FLASH_SS, 0xEF30); //EF30 for 4mbit  Windbond chip (W25X40CL)
  #endif
#endif

void setup () {
#ifdef WITH_RFM69
  radio.sleep();
#endif

#ifdef WITH_SPIFLASH
  if (flash.initialize())
    flash.sleep();
#endif

  for (uint8_t i=0; i<=A5; i++)
  {
#ifdef WITH_RFM69
    if (i == RF69_SPI_CS) continue;
#endif
#ifdef WITH_SPIFLASH
    if (i == FLASH_SS) continue;
#endif
    pinMode(i, OUTPUT);
    digitalWrite(i, LOW);
  }
}

void loop ()
{
  //optional blink to know radio/flash sleeping went OK
  pinMode(LED, OUTPUT);
  digitalWrite(LED, HIGH);
  delay(30);
  digitalWrite(LED, LOW);

  //sleep MCU for 8seconds
  LowPower.powerDown(SLEEP_8S, ADC_OFF, BOD_OFF);
}


DeepSleep
Code: [Select]
//***********************************************************************************************************
// Sample sketch that achieves the lowest power on a Moteino of ~6.5uA
// Everything is put to sleep including the MCU, the radio (if any) and the FlashMem chip
//**** SETTINGS *********************************************************************************************
#define WITH_RFM69              //comment this line out if you don't have a RFM69 on your Moteino
#define WITH_SPIFLASH           //comment this line out if you don't have the FLASH-MEM chip on your Moteino
//***********************************************************************************************************

#include <Arduino.h>            // assumes Arduino IDE v1.0 or greater
#include <avr/sleep.h>
#include <avr/wdt.h>
#include <avr/power.h>

#ifdef __AVR_ATmega1284P__
  #define LED           15 // Moteino MEGAs have LEDs on D15
  #define FLASH_SS      23 // and FLASH SS on D23
#else
  #define LED           9 // Moteinos have LEDs on D9
  #define FLASH_SS      8 // and FLASH SS on D8
#endif

#if defined(WITH_RFM69) || defined(WITH_SPIFLASH)
  #include <SPI.h>                //comes with Arduino IDE (www.arduino.cc)
  #if defined(WITH_RFM69)
    #include <RFM69.h>            //get it here: https://www.github.com/lowpowerlab/rfm69
    RFM69 radio;
    #define NETWORKID 100
    #define NODEID 123
    #define FREQUENCY RF69_915MHZ
  #endif
  #if defined(WITH_SPIFLASH)
    #include <SPIFlash.h>         //get it here: https://www.github.com/lowpowerlab/spiflash
    SPIFlash flash(FLASH_SS, 0xEF30); //EF30 for 4mbit  Windbond chip (W25X40CL)
  #endif
#endif

//watchdog interrupt
ISR (WDT_vect) {
  wdt_disable();
}

void setup () {
#ifdef WITH_RFM69
  radio.initialize(FREQUENCY,NODEID,NETWORKID);
  radio.sleep();
#endif

#ifdef WITH_SPIFLASH
  if (flash.initialize())
    flash.sleep();
#endif

//  //optional blink to know radio/flash sleeping went OK
 pinMode(LED, OUTPUT);
 digitalWrite(LED, HIGH);
 delay(80);
 digitalWrite(LED, LOW);
//  delay(50);
//  digitalWrite(LED, HIGH);
//  delay(50);
//  digitalWrite(LED, LOW);

  for (uint8_t i=0; i<=A5; i++)
  {
#ifdef WITH_RFM69
    if (i == RF69_SPI_CS) continue;
#endif
#ifdef WITH_SPIFLASH
    if (i == FLASH_SS) continue;
#endif
    pinMode(i, OUTPUT);
    digitalWrite(i, LOW);
  }

  power_timer1_disable();
  power_timer2_disable();
  power_twi_disable();
}

void loop ()
{
  // disable ADC
  ADCSRA = 0; 
  // clear various "reset" flags
  MCUSR = 0;
  // allow changes, disable reset
  WDTCSR = bit (WDCE) | bit (WDE);
  //set interrupt mode and an interval
  WDTCSR = bit (WDIE) | bit (WDP3) | bit (WDP0); //set WDIE, and 8 seconds delay
  wdt_reset(); //pat the dog...
 
  set_sleep_mode (SLEEP_MODE_PWR_DOWN);
  noInterrupts(); // timed sequence follows 
  sleep_enable();

  // turn off brown-out enable in software
  // BODS must be set to one and BODSE must be set to zero within four clock cycles
  MCUCR = bit (BODS) | bit (BODSE);
  // The BODS bit is automatically cleared after three clock cycles
  MCUCR = bit (BODS);
  interrupts();
  sleep_cpu();

  //cancel sleep as a precaution
  sleep_disable();
}


Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #12 on: February 23, 2017, 10:42:57 PM »
Should work, try it with nothing attached but the GND and VCC (3.5v+).

LukaQ

  • Sr. Member
  • ****
  • Posts: 301
  • Country: si
Re: Merging Exported data into one CSV file
« Reply #13 on: February 24, 2017, 02:22:17 PM »
5mA is far too high. I had far less with my, same meter...not in 6uA range, but if I remember right, average in 60-70uA. Is that constant 5ma?

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #14 on: February 25, 2017, 03:17:30 PM »
I was able to  get as low as 6.9uA with the DeepSleep_UsingLowPowerLibrary, but sometimes, the current consumption gets stuck at 0.33mA and doesn't go lower than that. Still trying to figure out what is going on.


EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #15 on: February 25, 2017, 03:19:45 PM »
Hi Felix,
Have you had the chance to try this out?  :-[

Thanks for your help!

I think I can implement a generic function to download all the CSV raw data of a node. There would be a default limit of about 100k data points.
The graph would look something like this, note the missing points and merged time index:



perky

  • Hero Member
  • *****
  • Posts: 873
  • Country: gb
Re: Merging Exported data into one CSV file
« Reply #16 on: February 25, 2017, 03:20:15 PM »
That's like a pull-up or two not being disabled and therefore sinking current. That could happen depending on the state of the signal when put to sleep.
Mark.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #17 on: February 25, 2017, 04:11:07 PM »
Thanks perky,
Any suggestions on how I can resolve the issue?

perky

  • Hero Member
  • *****
  • Posts: 873
  • Country: gb
Re: Merging Exported data into one CSV file
« Reply #18 on: February 25, 2017, 05:28:30 PM »
I'd put it to sleep, and while it's asleep measure the voltages on pins that you think might have a pull-up on it. If any of them are low look through the code and see if the internal pull-up is enabled (or there's an external pull-up) on that signal. A quick way might be to print out the input status and the pull-up status registers for those pins just prior to going to sleep.

Edit: BTW this can also happen when input pins are left floating. In that case it may or may not exhibit the behaviour depending on whether the input has charged up to the threshold, so look for tri-stated signals that have no pull-up/pull-down and are not being driven by any source. If it is this just putting a scope probe on signal and sometimes just touching the pins with your finger can change the current (assuming you're static discharged of course!)

Mark.
« Last Edit: February 26, 2017, 04:16:23 PM by perky »

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #19 on: February 26, 2017, 03:53:37 PM »
@EnMon, attached is the patch to add node export to CSV.
Please try it and let me know how it works. Based on your feedback (and others!) I will tweak it and add it to the next release.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #20 on: February 27, 2017, 06:06:36 AM »
Hurray!!!!!!!!   It works 8)
Please find attached images.

I wouldn't have been able to tell easily that some data points were missing.

Thanks Felix, I really appreciate your time and effort.   :)


Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #21 on: February 27, 2017, 08:51:38 AM »
My pleasure, I don't really get paid for it but it's fun and hopefully you consider using my products :)

I will plan to merge all this more seamlessly into a graph on the node front page where graphs are all merged then you have the same range buttons to choose/zoom and another CSV button to download what you see in the selected graph range.

Play some more and find any quirks, I didn't extensively test it.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #22 on: February 27, 2017, 11:54:47 AM »
The new features will be great, upon addition.

I've been playing around with it and no issues thus far. I'll let you know if I run into any.

Quick question: I am still trying to figure out reasons for missing data points.
My assumption is that for a Weathernode, transmitting Temperature and Humidity data, the two sets of data should be transmitted at the same time since they are coming from the same sensor. Why would I receive Temp and not Humidity or Humidity and not Temp at the Gateway? Please lead me in the right direction on how to understand issues with some data points not been received. 


Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #23 on: February 27, 2017, 01:07:43 PM »
Often assumptions are != reality, please check your sketch to see if that is indeed the fact. If so I would fully expect the times to match, yours are not even close to each other.

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #24 on: March 16, 2017, 10:07:46 PM »
Looks like I might have found a bug *sad*

I am still having issues with missing data points, I followed your lead as to checking my code but found no clues.

Fortunately, I checked the gateway.db file and the system log file and realized the gateway Moteino is actually receiving the data but I assume it's either not logging the data or it's not exporting it correctly to the web server.

As usual, i have attached images that reveal that the missing data point is actually existing in the gateway.db file but it just never makes it to the CSV download file.

What can I do to resolve this?  :-[



Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #25 on: March 17, 2017, 11:29:18 AM »
That's because the logger does not log consecutive duplicates by default, to save DB space. There is a time limit however, which can be changed if you'd like.

Here's the logger function in logUtil.js that handles that and comments to explain:

Code: [Select]
// filename:  binary file to append new data point to
// timestamp: data point timestamp (seconds since unix epoch)
// value:     data point value (signed integer)
// duplicateInterval: if provided a duplicate value is only posted after this many seconds    <<<EXPLANATION
exports.postData = function post(filename, timestamp, value, duplicateInterval)

The gateway.js app calls this here:

Code: [Select]
dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);

The duplicateInterval can be used in each metric to define how often a duplicate will be logged, you will see a bunch of defaults in metrics.js like this: duplicateInterval:3600
that is 1 hour in seconds.

So ... you can change the duplicateInterval in metrics.js BUT i strongly recommend you redefine the metric you want customized in your own user metrics file, that's so it doesn't get overwritten in an upgrade.
By default the logger resolution is 1 second, so to make sure duplicates are logged all the time, just do duplicateInterval:1.
FWIW The null in the post() will fallback to the post() function's default of 1, but due to the merger of definitions between metrics.js and your own user metrics .js file, the merger is inclusive which means if you don't redefine it, it will retain the value in the master metrics.js.

Perhaps duplicateInterval should be a global setting but then some folks may want this customized per metric, which complicates matters. But I hope this helps you for now.

An example user V metric with customization is already given here. Just change 3600 to 1 in here and bob's your uncle. Make sure to restart the app from the settings page.

Code: [Select]
/*this sample metric will override that which is already defined in main metrics.js*/
/*you can redefine defaults or write your own new custom metrics in 1 or more files in this folder, separate them as you'd like - they all get merged together when the app loads*/
exports.metrics = {
  V:
  {
    name: 'V',
    regexp: /\b(?:V?BAT|VOLTS|V)\:([\d\.]+)v?\b/i,
    value: '',
    duplicateInterval: 1,

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #26 on: March 17, 2017, 12:51:35 PM »
It works!
You really should consider taking up a faculty job in the EECS dept of any school you deem fit.  ;D
What happens when the DB gets full?
Or rather how long can I have this running if I receive data every 10 minutes from over 10 transmitting motes?



Pardon me for my numerous demands on your gateway app, considering building mine from the scratch for future projects  :-[

How can I include the formula for converting the Unix Time Stamp, so that I don't have to manually do the calculations in excel?

I tried looking through the gateway.js code and I found "var ts" and console.log as shown below:


Code: [Select]
 //log data for graphing purposes, keep labels as short as possible since this log will grow indefinitely and is not compacted like the node database
            if (existingNode.metrics[matchingMetric.name].graph==1)
            {
              var graphValue = metricsDef.isNumeric(matchingMetric.logValue) ? matchingMetric.logValue : metricsDef.determineGraphValue(matchingMetric, tokenMatch); //existingNode.metrics[matchingMetric.name].value;
              if (metricsDef.isNumeric(graphValue))
              {
               [color=yellow] var ts[/color] = Math.floor(Date.now() / 1000); //get timestamp in whole seconds
                var logfile = path.join(__dirname, dbDir, dbLog.getLogName(id, matchingMetric.name));
                try {
                  [color=yellow]console.log[/color]('post: ' + logfile + '[' + ts + ','+graphValue + ']');
                  dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);
                } catch (err) { console.error('   POST ERROR: ' + err.message); /*console.log('   POST ERROR STACK TRACE: ' + err.stack); */ } //because this is a callback concurrent calls to the same log, milliseconds apart, can cause a file handle concurrency exception
              }
              else console.log('   METRIC NOT NUMERIC, logging skipped... (extracted value:' + graphValue + ')');
            }

Can I safely insert this formula " /(1000*60*60*24)+"1/1/1970"+(-5/24)" without breaking the code?

What would be your recommended approach to getting this done?

Thank you so much for all your help!

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #27 on: March 17, 2017, 04:38:59 PM »
You really should consider taking up a faculty job in the EECS dept of any school you deem fit.  ;D
Well thanks i feel flattered  :) bu i think I enjoy low power lab too much for now and i dont like sitting in traffic 2 times a day

What happens when the DB gets full?
Or rather how long can I have this running if I receive data every 10 minutes from over 10 transmitting motes?

The "DB" has no size limit, only your SD card.
I have only a handful of regular transmitting nodes, and I had them for years. I used a 4gb card (until it failed recently and was forced to upgrade to 8gb) and i havent counted the # of logged entries, but I have a backup of that point and it was 30MB. Divide by 9bytes per entry, about 3 million entries. Still that is far from filling a 4gb. If you do end up with a "full db" it's EZ .. upgrade your SD :)

How can I include the formula for converting the Unix Time Stamp, so that I don't have to manually do the calculations in excel?

Just calculate the "excel" timestamp right in the javascript and output it to the csv, that way when you open it in excel you just need to format it to DATE.
Let me know if you need help with that, im swamped.

PS MS products == ghetto, they dont support a lousy UNIX timestamp  ::)

EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #28 on: March 17, 2017, 05:28:38 PM »
There is nothing like working from the comfort of your home.  ;D

I can only imagine the workload you have, helping lots of folks like me. (I really admire your dedication!)

For calculating the excel time-stamp and outputting it to CSV, I'm thinking of creating a new variable say
 tx = ts / (1000*60*60*24)+"1/1/1970"+(-5/24) and replacing the "ts" with "tx" in the dblog.postData code line below:

dbLog.postData(logfile, ts, graphValue, matchingMetric.duplicateInterval || null);

Is there any other edits I need to make to make this work?


EnMon

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #29 on: March 21, 2017, 06:16:06 PM »
Hi felix,
Did you by any chance take a look at this?

Felix

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #30 on: March 22, 2017, 09:05:17 AM »
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

  • NewMember
  • *
  • Posts: 32
  • Country: us
Re: Merging Exported data into one CSV file
« Reply #31 on: March 22, 2017, 09:19:03 PM »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 6531
  • Country: us
    • LowPowerLab
Re: Merging Exported data into one CSV file
« Reply #32 on: March 23, 2017, 09:39:47 AM »
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: