OPC data to excel spreadsheet

I am using Kepware as an OPC server for RSLogix5000. I usually use a software called canary trending to log and trend the data. I was wondering if there is a way to transfer data of a few tags from Kepware straight into an excel spreadsheet. Can this be done through formulas within excel or would I have to find a way to configure excel as a logger administrator through Kepware? Any help would be appreciated.

That .CSV import/export is for tag configuration - I'm not sure if that's what the OP was asking for. There are many threads on reading current OPC values via DDE from within Excel. This is not ideal for logging data.

If you wish to log data from an OPC source to an SQL database use FactorySQL or a similar program/utility. Some can log to an Excel spreadsheet, although I wouldn't recommend that approach as compared to a database. I would contact Software Toolbox for more information on their many economical utilities that fit this area. I think the OPC Datahub may be the application.

rsdoran said:

http://www.kepware.com/Products/feature-csvimportexport.htm

Click to expand...

I am old and my memory bad, but do know Kepware will enter data into an Excel worksheet without anything else involved, at least it did 4 years ago.

Talk to Kepware if using a paid version.

Oh, Cool! They're good guys at Kepware. I'm sure they'll tell you how to do it even with their free 2 hour trial.

rsdoran said:

I am old and my memory bad, but do know Kepware will enter data into an Excel worksheet without anything else involved, at least it did 4 years ago.

Talk to Kepware if using a paid version.

Click to expand...

I do not understand that response.

I was agreeing with you. I also suggested that Kepware tech support would be willing to assist anyone with their software for free, regardless of whether you have a paid version.

Back to the original question ...

"Can this be done through formulas within excel"

Yes it can.
I have been doing DDE and OPC calls from within Excel for some time. I have even done calls through Excel VBA.
There is a lot of information out on the internet. Do some homework and experimentation. It can be frustrating or a lot of fun.

Thank you all for the input.

Yes, there are TONS of posts about DDE calls from within Excel. My point was that this works well for realtime values, but doesn't help you much for historical datalogging, which is what the OP asked about. You could do it with VBA script, but that's technical and knarley - even for a programmer.

Oakley said:

Back to the original question ...

"Can this be done through formulas within excel"

Yes it can.
I have been doing DDE and OPC calls from within Excel for some time. I have even done calls through Excel VBA.
There is a lot of information out on the internet. Do some homework and experimentation. It can be frustrating or a lot of fun.

Click to expand...

I do recall once that when I was doing some surfing for DDE code, I did run accross code connecting to HistData for Wonderware. I recall that the code was somewhat simplistic.

But in today's world, historians are typically in some sort of database. MSQuery could be used for this type of application.

"You could do it with VBA script, but that's technical and knarley - even for a programmer."


Why not try? Aren't we all programmers to some degree.

There are lots of reasons why there are better approaches. That quote was referring to hacking Excel to be a DDE historian. Why wouldn't I recommend that? To begin with data logging should be done to a database, not a spreadsheet. Excel has row limitations (I think 65k), it can't be effectively queried, linearly increases in memory usage, and wasn't designed to deal with large amounts of data. That's why Microsoft sells Access and SQL Server. Next, setting up Excel to log data automatically is clunky at best. It requires autostarting the application (startup folder or registry), which requires that the computer auto log on the user too. You have to decrease security to allow Macros/VBA to auto-run, then hope that a user doesn't close the spreadsheet. Even if you're a good (professional) programmer, DDE to an OPC server in VBA in Excel is clunky and error handling sucks. Microsoft shifted direction a long time ago and they may or may not still formally support this. Once you get this right, you'll necessarily need to separate your logging into separate files. This was the worst part of HMIs in the 90s before every vendor learned to store historical data in some form of database. The system would slow down (especially graphs) as file size increased, and file corruption is common when dealing with lots of data as flat (text) file formats. Read only concurrent access is possible, but doesn't work well. You're dealing with a system that is hard to manage, hard to get useful data out of, and isn't scaleable.

It'd be an interesting exercise in programming to see if you could do it. For a production system, just like you said, use a historian or a data-logging utility - that's what they were designed for. They're not that complicated or expensive. There's no reason to do custom coding unless you really need to.

Oakley said:

I do recall once that when I was doing some surfing for DDE code, I did run accross code connecting to HistData for Wonderware. I recall that the code was somewhat simplistic.

But in today's world, historians are typically in some sort of database. MSQuery could be used for this type of application.

"You could do it with VBA script, but that's technical and knarley - even for a programmer."


Why not try? Aren't we all programmers to some degree.

Click to expand...

ControlLogix <--> Excel

I'm an engineer at I/Gear, and we have a software called Data Transport Utility that can do that.

Our software, which runs as a service on a host PC, has a native driver that can connect to a ControlLogix PLC over Ethernet to pull the tags in to internal datapoints. We have an OPC connector as well, but I usually use the ControlLogix connector when possible.

Once the internal datapoints are populated, the data is available to any other connected device. Our software can also make a connection to a local or remote Excel spreadsheet and export the data out. The command to do this would typically be event-based, e.g. it would only write data to the spreadsheet on the change of some significant piece of PLC data (part number, serial number, etc.).

There is also a scripting component if you need to perform any sort of advanced logic on the data. The whole process would complete in a matter of milliseconds, depending on how may tags you need.

To set up the above process might take around twenty minutes.

If you want to try it out, you can download a free demo at http://www.igearonline.com/downloads/default.asp

Let me know if you have any questions.

Mike Atwell
[email protected]

Yes, thank you very much for that advertisement.

Notice the date of the last post?

I did. My experience in manufacturing is that if it was a problem last month, it is still a problem today. Chances are he is still looking for a solution.