sending PLC data/HMI Data to Excel Sheet

[noorloai 0]

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.

[Mark- 17]

Hello,

You do not say which HMI.

 

Edited 22 Jan 2016 by Mark-

[noorloai 0]

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

[Scadadoctor 0]

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.  

[Michael Walsh 265]

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.

[noorloai 0]

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

[noorloai 0]

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

[Crossbow 277]

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

[f3dps2 4]

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.

[PrecisionPLC 0]

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

[gclshortt 51]

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

[DiegoLic 0]

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.

 

 

[BE 11]

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.