Export Data from OctoPrint-PrintHistory's Database as CSV

ph-export.sh

#!/bin/bash

set -e

## Licensed under GNU GPLv3
##
## Export Data from OctoPrint-PrintHistory's Database as CSV
## Outport Format is compatible with OctoPrint-PrintJobHistory
##
## https://github.com/imrahil/OctoPrint-PrintHistory
## https://github.com/OllisGit/OctoPrint-PrintJobHistory
##
## Requirements: apt install sqlite3 bc
## Usage: ./ph-export.sh <PrintHistory-db-File>
## Example: ./ph-export.sh ~octoprint/.octoprint/data/printhistory/history.db
##
## The CSV import of PrintJobHistory seems to hang if a File contains more than a specific amount of entries, so a new CSV is created every time the chunk_size is reached.
## You might want to tweak chunk_size to your specific needs.

dbfile=${1}

chunk_size=670
file_index=0
data_counter=0
csv_out="ph-export"

## header helper function
function header {
  echo '"User","Print result [success canceled failed]","Start Datetime [dd.mm.yyyy hh:mm]","End Datetime [dd.mm.yyyy hh:mm]","Duration","File Name","File Path","Note","Spool Name","Used Length [mm]","Calculated Length [mm]"'
}

## prime initial file
header > ${csv_out}-${file_index}.csv

## export data
while read -r line; do

  ## Retrieve Fields
  id=$(echo $line | cut -f1 -d'|')
  filename=$(echo $line | cut -f2 -d'|')
  note=$(echo $line | cut -f3 -d'|')
  spool=$(echo $line | cut -f4 -d'|')
  filamentVolume=$(echo $line | cut -f5 -d'|')
  filamentLength=$(echo $line | cut -f6 -d'|')
  printTime=$(echo $line | cut -f7 -d'|')
  success=$(echo $line | cut -f8 -d'|')
  timestamp=$(echo $line | cut -f9 -d'|')
  user=$(echo $line | cut -f10 -d'|')
  parameters=$(echo $line | cut -f11 -d'|')

  ## Set result
  case ${success} in
    1)result='success';;
    *)result='failed';;
  esac

  ## Convert Timestamps
  startdatetime=$(date -d @${timestamp:-0} '+%d.%m.%Y %R')
  enddatetime=$(date -d @$(echo "${timestamp:-0} + ${printTime:-0}" | bc) '+%d.%m.%Y %R')
  duration=$(eval "echo $(date -ud "@${printTime:-0}" +'$((%s/3600/24))d%Hh%Mm%Ss')")

  ## Isolate Filename
  fname=$(echo ${filename} | rev | cut -f1 -d'/' | rev)

  ## output current dataset
  echo "\"${user}\",\"${result}\",\"${startdatetime}\",\"${enddatetime}\",\"${duration}\",\"${fname}\",\"${filename}\",\"${note}\",\"${spool}\",\"${filamentLength}\",\"${filamentLength}\"" | tee -a ${csv_out}-${file_index}.csv

  ## increase data counter
  ((data_counter=data_counter+1))
  ## Reset counter and create a new file if we reached the magical limit
  if [ ${data_counter} -eq ${chunk_size} ]; then
    data_counter=0
    ((file_index=file_index+1))
    header > ${csv_out}-${file_index}.csv
  fi

done < <(echo 'select * from print_history;' | sqlite3 ${dbfile})