@chris299 Hier "as it is" Ein roher Klotz, no thrills, kein Komfort, keine Fehlerabfangen und für Windows-Verzeichnisse. Keine Ahnung wie man das auf Linux-Verzeichnisse umstellt. Aber auch da kann ChatGPT sicher helfen. Minimale Dialoge ohne Überprüfung.
Ein einigermassen aktuelles Python muß auf dem Rechner laufen (bei mir 3.10.8) und der Python Pfad sollte dem System bekannt sein.
Ich starte immer aus der Kommandozeile heraus (CMD), weil ich dann ggf Fehlermeldungen sehen. Bei mir aus einem Verzeichnis D:\auswerungen
Das Script heisst z.B.
iobroker-history-all-data-to-xls-V3-local-time.py
dann starte ich es mit
python iobroker-history-all-data-to-xls-V3-local-time.py
Es werden einige Pakete benötigt. Steht im Programm als Kommentar. Vielleicht brauchst Du bei Deiner Installation noch weitere. Dann kann man das den Fehlermeldungen entnehmen.
Kann am Anfang etwas Gefummel sein. ChatGPT kennt sich mit Python recht gut aus und hilft gerne - auch bei der Umstallung auf Linux, falls erforderlich 🙂
Ach ja, Python hat dieselbe Unart wie YAML. Man hat sich von Klammern als Strukturelement verabschiedet und nutzt stattdessen Einrücken. In Vielfachen von 4 Blanks.
Hier eine Sctript-Version für local time:
# created with the help of ChatGPT
# Install openpyxl library if necessary
# Make sure to install pandas using: pip install pandas
import os
import json
import pandas as pd
from datetime import datetime
import pytz # Make sure to install pytz using: pip install pytz
def read_json_file(file_path):
with open(file_path, 'r') as file:
data = json.load(file)
return data
def extract_data_from_json(json_data):
data_list = []
for entry in json_data:
if entry.get("val") is not None:
val, ts = entry["val"], entry["ts"]
data_list.append((val, ts))
return data_list
def convert_timestamp(ts):
local_tz = pytz.timezone('Europe/Berlin') # Adjust to your local time zone
local_time = datetime.fromtimestamp(ts / 1000, local_tz)
sample_date = local_time.strftime('%d.%m.%Y')
sample_time = local_time.strftime('%H:%M')
return sample_date, sample_time
def generate_excel(start_date, end_date, iobroker_directory, filename_template, xls_directory, xls_name):
# Prepare date range
date_range = pd.date_range(start=start_date, end=end_date).strftime('%Y%m%d')
# Initialize lists to store data
all_data = []
# Counter for monitoring output
counter = 0
dataset_counter = 0
# Display start and end dates
print(f"Start Date: {start_date}")
print(f"End Date: {end_date}")
# Show default values
print(f"ioBroker history directory: {iobroker_directory}")
print(f"ioBroker - Filename: {filename_template}")
print(f"Directory for xls Output: {xls_directory}")
print(f" ")
# Get user-defined xls_name
user_defined_xls_name = input("Enter the user-defined xls_name (default: 'ioBroker-history'): ") or 'ioBroker-history'
print(f" ")
print(f"Data will be collected from files")
# Iterate through directories and files
for date in date_range:
directory_path = os.path.join(iobroker_directory, date)
if os.path.exists(directory_path):
for root, dirs, files in os.walk(directory_path):
for file in files:
if file.startswith(filename_template) and file.endswith('.json'):
file_path = os.path.join(root, file)
json_data = read_json_file(file_path)
data_list = extract_data_from_json(json_data)
all_data.extend(data_list)
# Monitor progress
counter += 1
dataset_counter +=1
# print(f".", end="")
# Check if 8 entries are printed and start a new line
if counter % 150 == 0:
print(f" {dataset_counter}")
# Convert data to DataFrame
print(f"conversion ongoing, please wait")
df = pd.DataFrame(all_data, columns=['Value', 'Timestamp'])
# Convert timestamp and add Date and Time columns
df['Date'], df['Time'] = zip(*df['Timestamp'].apply(convert_timestamp))
# Replace dot with comma in the 'Value' column
df['Value'] = df['Value'].astype(str).str.replace('.', ',')
# Create complete xls_name
xls_name += f'-from-{start_date}-to-{end_date}.xlsx'
xls_complete_name = user_defined_xls_name + xls_name
csv_name = ""
csv_name += f'-from-{start_date}-to-{end_date}.csv'
csv_complete_name = user_defined_xls_name + csv_name
# Show complete path and file name
output_path = os.path.join(xls_directory, xls_complete_name)
output_path_csv = os.path.join(xls_directory, csv_complete_name)
print(f"Location Excel File: {output_path}")
print(f"Location CSV File: {output_path_csv}")
# Save to CSV
print(f"csv file will be created, please wait")
df.to_csv(output_path_csv, index=False)
print(f"\nCSV file created: {output_path_csv}")
# Save to Excel
print(f"xlsx file will be created, please wait")
df.to_excel(output_path, index=False)
print(f"\nExcel file created: {output_path}")
print(f"job done")
# Get user input
print(f"Version 1.0 based on ChatGPT 3.5 with own debugs and mods")
start_date = input("Enter the start date (YYYYMMDD): ")
end_date = input("Enter the end date (YYYYMMDD): ")
iobroker_directory = input("ioBroker history directory (default: 'd:\\ioBroker-data\\History\\'): ") or 'd:\\ioBroker-data\\History\\'
filename_template = input("ioBroker json filename: (default: 'history.hm-rpc.1.CUX9000052.1.STATE.json'): ") or 'history.hm-rpc.1.CUX9000052.1.STATE.json'
xls_directory = input("Directory for xls Output (default: 'D:\\Auswertungen'): ") or 'D:\\Auswertungen'
generate_excel(start_date, end_date, iobroker_directory, filename_template, xls_directory, '')
Viel Erfolg!
