Configuring Omron CJ1M PLC Reporting with CSV and MS Access

Overview: Building a Reporting Pipeline Around the CJ1M

The Omron CJ-series CJ1M is a compact modular PLC that frequently appears as the controller of record on packaging, material-handling, and OEM machine cells. It supports an HMC-EF CompactFlash memory card, an Ethernet option board or dedicated module (CJ1W-ETN21), and the Controller Link module (CJ1W-CLK21), all of which can be combined to build a self-contained reporting pipeline that ends in a relational database on the office side of the network.

The canonical workflow is:

1. The CJ1M writes production, alarm, and process values to a CSV file on the CompactFlash card using the File Memory operations built into the CPU.
2. An automation computer or SCADA station connects to the PLC's Ethernet module and pulls the CSV over FTP.
3. CX-Supervisor (or an in-house application) parses the CSV and inserts records into a Microsoft Access (.accdb / .mdb) database using ODBC.

This article covers the hardware prerequisites, the on-PLC file format, the FTP transfer setup, the CX-Programmer project storage convention, and the CX-Supervisor to Access linkage that the original author asked about.

Hardware Prerequisites

CompactFlash Memory Card Architecture on the CJ1M

The CJ1M CPU recognizes an HMC-EF CompactFlash card as "File Memory". Three system files are written to the card automatically when a project is downloaded from CX-Programmer to the CPU:

The card is shared between these system files and any user CSV file the application program creates. The File Memory is addressed as a sequential byte stream; CP1H/CJ1M CPUs expose it through the special I/O area and through ladder instructions FREAD, FWRITE, and CMND.

Generating CSV Files on the PLC

CSV is the lowest-common-denominator exchange format between an embedded controller and a PC database. Two approaches are field-proven on the CJ1M:

Approach 1 - STL string build + FWRITE

The application constructs an ASCII row in a word-mapped DM/EM buffer, terminates it with 0D0A (CR + LF), and writes the row to the card using FWRITE. A typical record of 64 bytes (32 words) per line is a good compromise between throughput and parse complexity.

;--- Build one CSV line in D20000..D20031 (ST-style STX blocks) ---
; Tag    , Value , Unit , Timestamp , Status
; string constants come from EM bank (e.g. E0_20000 contains 'TEMPERATURE')
;
; Using CX-Programmer MOVE/MOV$ ladder:
;   D20000 = $5445   ; 'TE'
;   D20001 = $4D50   ; 'MP'
;   ... etc
;
; Write to file "REPORT.CSV" on the memory card:
;   FWRITE  0  D20000  32  "REPORT.CSV"  &0
;
; Trigger every minute with a clock-pulse bit (P_1min):

Strings are written using the instruction variant FWRITE$ when CX-Programmer is configured to treat the source words as ASCII. The third operand is the word count, and the last operand is the file mode flag (0 = create/append, 1 = overwrite, 2 = create-or-error).

Approach 2 - Sequential file log via ladder task

For high-volume logging, partition the EM area into 32 KB data blocks, write a full block at a time using FWRITE, and rotate the file name on a daily basis: REPORT20240315.CSV. CX-Programmer's date/time variables (_Y, _M, _D) feed the filename so the file system stays self-organized.

Storing the CX-Programmer Project (.CXT) on the Flash Card

The CX-Programmer project can be exported to the CompactFlash card as a .cxt file using File > Save As with File of type set to CX-Programmer project (.cxt), then selecting the memory-card drive letter in CX-Programmer's PC Card Explorer. The .cxt format is the ladder-visible binary representation of the project and is the canonical way to ship a project alongside the controller to a remote site.

Worked steps in CX-Programmer v9.x:

1. Connect online to the CJ1M and execute PLC > Transfer > From PLC to retrieve the running project.
2. Select File > Save As and choose the CX-Programmer Project Files (.cxt) filter.
3. In the save dialog, navigate to the memory card (the HMC-EF shows as a removable drive on the engineering PC via a USB CF adapter, or via FTP when the Ethernet module is online).
4. Save with a descriptive name such as MACHINE01_LINE3.CXT.

To reload the project at commissioning: copy the .CXT back to the engineering laptop, open it in CX-Programmer, and transfer to the new CPU. Symbol files (SYMBOLS.SYM) and rung comments (COMMENTS.CMT) bundled inside the .CXT are preserved.

Ethernet Module CJ1W-ETN21 Configuration for FTP

The CJ1W-ETN21 is the only CJ-series Ethernet unit that exposes an FTP server capable of browsing the CompactFlash card. The ETN11 supports FINS/UDP and FINS/TCP only. Refer to the Omron CJ1W-ETN21 product page and the Ethernet Units Operation Manual (W463) for the parameter set.

The FTP root path on the CJ1W-ETN21 corresponds to the file-memory bank that is currently mounted. After the memory card is inserted and recognized, the files appear at the FTP root:

/
  /REPORT.CSV
  /REPORT20240315.CSV
  /AUTOEXEC.OBJ
  /SYMBOLS.SYM
  /COMMENTS.CMT
  /MACHINE01_LINE3.CXT

Pulling CSV Files by FTP from the Automation Computer

A Windows scheduled task or a small Python script can pull the CSV at any interval. The simplest scripted pull is:

# pull_reports.py - sample FTP retrieval
import ftplib, datetime, os

HOST = "192.168.10.21"
USER = "omron"
PWD  = "<set in ETN21>"
OUT  = r"C:\Reports\Inbox"

with ftplib.FTP(HOST) as ftp:
    ftp.login(USER, PWD)
    files = ftp.nlst("*.CSV")
    for f in files:
        local = os.path.join(OUT, f)
        with open(local, "wb") as fp:
            ftp.retrbinary(f"RETR {f}", fp.write)
        print("Pulled", f, os.path.getsize(local), "bytes")

Run this script every 5 minutes via Windows Task Scheduler. After each successful pull, append a row to an Access log table recording the file name, file size, pull timestamp, and source IP - this provides audit traceability that is often required for ISO 9001 environments.

Controller Link as an Alternative Network Path

If the supervising computer is already on a Controller Link token-ring network (typical in older Sysmac installations), the CJ1W-CLK21 module is preferred over Ethernet. Controller Link carries FINS messages at up to 2 Mbps on twisted pair, but does not provide FTP. CSV retrieval over Controller Link is implemented by ladder code that reads the file and broadcasts its contents in a FINS file-memory read (FREAD served via a host-link computer). Refer to the Controller Link product page and the Controller Link Units Operation Manual (W309).

For plants with both networks, install both modules in the same rack and configure CX-Integrator routing tables so that the FINS source/destination tables forward file-memory reads transparently.

CX-Supervisor and MS Access Integration

CX-Supervisor (SCADA runtime from Omron) is the path of least resistance to MS Access because it binds data points directly to ODBC data sources. Steps to connect:

1. Open the CX-Supervisor project for the machine cell.
2. On the engineering PC, configure a 32-bit ODBC System DSN named, for example, REPORTS_ACCESS, pointing to the Access database file (e.g., C:\Reports\Reports.accdb).
3. In CX-Supervisor, open Database > Database Connection and link the DSN.
4. For each CJ1M tag, set the Database tab properties: Save Changes = On Change or Periodic (10 s), Table = ProcessLog, Field = TagValue.
5. Build an Access table with at least the columns: TagName, TagValue, Timestamp, Quality, SourceFile.
6. Run the runtime and verify rows appear in Access using a linked table or a small Access query.

If a SCADA layer is undesirable, an alternative is to import the CSV directly into Access through a VBA macro bound to an Access form button:

' Access VBA - import the latest CSV from the FTP inbox
Sub ImportLatestCSV()
    Dim fso As Object, f As Object, folder As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\Reports\Inbox")
    For Each f In folder.Files
        If LCase(Right(f.Name, 4)) = ".csv" Then
            DoCmd.TransferText acImportDelim, , "RawCSV", f.Path, True
        End If
    Next f
End Sub

Run ImportLatestCSV() on a timer event or on the After Update of a "Pull Now" button.

Access Database Design for PLC Reports

A well-normalized Access schema keeps queries fast and reduces duplicate storage. Suggested tables:

Indexes on ProcessLog.Timestamp and ProcessLog.TagID will keep Access responsive above 1 million rows. Compact the database monthly (Database Tools > Compact and Repair) - Access tends to bloat when CSV imports happen frequently.

End-to-End Commissioning Procedure

1. Insert an HMC-EF card into the CPU and verify the MEM indicator turns steady green.
2. Download the application program from CX-Programmer; confirm AUTOEXEC.OBJ, SYMBOLS.SYM, and COMMENTS.CMT appear at the FTP root.
3. Run the ladder for two minutes; download REPORT.CSV from FTP and verify the header row matches the configured tag list.
4. Install and configure the CJ1W-ETN21 module. Test ping from the automation computer to the PLC's IP.
5. Open the FTP client and login with the configured user; browse the file memory and confirm all expected CSV files appear.
6. Install CX-Supervisor or the standalone Python puller on the automation computer.
7. Configure the ODBC DSN to the Access database and bind each PLC tag to the appropriate field.
8. Run for 24 hours. Compare row counts in ProcessLog against the number of CSV writes performed by the PLC.

Verification Checklist

Troubleshooting Matrix

Frequently Asked Questions