Automatic CSV Data Processing for Industrial Reports and Dashboards

In the world of industrial automation, CSV (Comma Separated Variables) file formats are very common and an excellent way to transfer data between systems for report generation and performance dashboards.  The process required for automated CSV File importing are:

  • Automated CSV File Collection from Remote Sources

  • Automated Processing of CSV Files – Mapping to their Format

  • Logging CSV File Data to a Relational Database for Archiving

  • Subsequently Using CSV Data for Reports and Dashboards

 

Automatically Collecting CSV Files with an FTP File Collector

Common sources of CSV Files:

  • HMI Panels logging data to CSV files
  • Injection Molding machines logging to CSV files
  • Process Analyzers logging data to CSV files
  • Smart Recorders logging data to CSV files
  • Microsoft Excel Exports of data to CSV files

Data logging can be considered an export function for generating CSV files.  Often, data logging is spread over many files, generated daily or monthly to limit file sizes.  CSV files are typically stored in a local directory or in the equipment creating them.  Dream Report offers several features to completely automate the collection, processing and statistical use of CSV Files.  First, Dream Report offers an Automated FTP File Collector Utility.  This tool can be set to automatically check remote locations for new or updated CSV files, and then copy them to a local directory for processing.  Dream Report then offers a CSV Driver to automatically monitor CSV files in a directory and either automatically import new data that has been added to a file, or import a new file in its entirety.

Automatically Processing CSV Files for Data Extraction and Archiving

Dream Report offers the ultimate in configuration flexibility for mapping to and importing data from CSV files:

  • Automatically Processing CSV Files for Reports and Dashboards is Easy with Dream Report

    CSV File Driver Configuration

    CSV files are not always C = Comma, separated. There are other characters that can act as a delimiter.  Dream Report allows the selection of other characters like semicolon, tab and space.

  • Sometimes there is Header information that needs to be ignored. There are “Ignore X Rows” configurations in Dream Report.
  • Data is almost always time-stamped in a CSV file and there are no standards for Date and Time formats. Dream Report delivers a host of common formats and also enables you to completely customize its ability to understand your specific time format.
  • The logging of data into a file generally takes one of two forms, a Column Item Structure with Tags across in Columns and Data arranged in Rows and a Simple Database format where there are columns for time, tags, data. In this latter format, Dream Report needs to resolve all Tags by reading them out of the Tags column and must then store data and times appropriately.  Dream Report handles both formats.
  • In addition, logging may be done based on UTC (Coordinated Universal Time – yes, you’d think it should be CUT, but it’s UTC – the time starting at the Prime Meridian. They also call it Universal Time Coordinated) and you’ll want to convert that time to the time of your local time zone.  There’s a check box for that in Dream Report.
  • Dream Report will automatically monitor a directory and check it on a regular (user defined) interval. Dream Report also offers the ability to keep or delete files once processed.

The processing (CSV File Import) enables Dream Report to treat that data source similarly as it would a real-time data source.  The results will be archived by Dream Report by using its Data Logger function.  The output of the Dream Report data logger can be a simple .mdb file (Microsoft Data Base – Access), a Microsoft SQL Server type database (SQL Express or SQL Compact), or any DSN (Data Source Name) such as My-SQL or Oracle.  Once archived, this data can be combined with any other that Dream Report is accessing to generate reports and dashboards.

 

Automatically Exporting Data to a CSV File with Dream Report

Why would Dream Report export data?  The most common reason is to import it in to another application for further analysis, or feed data to an archiver to create a permanent record.

Typical examples of reasons to Export CSV Data:

  • Import data to a Historian to create a permanent record for all to use.
  • Export Manual Entry Data for use in other systems.
  • Export Raw Data or Results for use in Excel (Dream Report can create Excel files directly too).

Dream Report offers a great deal of flexibility in generating CSV files.  CSV files can be created from a number of Dream Report objects including single data objects and the more powerful table objects.  You can combine the data collection and analytic capabilities of Dream Report, along with its scheduling ability to generate the right information at the right time in the right format for integration with your other software solutions.  Dream Report will automatically deliver that data to the location of your choice through Windows transfer or File Transfer Protocol (FTP).

Review this Blog for using CSV Files with Historian products.

The bottom line?  If you’re dealing with CSV files, then you should be talking with ODS about DR.  🙂