noorloai

sending PLC data/HMI Data to Excel Sheet

13 posts in this topic

Hello all,

I am using NJ501-1300 CPU, connect to HMI Via Ethernet and would like to capture data from the PLC/HMI to excel spread sheet.What is the best way to do that and can you give me some outline how to start? Is there any manual should I follow to help read data in an excel sheet?

Thanks for your support.

Share this post


Link to post
Share on other sites

Hello,

You do not say which HMI.

 

Edited by Mark-

Share this post


Link to post
Share on other sites

Hi, 

I am using HMI  NS8-TV01B-V2 system V8.6 programmed using CX-Designer and Sysmac studio for my PLC NJ1500-3100. I need to read som data in Excel spread sheet.

any help would be appreciated

Share this post


Link to post
Share on other sites

The way we did this for another Omron project was using a Microsoft .NET class & embedding it in MS Excel. You may be able to download from www.controlsystemtools.com.  

Share this post


Link to post
Share on other sites

I am not sure if you need a "live" view of the NJ memory or if you just need a report (end of shift data for example).  If you just need a report, you can use ladder instructions to create a .csv file on the SD card in the NJ and then use FTP to get it off of the memory card and onto your PC.  You can then open the file in Excel.

Share this post


Link to post
Share on other sites

As a start Up I need to get end of shift data report.

Share this post


Link to post
Share on other sites

Hi,

Michale, I am working on this way programming the SD Memory card. On the other side I am just wondering if its possible to get the data from the HMI (PT) I am using NS8?

I found some information “using the operation log function” option in the HMI to get the result in Excel sheet.  So just wondering if anyone tried it before, and gave you some result.

Cheers

Share this post


Link to post
Share on other sites

Most HMI can store logged data to CSV format, and CSV has no format and can be opened in Excel.

Share this post


Link to post
Share on other sites

I have experience doing this for logging PLC information, and am using the .csv stored on the PLC memory card option. Mainly because the Sysmac Studio software makes it incredibly easy to store data in the exact format you want, to the exact filename you want in the exact folder you want.

For my filename, I use a string concatenate:

CONCAT 'Log', DateStr, '.csv' where DateStr is a string conversion of the current date "2016-03-03"

If you want it in a separate folder on the SD card, simply change the first string to '/Folder1/Log'

Then, use the FILEPUTS function block (along with fileopen and fileclose of course) to put a string into your csv file.

Note: Format the string as 'Data1,Data2,Data3$r' using CONCAT to add new lines of comma separated values.

Then, you can use an excel macro to execute a command line FTP get from the memory card and import the information directly into your formatted workbook.

1 person likes this

Share this post


Link to post
Share on other sites
On 14/02/2016 at 7:08 PM, Crossbow said:

Most HMI can store logged data to CSV format, and CSV has no format and can be opened in Excel.

can you explain in more detail how to store logged data to CSV format from HMI, I am using NA series 15 inch along with NX102-9000

Share this post


Link to post
Share on other sites

https://accautomation.ca/omron-cp1h-advancedhmi-communication/

The above post may help you out.  AdvancedHMI can communicate to many devices. The Omron PLCs can communicate Host Link (C Mode Commands) Serial – RS232, RS485, RS422. It can also communicate Ethernet FINS.

AdvancedHMI is written in VB.net and runs on visual studio. Here is a link to show you how to then communicate to an excel spreadsheet.
https://www.tutorialspoint.com/vb.net/vb.net_excel_sheet.htm

Regards,
Garry

Share this post


Link to post
Share on other sites
On 2/3/2016 at 4:13 PM, f3dps2 said:
On 2/3/2016 at 4:13 PM, f3dps2 said:

I have experience doing this for logging PLC information, and am using the .csv stored on the PLC memory card option. Mainly because the Sysmac Studio software makes it incredibly easy to store data in the exact format you want, to the exact filename you want in the exact folder you want.

For my filename, I use a string concatenate:

CONCAT 'Log', DateStr, '.csv' where DateStr is a string conversion of the current date "2016-03-03"

If you want it in a separate folder on the SD card, simply change the first string to '/Folder1/Log'

Then, use the FILEPUTS function block (along with fileopen and fileclose of course) to put a string into your csv file.

Note: Format the string as 'Data1,Data2,Data3$r' using CONCAT to add new lines of comma separated values.

Then, you can use an excel macro to execute a command line FTP get from the memory card and import the information directly into your formatted workbook.

Could you show me how yo make it, please? I've been doing that for a while and still doesn´t create the file csv.

 

 

Share this post


Link to post
Share on other sites
1 hour ago, DiegoLic said:

Could you show me how yo make it, please? I've been doing that for a while and still doesn´t create the file csv.

I have been successfully using the following function block. Link below.

There is a guide for using it in the ZIP file if I recall correctly, really easy to use and works well. In my case I have multiple instances of it, and with a little bit of code in place to prevent them all executing at once, they work great.

I did the CONCAT for the filename in some inline structured text (I want a new file to be created every month), wrote the results of that CONCAT to a variable, which was then used as the file name variable in the function block. If the file with that name already exists, then it doesn't create a new file. Otherwise it creates a new file as per the file name variable.

There is also a column names input, so the block will make the first row of the CSV file a set of column names when it creates a new file. Very handy :-).

If you decide not to actually use the function block, you can still open it to see how it runs, which might help you to work out why your code isn't creating the CSV file too.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now