Log Userdata and Visualize it

Revit
1

Hello Dynamo Friends :slight_smile:

Inspired by some threads* on the board I´m now logging userdata and I´m trying to visualize it in excel.
I`m struggling with excel, i would like to share my workflow and I´m very interrested in what all you guys are using or have created regarding userdata and charts! And I would be very interrested in methods to vizualize data live with a webserver!?

My problem with excel:
The formulas (UNIQUE, COUNTIF) won`t update when i write data into the file with dynamo :frowning:
When i write the data manually the formulas will update automatically and so will the charts!

So whats the difference between the dynamo and manual method?

image
image3707×1314 260 KB

image
image1748×1708 683 KB

image
image2941×1288 270 KB 

import clr
clr.AddReference('DynamoRevitDS')
import Dynamo
clr.AddReference('DSCoreNodes')
import DSCore
from DSCore import *
clr.AddReference("RevitServices")
from RevitServices.Persistence import DocumentManager
from datetime import datetime

dynamoRevit = Dynamo.Applications.DynamoRevit()

currentWorkspace = dynamoRevit.RevitDynamoModel.CurrentWorkspace

dynamoFileName = currentWorkspace.FileName

app = DocumentManager.Instance.CurrentUIApplication.Application

userName = app.Username

now = datetime.now()
dateformat = "%d.%m.%Y"
timeformat = "%X"
date = now.strftime(dateformat)
time = now.strftime(timeformat)

OUT = userName, date, time, dynamoFileName

*Threads:

Sending emails - Developers - Dynamo (dynamobim.com)

Python to get scripts usage informations in your company - Developers - Dynamo (dynamobim.com)

2

In my opinion excel is absolutely the wrong tool for display here. Shift to something else.

I have utilized PowerBI far too many times at this point. Data can be pulled from Revit via Dynamo, written to a CSV, Excel file, or JSON string. From there you can run a query on the data in PowerBI and generate your dashboard. These dashboards can then be published to the web and viewed by others as desired. If new data comes in it can be set to live update (or not)… overall it’s infinitely more suitable for the task.

Literally everything I have ever built has customer data in it so I can’t share any visuals, but there are a bunch of resources for learning how to utilize the tool.

3 Likes
3

I would go with PowerBi as mention…but think Bumblebee have nodes for automatic piescharts in excel…

https://konradsobon.gitbooks.io/bumblebee-primer/content/270_chart_style.html

2 Likes
4

+1 to power bi. Currently have my scripts (pyrevit/dynamo) silently reporting to user based journal files on server in csv format. I jam them all together using pyrevit into a log them power bi just needs a refresh. Has worked for me for about 2 years now, thousands of rows and no issues.

2 Likes
5

Thanks for your replies.

Unfortunately i have nothing else available, only excel. There is no collaboration with the IT department possible and i can´t install anything myselfe.

6

Personally I’d undertake a different task until IT will provide you the tools you need. These are some VERY limiting constraints you have yourself in.

One thought: you could write some interactive web code (ie: react or angular) which would parse the data and allow you to build interactive charts from updating data… this is a VERY big lift though.

1 Like
7

So as i wanted to look up the Power BI homepage i got automatically logged in, so it turnes out that we have Power BI in our office360 account, and so i could upload my data, create charts and even share with specific users and groups.

Only thing i can´t find out is how to update the data. I used import data and not file upload. Refresh buttons are there but don´t do anything. But I think somehow that will be possible.

2 Likes
8

Generally I have used PBI desktop which retains a path to the source. If I recall correctly you can store sources on onedrive for use in PBI as an app.

2 Likes
9

I have now pretty much the same problem as before.
To use the data with Power BI it has to be formated as a table (STRG+T in excel)
Now the new rows i add with dynamo are not formated as a table. Manual entries are.

So what do I do now? Is this a checkbox i have to check somewhere in excel? Or should i don`t use excel for my logging at all?

10

PowerBI can utilize raw excel data, or (even faster, more portable, and simpler to ‘just append to’) a CSV. Look into the query editor, but likely for guidance on that tool you’re best off in the PowerBI forums.

1 Like
11

Then CSV it will be, thanks!
I hate excel anyway :smiley:

1 Like
12

But now the semicolons make trouble…i don´t get it:

image
image1776×574 29.8 KB

image
image1685×921 71.8 KB

13

Semicolons are special characters in design script, and often cannot be escaped as they are coded pretty deep down. Best to utilize a string node, or a different demarcation (I like tabs or \t if you can’t use the comma).

1 Like
14

I see, will keep that in mind, thank you.

image
image1707×918 75.3 KB

2 Likes
15

I tried to switch to python for reading and writing but I´m struggling with writing. I get weird results. (Also switched to tabs)

image
image1335×311 27.5 KB 

with io.open(filePath,"w", encoding="UTF8") as file:	
	writer = csv.writer(file)
	writer.writerows(NewLog)

image
image1931×380 20.6 KB

Or:

with io.open(filePath,"w", encoding="UTF8") as file:	
	writer = csv.writer(file)
	writer.writerow(NewLog)

image
image1933×388 21 KB

with the dynamo write node it works so my read part should be right.
2 hours are enough for trying to get that working :smiley: hoping for help now.

16

Assuming the issue is the blank lines between entries, check out the solution here: windows - CSV file written with Python has blank lines between each row - Stack Overflow

17

Hm, that guy is using a for loop, i also tried that with the same weird result:

image
image1934×388 21.4 KB 

with io.open(filePath,"w", encoding="UTF8") as file:	
	writer = csv.writer(file)
	for Line in NewLog:
		writer.writerow(Line)

really not sure what is happening. I get all items in one row or i get “,” between every character…
edit: ok theres more to read on that stackoverflow thread, will try that

edit:

The mentioned binary mode WB makes things worse, now i get extra “” to my one row results…

image
image2897×377 24.8 KB 

with io.open(filePath,"wb") as file:	
	writer = csv.writer(file)
	writer.writerow(NewLog)

OUT = NewLog
18

The “solution” posted there has the write as:

with open('/pythonwork/thefile_subset11.csv', 'w', newline='') as outfile:

Translating that to your code, you get:

with open(filePath, 'w', newline='') as file:

They newline=“” parameter is the key; otherwise it uses the Python default which will not be interpreted correctly in Windows.

1 Like
19

Tried that, makes no difference in my results :confused:

Also tried to use “,” because i thought the tabs are messing things up.

But whatever i do, results are:

single line

"11495,DOK,09.10.2022,14:03:07,11495_BAU.rvt","11495,DOK,09.10.2022,18:33:56,11495_BAU.rvt"

or

tons of commas

1,1,4,9,5,",",D,O,K,",",0,9,.,1,0,.,2,0,2,2,",",1,4,:,0,3,:,0,7,",",1,1,4,9,5,_,B,A,U,.,r,v,t
1,1,4,9,5,",",D,O,K,",",0,9,.,1,0,.,2,0,2,2,",",1,8,:,3,0,:,3,2,",",1,1,4,9,5,_,B,A,U,.,r,v,t

now my reading also gave up, and so do I, getting only empty lists now…

with io.open(filePath) as file:	
	string = file.read().splitlines()

OUT = string
20

Post the .dyn and I’ll have a look later today. :slight_smile:

1 Like