Extracting Bright Data to CSV
Hello
Where may I find instructions and advice on how to extract my half hourly electricity usage data from my Bright app to a csv file? I am not a programmer so please let's start with a toe in the water approach first!
Many thanks
Comments
Look in the app itself under Support - FAQ's ?
I've downloaded electricity usage csv data from the Smart Tariff Smart Comparison site, but cannot make sense of the timestamp format after opening in Libre Office calc - none of the Time formatting options result in anything sensible. Can anyone help?
Where can I find gas usage csv data?
I've just struggled with this issue myself. It's a Unix timestamp and is the number of milliseconds since the start of the 'Unix Epoch' - midnight on 1st Jan 1970. So to convert in Excel you need to convert this number to days by dividing by 1000 to convert to seconds and again by 86400 (number of seconds in a day). This will likely give you a fractional result (ie not a whole number) which is fine - the fractional part is the time.
Next, you need to add the result to Excel's value for the date 1st Jan 1970. The following formula will do all the above (assuming your datestamp is in cell a1) -
=a1*1000*86400+DATE(1970,1,1)
The result will be the Excel timestamp equivalent of the Unix timestamp. You will need to format the result with an appropriate date/time format. Select the cell containing the result, and go to 'Format Cells'. Select Category 'Custom'. You will see a selection of pre-configured formats, but you can enter your own in the box immediately under the 'Type:' heading. Replace whatever is there with the following -
d/m/yy h:mm;@
This should give you your date and time in the same cell. However, if you need these in separate cells, then copy the value into two separate cells and format one as a date (eg dd/mm/yy) and the other as a time (eg hh:mm). Remember thought that the formatting only changes what you see in the cell - the underlying data is still an Excel datestamp so be careful if you want to do calculations on these (eg working out the number of days between a cell and today's date, as the results could be misleading.
Remember, this will be UTC - Co-ordinated Universal Time, or GMT to us Brits, so you will need to allow for this if you want daylight saving time (BST).
Good luck.
Thanks very much Paul - untangled that for me! Just a small/significant thing - as you say, you need to divide, so the correct formula is:
=((A2/1000)/86400)+DATE(1970,1,1)
(my timestamp data, from the original downloaded file, starts in A2)
Cheers, Rob
Ahh - good spot. Thanks for the correction.
Paul