Automate File Processing

Customer Inquiry

• An external PLC generates TXT files that must be downloaded and converted into an SQL database.
• The PLC produces four files daily, each 5MB, totaling 20MB per day.
• File names follow the format YYMMDDHH.txt (e.g., 14022719.txt).
• Files are generated at 01:00, 07:00, 13:00, and 19:00 each day.
• Each file’s first row contains a timestamp (YYMMDDHHMMSS).
• It is crucial that each record is written only once in SQL.
• The file size remains consistent, and data is deleted after 10 days (FIFO method) on the PLC.

ETL Process

The ETL job will:

1. Download files via FTP to a local directory.
2. Convert the files to SQL and insert data into the database.
3. Delete the local copies after processing.

• The ETL process will run four times daily (about an hour after file generation).
• Files on the PLC must remain untouched, as other systems access them.

The Challenge

How do we track which files have been processed?

Potential issues include:

• Files cannot be deleted from the PLC after processing.
• Internet/server downtime may cause backlog (e.g., needing to process 6+ files instead of 4).
• Gaps in file timestamps due to PLC downtime are acceptable but should be managed properly.

The Solution

1. Synchronization Strategy

First, coordinate with the file provider to avoid downloading files while they are still being uploaded.
One approach is to have them place a “completion marker” file after upload is finished.

2. Preventing Duplicate Data Loads

Step 1: Extract File Metadata

Use a metadata transformation object to capture the source file name.

Step 2: Check if the File Was Already Processed

Use the “In List” Validator to compare against previously processed files.

Step 3: Load Data & Track Processed Files

After loading data into SQL, store processed file names in a separate table.

Overall ETL Workflow

1. Download files from the FTP server using a filename mask.
2. Process files while recording processed filenames to avoid duplicates.
3. Move processed files to an archive folder for backup.

With this approach, no files are processed twice, even after downtimes, and the system remains efficient.

Posted on March 14, 2023 • 2 min read • 377 words