CSV export and import #67

Open
opened 7 years ago by cpfeiffer · 15 comments
Owner

IMHO mobile phones are not the right place to keep data, but before we have a service where we can send the data (see #49) a CSV export and import would allow users to easily back up and restore their activity data.

IMHO mobile phones are not the right place to keep data, but before we have a service where we can send the data (see #49) a CSV export and import would allow users to easily back up and restore their activity data.
cathalgarvey commented 6 years ago (Migrated from github.com)
Owner

+1! Why is this feature not considered critical? I bought a fitness tracker (Mi Band) because I want to track my activity and sleep. It's 100% about the data.

I am deeply appreciative of Gadgetbridge: I literally wouldn't have bought a Mi Band otherwise (where do I donate? :)), but I want to store, manipulate, annotate, and visualise my data outside the app.

When will data export be supported? CSV is perfect for this, it's super-lightweight for developers and well-supported/easily parsed elsewhere, especially if you avoid strings and just use numbers (such as ", , steps, activity rating".

Slightly less lightweight to implement, but far more versatile and data-rich, would be JSON Lines: each line is a JSON object, there is no global list, so data is appended to end of file and data can be parsed line-by-line.

If any export feature, coupled with a "Share" intent, could be implemented it would let me satisfy my own data processing needs independent of the Gadgetbridge app; I'd really appreciate this.

+1! Why is this feature not considered critical? I bought a fitness tracker (Mi Band) because I want to track my activity and sleep. It's 100% about the data. I am deeply appreciative of Gadgetbridge: I literally wouldn't have bought a Mi Band otherwise (where do I donate? :)), but I want to store, manipulate, annotate, and visualise my data outside the app. When will data export be supported? CSV is perfect for this, it's super-lightweight for developers and well-supported/easily parsed elsewhere, especially if you avoid strings and just use numbers (such as "<from unix time>, <to unix time>, steps, activity rating". Slightly less lightweight to implement, but far more versatile and data-rich, would be [JSON Lines](http://jsonlines.org/): each line is a JSON object, there is no global list, so data is appended to end of file and data can be parsed line-by-line. If any export feature, coupled with a "Share" intent, could be implemented it would let me satisfy my own data processing needs independent of the Gadgetbridge app; I'd really appreciate this.
Poster
Owner

Completely agree with you. It would be helpful if someone could have a look at how other projects are exporting or expecting data so that we can be compatible with them instead of inventing or own format.

Completely agree with you. It would be helpful if someone could have a look at how other projects are exporting or expecting data so that we can be compatible with them instead of inventing or own format.
Owner

Why I do agree with you, let me comment wrt the "critical" part.

There are already ways to export the database:

  • Copy it from adb she'll if you're rooted
  • Extract it from an adb backup if you aren't

Once you have the sqlite db on your computer you can do whatever you want (there's a csv export script in our wiki).

Is this end-user friendly? Of course not. But as Carsten said, until we don't have a user-friendly format to export to (what would an end user do with a csv which isn't ready-to-be-ingested by any other tool?) I don't see the urgency.

In any case this issue would a perfect starting point for someone willing to help. It's fairly self-contained and one don't have to deal with the communication with different devices etc. :)

Why I do agree with you, let me comment wrt the "critical" part. There are already ways to export the database: - Copy it from adb she'll if you're rooted - Extract it from an adb backup if you aren't Once you have the sqlite db on your computer you can do whatever you want (there's a csv export script in our wiki). Is this end-user friendly? Of course not. But as Carsten said, until we don't have a user-friendly format to export to (what would an end user do with a csv which isn't ready-to-be-ingested by any other tool?) I don't see the urgency. In any case this issue would a perfect starting point for someone willing to help. It's fairly self-contained and one don't have to deal with the communication with different devices etc. :) ##
cathalgarvey commented 6 years ago (Migrated from github.com)
Owner

CSV can be imported to Libreoffice or MSOffice so I'd argue any CSV is better than awaiting the "perfect" CSV.

Thanks for the advice re:adb, I'll play with it, but obviously it's not a real answer to the issue: if GB is to be a replacement and not merely a shim, it needs to be useful to people who's never heard of ADB. And it ought to be convenient even for power users because this is lifestyle tracking, not weekend hackathon stuff! :)

On 3 October 2015 12:28:11 IST, Daniele Gobbetti notifications@github.com wrote:

Why I do agree with you, let me comment wrt the "critical" part.

There are already ways to export the database:

  • Copy it from adb she'll if you're rooted
  • Extract it from an adb backup if you aren't

Once you have the sqlite db on your computer you can do whatever you
want (there's a csv export script in our wiki).

Is this end-user friendly? Of course not. But as Carsten said, until we
don't have a user-friendly format to export to (what would an end user
do with a csv which isn't ready-to-be-ingested by any other tool?) I
don't see the urgency.

In any case this issue would a perfect starting point for someone
willing to help. It's fairly self-contained and one don't have to deal
with the communication with different devices etc. :)


Reply to this email directly or view it on GitHub:
https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-145239598

Sent from my Android device with K-9 Mail. Please excuse my brevity.

CSV can be imported to Libreoffice or MSOffice so I'd argue any CSV is better than awaiting the "perfect" CSV. Thanks for the advice re:adb, I'll play with it, but obviously it's not a real answer to the issue: if GB is to be a replacement and not merely a shim, it needs to be useful to people who's never heard of ADB. And it ought to be convenient even for power users because this is lifestyle tracking, not weekend hackathon stuff! :) On 3 October 2015 12:28:11 IST, Daniele Gobbetti notifications@github.com wrote: > Why I do agree with you, let me comment wrt the "critical" part. > > There are already ways to export the database: > - Copy it from adb she'll if you're rooted > - Extract it from an adb backup if you aren't > > Once you have the sqlite db on your computer you can do whatever you > want (there's a csv export script in our wiki). > > Is this end-user friendly? Of course not. But as Carsten said, until we > don't have a user-friendly format to export to (what would an end user > do with a csv which isn't ready-to-be-ingested by any other tool?) I > don't see the urgency. > > In any case this issue would a perfect starting point for someone > willing to help. It's fairly self-contained and one don't have to deal > with the communication with different devices etc. :) > > ## > > --- > > Reply to this email directly or view it on GitHub: > https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-145239598 ## Sent from my Android device with K-9 Mail. Please excuse my brevity.
Poster
Owner

Just for the record, the "Debug " screen sports an export option that will copy the db to the external sdcard. So you neither need to use adb, nor be rooted; you can use any file manager to copy/mail/share/whatever the db.

Just for the record, the "Debug " screen sports an export option that will copy the db to the external sdcard. So you neither need to use adb, nor be rooted; you can use any file manager to copy/mail/share/whatever the db.
cathalgarvey commented 6 years ago (Migrated from github.com)
Owner

Handy! How hard would it be to add an option that dumps the SQL table to CSV directly, then? Sounds like most of the pieces are already there, and they're both tabular formats..

On 3 October 2015 13:22:51 IST, Carsten Pfeiffer notifications@github.com wrote:

Just for the record, the "Debug " screen sports an export option that
will copy the db to the external sdcard. So you neither need to use
adb, nor be rooted; you can use any file manager to
copy/mail/share/whatever the db.


Reply to this email directly or view it on GitHub:
https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-145243753

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Handy! How hard would it be to add an option that dumps the SQL table to CSV directly, then? Sounds like most of the pieces are already there, and they're both tabular formats.. On 3 October 2015 13:22:51 IST, Carsten Pfeiffer notifications@github.com wrote: > Just for the record, the "Debug " screen sports an export option that > will copy the db to the external sdcard. So you neither need to use > adb, nor be rooted; you can use any file manager to > copy/mail/share/whatever the db. > > --- > > Reply to this email directly or view it on GitHub: > https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-145243753 ## Sent from my Android device with K-9 Mail. Please excuse my brevity.
cathalgarvey commented 6 years ago (Migrated from github.com)
Owner

PS: I'd eagerly contribute if I spoke Java to any meaningful extent, so please forgive my failure to PR with the request!

PS: I'd eagerly contribute if I spoke Java to any meaningful extent, so please forgive my failure to PR with the request!
Poster
Owner

Don't worry, eventually we'll get there :-) In the meantime, something like http://stackoverflow.com/questions/5776660/export-from-sqlite-to-csv-using-shell-script might suit your needs.

Don't worry, eventually we'll get there :-) In the meantime, something like http://stackoverflow.com/questions/5776660/export-from-sqlite-to-csv-using-shell-script might suit your needs.
Poster
Owner

Another thing: http://aaa.andsen.dk/aSQLiteManager.html

It is available in f-droid and supposedly allows you to open an existing sqlite database and perform csv imports and exports.

Another thing: http://aaa.andsen.dk/aSQLiteManager.html It is available in f-droid and supposedly allows you to open an existing sqlite database and perform csv imports and exports.
Poster
Owner

Wow, we even have a wiki page regarding csv export:
https://github.com/Freeyourgadget/Gadgetbridge/wiki/Developer-Documentation

Wow, we even have a wiki page regarding csv export: https://github.com/Freeyourgadget/Gadgetbridge/wiki/Developer-Documentation
Poster
Owner

Did you succeed in exporting your data yet?

Did you succeed in exporting your data yet?
cathalgarvey commented 6 years ago (Migrated from github.com)
Owner

Haven't had a chance but will do, and thanks to everyone for the suggestions. My individual success isn't really the point of this feature request of course, because 95% of gadgetbridge users will never be aware of or able to export through ADB/Root apps.

On 7 October 2015 19:45:04 IST, Carsten Pfeiffer notifications@github.com wrote:

Did you succeed in exporting your data yet?


Reply to this email directly or view it on GitHub:
https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-146290482

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Haven't had a chance but will do, and thanks to everyone for the suggestions. My individual success isn't really the point of this feature request of course, because 95% of gadgetbridge users will never be aware of or able to export through ADB/Root apps. On 7 October 2015 19:45:04 IST, Carsten Pfeiffer notifications@github.com wrote: > Did you succeed in exporting your data yet? > > --- > > Reply to this email directly or view it on GitHub: > https://github.com/Freeyourgadget/Gadgetbridge/issues/67#issuecomment-146290482 ## Sent from my Android device with K-9 Mail. Please excuse my brevity.
djibux commented 3 years ago (Migrated from github.com)
Owner

Hello,

Not sure this is the right place to post, but I have just been playing with Gadgetbridge sqlite database and managed get the information I needed to analyze my health data.

Of course you will need to adapt to your watch by changing the table. I have a Pebble so I use PEBBLE_HEALTH_ACTIVITY_SAMPLE.

Steps per day

select date(TIMESTAMP, 'unixepoch') as "Date", sum(STEPS) as "Steps"
from PEBBLE_HEALTH_ACTIVITY_SAMPLE
group by date(TIMESTAMP, 'unixepoch')

Sleep per day

select
  round(sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600.0,1) as "Duration",
  sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600 as "Hours",
  sum(TIMESTAMP_TO-TIMESTAMP_FROM)%3600/60 as "Minutes",
  datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start",
  datetime(max(TIMESTAMP_TO), 'unixepoch') as "End"
from PEBBLE_HEALTH_ACTIVITY_OVERLAY
where RAW_KIND = 1
group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day');

Sleep, deep sleep, nap and deep nap per day

select
  round(sum(case when RAW_KIND = 1 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Sleep Duration",
  round(sum(case when RAW_KIND = 2 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Sleep Duration",
  round(sum(case when RAW_KIND = 3 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Nap Duration",
  round(sum(case when RAW_KIND = 4 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Nap Duration",
  datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start",
  datetime(max(TIMESTAMP_TO), 'unixepoch') as "End"
from PEBBLE_HEALTH_ACTIVITY_OVERLAY
where RAW_KIND in (1, 2, 3, 4)
group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day');

I consider sleep sessions starting after 8pm as sleep for the next day. In other words if you go to bed at 9pm on May 1st and wake up at 7am on May 2nd, it will register as 10 hours on May 2nd.

I suspect the timestamps to be UTC, so you might need to add on offset to get the proper datetime depending on your timezone.

Hope that helps. Maybe it could be implemented in the app as a CSV export of health data.

Hello, Not sure this is the right place to post, but I have just been playing with Gadgetbridge sqlite database and managed get the information I needed to analyze my health data. Of course you will need to adapt to your watch by changing the table. I have a Pebble so I use `PEBBLE_HEALTH_ACTIVITY_SAMPLE`. Steps per day --- ```sql select date(TIMESTAMP, 'unixepoch') as "Date", sum(STEPS) as "Steps" from PEBBLE_HEALTH_ACTIVITY_SAMPLE group by date(TIMESTAMP, 'unixepoch') ``` Sleep per day --- ```sql select round(sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600.0,1) as "Duration", sum(TIMESTAMP_TO-TIMESTAMP_FROM)/3600 as "Hours", sum(TIMESTAMP_TO-TIMESTAMP_FROM)%3600/60 as "Minutes", datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start", datetime(max(TIMESTAMP_TO), 'unixepoch') as "End" from PEBBLE_HEALTH_ACTIVITY_OVERLAY where RAW_KIND = 1 group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day'); ``` Sleep, deep sleep, nap and deep nap per day --- ```sql select round(sum(case when RAW_KIND = 1 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Sleep Duration", round(sum(case when RAW_KIND = 2 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Sleep Duration", round(sum(case when RAW_KIND = 3 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Nap Duration", round(sum(case when RAW_KIND = 4 then TIMESTAMP_TO-TIMESTAMP_FROM else 0 end)/3600.0,1) as "Deep Nap Duration", datetime(min(TIMESTAMP_FROM), 'unixepoch') as "Start", datetime(max(TIMESTAMP_TO), 'unixepoch') as "End" from PEBBLE_HEALTH_ACTIVITY_OVERLAY where RAW_KIND in (1, 2, 3, 4) group by date(TIMESTAMP_FROM, 'unixepoch', '+4 hours', 'start of day'); ``` I consider sleep sessions starting after 8pm as sleep for the next day. In other words if you go to bed at 9pm on May 1st and wake up at 7am on May 2nd, it will register as 10 hours on May 2nd. I suspect the timestamps to be UTC, so you might need to add on offset to get the proper datetime depending on your timezone. Hope that helps. Maybe it could be implemented in the app as a CSV export of health data.
bkqtnte10 commented 3 years ago (Migrated from github.com)
Owner

End-user that would like to backup the data reporting in

End-user that would like to backup the data reporting in
MagicFab commented 3 years ago (Migrated from github.com)
Owner

I've updated the documentation at https://codeberg.org/Freeyourgadget/Gadgetbridge/wiki/Developer-Documentation with more details and steps to export to CSV.

I've updated the documentation at https://codeberg.org/Freeyourgadget/Gadgetbridge/wiki/Developer-Documentation with more details and steps to export to CSV.
Sign in to join this conversation.
No Milestone
No Assignees
3 Participants
Notifications
Due Date

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.