Read/Write from Siemens S7-300 DB's from Excel

[FONT=&quot]One of our customers has a Siemens S7-300, CPU 315-2DP PLC which they want to load a recipe into. The recipe is generated in Excel.[/FONT]

[FONT=&quot]Does anyone know of a way that excel could communicate directly with DB's in this PLC?[/FONT]

Not directly no.

Would be happy for someone to prove me wrong though, would be incredibly useful.

Do you have an HMI or SCADA software already connected to the S7?

[FONT=&quot]Yes, I have an iFix 6.0 application connected to the S7. We have considered to import it through iFix but that would limit the change in recipe to the one computer running iFix. We were hoping there could be another way so everyone with excel in the network could upload it from their own computer.[/FONT]
[FONT=&quot]I have done some test through an OPC server and VBA for excel but haven't found an easy way of doing that either. [/FONT]

OPC server can definitely push and pull data from an S7-300. Have multiple sites doing the same thing.

If the IFIX Machine is on your corporate network then the best way would be to have the recipes stored on an SQL Database on a server & allow the IFIX machine to select a recipe & download it. It seems odd that you want anybody with a PC to download a recipe, what happens if two decide to do it in short succession ?. The best way is to create the recipes in an SQL database, more secure, can hold loads of recipes so a recipe could be selected. Bonus is that the same data base could be used to log batch information to produce reports these reports could easily be written in protected mode in Excel so anybody could generate a report.
Excel could also be used to create recipes to populate the data base.
We once had a system where recipes were done on the company management system but could also be done on the local Scada. (IFIX) However to be more secure all recipe population was removed except for our product development. There was also a reporting system to generate reports but was very flaky, not easy to use & the supplier had gone without leaving the source code. I wrote an Automated excel spread sheet to allow everybody with excel to select & print reports of batches & details. These were pretty secure as the code was locked, only selected people on the network had permissions to access the data.

To summarize what parky said:

Excel -> SQL Database -> iFix -> PLC

I did it where recipes could be modified with Excel and written to and read from the SQL Database, and iFix (or in my case, Siemens WinCC) could select a recipe for the production.

If your company does not have SQL Server you could use Server Express, this allows a Database but limited to 4Gb and is free, however I'm not sure if this is for personal use only.

Yes, SQL server might be the best way to go here. But that would require script for adding recipes from Excel to SQL database and scripts in iFix to import recipe to PLC. And I guess I would require more computer power in order to keep the SQL server up and running.
I was hoping to keep the scripts and software at a minimum level and just import it directly from excel to PLC.


BTW, SQL server express is since 2017 updated to 10Gb and is free to use both personally and for commercial use.

Excel read DB via libnodave:

https://www.youtube.com/watch?v=4fuRY75GeNU

http://plc2k.com/excel-read-data-from-s7-300-with-libnodave/

Lower down the page someone has modified to also write to DB.

One other thought, Some years ago we had a system that had Beijers E series HMI's on them. These are capable of handling recipes and there was a utility FTP program that could read & write CSV files to the HMI for recipes, these could be edited in excel & saved then sent down to the HMI, here the operator could select the recipe for use. The E series has been superseded but although I have not used the later ones I bet they have a similar function.
The original programs were called HMI tools & for the cost of a small HMI & the software this could be an option. You can also log data to the HMI & download it. Again it has the same format as the recipes.
Update: I have looked at the IX series & it looks like only IX dev can download, there does not seem to be any utility program that I can find.
Although SQL Server appears to be a bit of a monster, I have it running on an old dual core Pentium, 4gb of ram win 7 and a Scada system so does not seem to cause any problems, The connection to the DB only happens when I select a recipe, however the logging is permanently connected, in actual fact at the same time I have GX works running as well. So I don't think it would cause problems.

Just my opinion.

Recipes are not trivial. I agree with what Parky says.
You should consider such things as limiting the access to both changing the recipes, and to activating the recipes in the PLC. This should require login.
You should consider how to ensure that the recipe data are transferred consistently. If not, you risk that the machine operates with ½ the recipe data being new and the other ½ being the old data, with potentially catastrophic results.

Usually, if you use recipe handling features in the HMI or SCADA software, it is taken care of properly. These canned HMI/SCADA recipe functions usually also have the ability to export and import to/from CSV and/or Excel.

You write that you could do it in iFix. I am sure that if you set up so that the Excel recipe data is stored on a network drive, you can have both that the recipes are handled properly (in iFix) and that many people can edit the recipes (in Excel).

Jesper is right. It would be quite easy to implement a directory on the IFIX machine or a server than contained the recipes in CSV format, not too difficult to write scripts to populate a pull down box containing a list of recipes that could be selected & transferred to the PLC variables used in the I/O driver.
All you need then is all the other PC's have access to that directory.
As an example the filename of the CSV file could be the Recipe Name.
Like I mentioned before we had a system that held recipes on an SQL server, these recipes were large to say the least, at 2,000 words long they contained considerable amount of data for example each stage had 6 types of product not only the weights but descriptions of the products and other data like temperature set points speeds, type of addition etc. and a total of up to 16 stages, QA test information and so on.
The recipes were selected at the Scada, downloaded to the respective PLC, then uploaded to the HMI and stored for operator use. The recipes were so large that they were larger than the HMI could cope with so each recipe was broken into 3, put in separate directories on the HMI so on selection the PLC code had to effectively download 3 recipes from the HMI.

I Agree with both Jesper and parky. I have tried to convince our customer that this is the best solution, but they still want and excel solution.

BryanGs solution with libnodave is exactly what I was hoping for. I have tried it and it works fine in pre win10 environments, but I have not been able to connect under win10 or winserver2016 as our customer has. If anyone has any experience with these environments, please let me know.

I have found another solution that works for win10/2016server. It is called IP-S7-LINK an die S7 mit TCP/IP and is developed by a German company named Tragger. Unfortunately, this software is quit expensive so if anyone has any other alternatives it would be appreciated.

Just had a quick look on siemens website & found this, not looked into it but it might be worth a look, I believe there is a siemens function block ftpX00CMD
(Ftp1X00Cmd) that implements a FTP client into the S7-CPU. The block can perform the following FTP commands:
CONNECT
STORE
RETRIEVE
DELETE
APPEND
DISCONNECT