Read Tags in Excel From Studio 5000

[cwal61]

Factory Talk View Machine Edition. Use ME_DataStore ActiveX object and map to any network location.

 

[turbohamad]

Respected Professionals;

Before going to learn Python I want to use xl reporter to fetch data from PLC through rslink and save it to the database. I download the xl Reporter but there is no option for connecting to Rslink.

On which site I download it for a try.

Regards

 

[widelto]

When you download xlreporter, there is a file called getting results, please read it first and you will be able to make a link to opc servers like rslink professional .

 

[andrewwei]

Thank you so much

Here are a couple dozen lines of python to use pylogix to retrieve data over ~9s at ~1Hz from a PLC (in this case a Micro820), then write those data to a MariaDB/MySQL database. The last two sections, which could be extracted and placed in a separate script e.g. to run once a day, and delete any data older than a week from the database, retrieve those data from the database and write them to an eXcel workbook. The IP addresses of the PLC and the MariaDB/MySQL servers are arbitrary. Explanations of each group of statements can be found via Google. How to setup the MariaDB/MySQL server for such access is left as an exercise for the user; one approach is available at this link. The Micro8xx code is shown in the image below. After that, the beginning of the MariaDB/MySQL database table data; the full table is also attached.

### Cf. https://www.plctalk.net/qanda/showthread.php?t=132004

import sys

import time

import pylogix

import datetime

import pandas as pd

import MySQLdb as Mdb

### Parse command-line arguments:  PLC IP address; MariaDB/MySQL DB server IP address; DB username

plcip = (['192.168.1.160']+[a[8:] for a in sys.argv[1:] if a[:8]=='--plcip=']).pop()

ip = (['127.0.0.1']+[a[5:] for a in sys.argv[1:] if a[:5]=='--ip=']).pop()

usr = (['test_log']+[a[7:] for a in sys.argv[1:] if a[:7]=='--user=']).pop()

### Ensure DATABASE 'test_log' and TABLE 'log' exist on MariaDB/MySQL server

Mdb.connect(host=ip,user=usr).cursor().execute('CREATE DATABASE IF NOT EXISTS test_log')

cu = Mdb.connect(host=ip,user=usr,db='test_log',autocommit=True).cursor()

cu.execute('CREATE TABLE IF NOT EXISTS log (tim datetime NOT NULL,tag varchar(32) NOT NULL,val double NOT NULL,PRIMARY KEY tn (tim,tag))')

### Sample data from PLC at ~1Hz, write data to MariaDB/MySQL DATABASE.TABLE 'test_log.log'

with pylogix.PLC(plcip) as plc:

  plc.Micro800 = not ('--no-micro8xx' in sys.argv[1:])

  for i in range(10):

    time.sleep(1.001)

    dt = datetime.datetime.utcnow()

    cu.executemany('INSERT INTO log VALUES (%s,%s,%s)',[(dt,tag,plc.Read(tag).Value,) for tag in 'et_ms c.Year c.Month c.Day c.Hours c.Minutes c.Seconds'.split()])

### Retrieve data from 'test_log.log' into list of dicts

cu.execute('select * from log')

ads = [dict(ts=None)]

for ts,tag,val in cu:

  if ts.isoformat() != ads[-1]['ts']: ads.append(dict(ts=ts.isoformat()))

  ads[-1][tag] = val

cu.close()

### Write list of dicts to eXcel workbook

pd.DataFrame.from_records(ads[1:],index='ts').to_excel('test_log.xlsx')

​

View attachment 60966
​

$ mysql test_log -e "select * from log"

+---------------------+-----------+------+

| tim                 | tag       | val  |

+---------------------+-----------+------+

| 2022-02-09 20:37:28 | c.Day     |    9 |

| 2022-02-09 20:37:28 | c.Hours   |   15 |

| 2022-02-09 20:37:28 | c.Minutes |   38 |

| 2022-02-09 20:37:28 | c.Month   |    2 |

| 2022-02-09 20:37:28 | c.Seconds |   25 |

| 2022-02-09 20:37:28 | c.Year    | 2022 |

| 2022-02-09 20:37:28 | et_ms     |   46 |

| 2022-02-09 20:37:29 | c.Day     |    9 |

| 2022-02-09 20:37:29 | c.Hours   |   15 |

...

​

Thank you so much. You guys are great

 

[drbitboy]

...

I applied a little Python-fu, found a better way to convert the dict-style table to a Pandas DataFrame, and got it down to a dozen and a half statements, wheee!

[Update: the script in the attached .zip should not have the random spaces that are in the post]

### Cf. https://www.plctalk.net/qanda/showthread.php?t=132004

import time

import pylogix

import pandas as pd

import MySQLdb as Mdb

### Parse command-line arguments:  PLC IP address; MariaDB/MySQL DB server IP address; DB username

plcip = (['192.168.1.160']+[a[8:] for a in pd.io.stata.datetime.sys.argv[1:] if a[:8]=='--plcip=']).pop()

ip = (['127.0.0.1']+[a[5:] for a in pd.io.stata.datetime.sys.argv[1:] if a[:5]=='--ip=']).pop()

usr = (['test_log']+[a[7:] for a in pd.io.stata.datetime.sys.argv[1:] if a[:7]=='--user=']).pop()

### Ensure DATABASE 'test_log' and TABLE 'log' exist on MariaDB/MySQL server

Mdb.connect(host=ip,user=usr).cursor().execute('CREATE DATABASE IF NOT EXISTS test_log')

with Mdb.connect(host=ip,user=usr,db='test_log',autocommit=True).cursor() as cu:

  cu.execute('CREATE TABLE IF NOT EXISTS log (tim datetime NOT NULL,tag varchar(32) NOT NULL,val double NOT NULL,PRIMARY KEY tn (tim,tag))')

  ### Sample data from PLC at ~1Hz, write data to MariaDB/MySQL DATABASE.TABLE 'test_log.log'

  with pylogix.PLC(plcip) as plc:

    plc.Micro800 = not ('--no-micro8xx' in pd.io.stata.datetime.sys.argv[1:])

    for i in range(10):

      dt = (time.sleep(1.001),pd.io.stata.datetime.datetime.utcnow(),)[-1]

      cu.executemany('INSERT INTO log VALUES (%s,%s,%s)',[tup for tup in [(dt,tag,plc.Read(tag).Value,) for tag in 'et_ms c.Year c.Month c.Day c.Hours c.Minutes c.Seconds'.split()] if not (None is tup[-1])])

  ### Retrieve data from 'test_log.log', insert into Pandas DataFrame, write to eXcel workbook

  n,df = cu.execute('select * from log'),pd.DataFrame(index=pd.Series(name='timestamp',dtype=float))

  for ts,tag,val in cu: df.loc[ts,tag] = val

  df.to_excel('test_log.xlsx')

​

 

[drbitboy]

Dropped another line. Heh.

[Update: and another].

 

[JTCat]

Have you tried filling in the data manually?
Run RS Linx on PC and assign a topic name to the processor and retrieve the data that way?

In the Excel cell you would type =RSLINX|TOPICNAME!'TAG,L1,C1'

So if I wanted to retrieve the contents of a tag named Cycle Count from a processor I assigned topic name Machine1 to it would type this in a cell =RSLINX|Machine1!'CycleCount.ACC,L1,C1'
Of course, macros and some other things have to be enabled for this to work in Excel, but it's the way I do it.