Studio5000 Exporting Data to a Spread Sheet

RedwingRazor · Dec 18, 2018

I have been tasked with something I have never done before and not having much luck searching for a solution.

I have a cell that requires a badge scan to accept a particular inspection. I record the badge number with the year, month, day, hour and minute. The logic is written and works.

I created a UDT of 6 DINTs called Badge_Log. I then created an array of 200, data type Badge_Log.

I need to extract the data from that array to some sort of spreadsheet. That is where I am stuck.

Any ideas?

Geoff White · Dec 18, 2018

Have a look at AdvancedHMI. You can rip all that data out of the PLC and stick it in a DB or write it as a spreadsheet using your own program made is MS Visual Studio and Archie's magical comms drivers.

Otherwise you can do some freaky stuff with rslinx professional and DDE Topics, and VBA. Not recommended as its pretty fragile.

RedwingRazor · Dec 19, 2018

Thank you, I'll give it a whirl!

dmroeder · Dec 19, 2018

I'm a fan of AdvancedHMI, good stuff. Working with Excel in Visual Studio can be a bit tricky. He has sample projects here:

https://sourceforge.net/projects/advancedhmi/files/advancedhmi/3.5/SampleProjects/

If you feel like dipping your toes in python, I could help you get the data out and into a CSV file.

dmroeder · Dec 19, 2018

Well just in case you want to try the python route...

I assumed that you had a tag named Badges. it's members are BadgeNumber, Year, Month, Day, Hour, Minute. It's a length of 200, so something like Badges[0].BadgeNumber, Badges[0].Year and so on...

Install Python (2.7 or 3.5)
Download pylogix: https://github.com/dmroeder/pylogix
Create a new file in the pylogix directory, say csv_file.py
Paste the code from below into csv_file.py (you will have to edit the tag names and IP accordingly)
Run it, you should get a CSV file named data.csv with your data

Code:

import csv
from eip import PLC

'''
Variables you may have to change
Tag names will be generated based off of these parameters
so in this case you would end up with tag names like:
Badges[n].BadgeNumber, Badges[n].Year, etc...
'''
plc_address = '192.168.1.20'
base_tag_name = 'Badges'
udt_members = ['BadgeNumber', 'Year', 'Month', 'Day', 'Hour', 'Minute']
array_len = 200

'''
Build a list of tag names based off of the base tag name, size of the
array and the members of the UDT 
'''
tag_list = []
for i in range(array_len):
    tag_list.append(['%s[%d].%s'%(base_tag_name,i,m) for m in udt_members])


'''
Read the values of all of our tags in the list we built
'''
values = []
with PLC() as comm:
    comm.IPAddress = plc_address
    for t in tag_list:
        values.append(comm.Read(t))

'''
Write the values into a new CSV file
'''
with open('data.csv', mode='w') as csv_file:
    writer = csv.writer(csv_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    writer.writerow(udt_members)
    for v in values:
        writer.writerow(v)

RedwingRazor · Dec 19, 2018

Thank you. Another way to try

RedwingRazor · Dec 19, 2018

Thank you very much. Another way to try.

dmroeder · Dec 19, 2018

I noticed that in Windows, my above code will skip lines when writing to CSV.

Adding lineterminator='\n' as shown below will fix that:

Code:

writer = csv.writer(csv_file, delimiter=',', quotechar='"', lineterminator='\n', quoting=csv.QUOTE_MINIMAL)

RedwingRazor · Dec 19, 2018

Thank you again, dmroeder!

Studio5000 Exporting Data to a Spread Sheet

RedwingRazor

Member

Geoff White

Member

RedwingRazor

Member

dmroeder

Lifetime Supporting Member

dmroeder

Lifetime Supporting Member

RedwingRazor

Member

RedwingRazor

Member

dmroeder

Lifetime Supporting Member

RedwingRazor

Member

Similar Topics