Rslink, excel and mysql database

Respected Members;

Good day. I want to perform the following task:
1. successfully fetches the data via RSLINK / DDE to excel.
2. Now i want to send/ insert that data to MySql databse which is on the remote server instead of localhost after every 5 seconds.

How can i perform this task?

can rslink is able to directly send data to MySQL database?
or any third-party software needed to accomplish this task?

Regards

If you have VBA (Visual basic for applications) enabled in excel you can write some VBA code that will populate a DB.
There are functions in VBA where you can create a timer i.e. 5 seconds, when this triggers it calls the VBA code you have written to populate the DB, I have done this on an SQL DB & Excel, it will depend on if your IT Dept. will enable Macros in Excel (Many don't) as VBA is essentially a Macro.
I do not have the code as I'm now retired but there are many examples of creating a timed event in excel and how to write values to a DB.

Here is some code (I Think) to create a cyclic timer in VBA
' Create the timer
Sub my_onTime()
Application.OnTime Now + TimeValue("00:00:5"), "my_Procedure"
End Sub

Sub my_Procedure()
' Do what you want here i.e. populate the DB
my_onTime ''call my_onTime in my_Procedure, so that two Procedures call each other
End Sub

Here is a link to some code to populate a DB (need to alter to connect to MYSQL though.
http://tomaslind.net/2013/12/26/export-data-excel-to-sql-server/

@Turbohamad :
There is another way where you do not need to use RSLINK, this time you need to use PYLOGIX , it is a module in python to read/write data from AB PLCS.
At the same time time there is another module in python called OPENPYXL that is used to moved data from/to to EXCEL.
Both modules can be found on GitHub
PYTHON is also used to to move data to/from SQL , there are many books that show how to do it.
I have used pylogix and works out with no problem, still learning openpyxl and to improve my python I enrolled in a course from Udemy.

If you can program in VB.net

AdvancedHMI can be used to write Allen Bradley PLC data directly to MySql database without RSLinx.

There are some examples on the AdvancedHMI website.

I also have some projects that I have worked on if you need guidance

widelto said:

@Turbohamad :
There is another way where you do not need to use RSLINK, this time you need to use PYLOGIX , it is a module in python to read/write data from AB PLCS.
At the same time time there is another module in python called OPENPYXL that is used to moved data from/to to EXCEL.
Both modules can be found on GitHub
PYTHON is also used to to move data to/from SQL , there are many books that show how to do it.
I have used pylogix and works out with no problem, still learning openpyxl and to improve my python I enrolled in a course from Udemy.

Click to expand...

It's important to know what PLC model the OP is working with because pylogix only supports ControlLogix, CompactLogix and Micro800 PLC's.

I would be interested in helping out with this if you choose to use python @turbohamad. While I have python experience, I don't have much real world database experience. I think it would be a good exercise.

The key to programmatic SQL is first understanding the SQL language syntax. Once you know how to issue a select statement by hand (e.g. mysql -hhost -uuser -p database_name "SELECT * FROM table_name;" or "INSERT INTO table_name VALUES ..."), the library to connect from python becomes just another layer of indirection to solve a problem.

drbitboy said:

The key to programmatic SQL is first understanding the SQL language syntax. Once you know how to issue a select statement by hand (e.g. mysql -hhost -uuser -p database_name "SELECT * FROM table_name;" or "INSERT INTO table_name VALUES ..."), the library to connect from python becomes just another layer of indirection to solve a problem.

Click to expand...

I've played around with some basic examples of logging to databases. Not sure why, but they still feel a little mysterious to me. Maybe because I'm so conditioned to see things visually.

One thing to remember is on most IDE's you may need to add components or references i.e. SQL etc. as they are not loaded by default, there are plenty of articles on the web for connecting, populating & getting data in DB's, I do agree there are some weird things for example there are a number of ways to create the connection, not sure why perhaps some are legacy ways that have been super ceded but still there for backward compatibility.

I have written a couple of applications with Python and ControlLogix (Pylogix). One was to write data to Excel (using 2 methods: one for writing data while the Excel file was open, one to write data when the file was closed - BIG difference in the Python modules!). The other was to take line data from 30 PLCs and store it into SQL.

If you have any specific questions about Python or SQL let me know.

sigmadelta said:

... to write data to Excel (using 2 methods: one for writing data while the Excel file was open, one to write data when the file was closed - BIG difference in the Python modules!). ...

Click to expand...

Wait, is there a Python module that can write data incrementally to an eXcel file in place? Where is this magical module?

I ask because the .XLSX format is a PKZIP archive of (mostly) XML files. So in order to append data I thought I had to

• import the entire set of initial eXcel data e.g. to a Pandas DataFrame in memory,
• then append, again in memory, any new data to that DataFrame,
• then export that DataFrame to an essentially new .XLSX i.e. to the PKZIP archive.

I have written code to parse a .XLSX archive as a zip using the zipfile module, re-write the XML file with the data for the sheet of interest, and re-build the archive, again with the zipfile module, but that is essentially the same thing without the convenience of the Pandas method.

Obviously a .CSV, or even an SYLK (.SLK), can be easily appended to via open('....csv','a'); I assume that is not what is on the table here.

Wait, is there a Python module that can write data incrementally to an eXcel file in place? Where is this magical module?

Click to expand...

It's called xlwings: https://www.xlwings.org/

I had to use openpyxl as well for other functions because xlwings would not save xlsm files with EXCEL VBA.

Dera Sigmadelta;
I am new to python. I do not know how to access the controllogix PLC and where I write the code to read data from PLC and write it to mysql database.

can you help me to accomplish this task?

Regards

I dare say you could also do this quite easily with Node Red. There are nodes to read from an AB PLC (CIP) and also nodes to interface with mySQL. Doing it in Node Red probably requires less programming skill than using python.

As others have said, VBA can also interract with a database and you can even call a stored proceedure in the database.

turbohamad, if you open these examples in notepad or an IDE such as PyCharm, you will see examples of python code to read/write to the ControlLogix.

https://github.com/dmroeder/pylogix/tree/master/examples