Configuring Node-RED to Poll FTP CSV Files into MQTT

Where does the CSV stop?

Four hops carry a speed value from the machine to a dashboard, and each one fails differently:

Hop	Role	Typical failure	Check
Machine FTP server	Writes a new CSV every 30 s	File still open / partially written when read	Compare file size across two reads
TCP 21 control channel	Login, directory listing, commands	Credentials, firewall, host unreachable	telnet host 21, look for the 220 banner
Data channel (PASV/PORT)	Carries the file bytes	Passive-mode ports blocked; listing hangs after login	Force passive, then active, see which returns bytes
Node-RED to broker	Publish on 1883/8883	Client ID collision, wrong topic, no broker auth	mosquitto_sub on a wildcard topic

The classic symptom — the flow deploys clean, the FTP node fires, nothing appears downstream — is almost always the data channel, not the login. FTP negotiates a second TCP connection for every transfer. In passive mode the server names an ephemeral port and the client dials out to it; if a firewall or NAT between the gateway and the machine only allows 21, control commands succeed and the transfer stalls with no useful error. Layer one and layer four first: confirm the path carries file bytes before blaming any node.

Before committing to FTP at all, check whether the machine exposes the same speed register over Modbus TCP or an OPC UA endpoint. Polling a holding register removes the file lifecycle, the delete step, and the partial-read race in one move. FTP is the fallback when nothing else is published.

Can you fetch the file outside Node-RED?

Prove the transfer with a plain FTP client on the same host that will run the flow — same OS, same user, same network interface. If a desktop client on a laptop works but the gateway does not, the difference is routing or firewall, not configuration.

The contributed FTP nodes are thin wrappers and are unreliable in exactly the cases that matter: passive-mode quirks, servers that refuse a directory listing, and embedded FTP daemons that answer commands out of spec. An exec node calling a real client is the dependable route. On Linux, lftp collapses connect, transfer, and cleanup into one command:

lftp -u operator,secret -e "set ftp:passive-mode true; \
  set net:timeout 10; set net:max-retries 2; \
  mget -E -O /var/tmp/csv /data/*.csv; bye" 192.168.10.50

The flags that matter: -E deletes each source file on the server after a successful retrieval, -O sets the local destination, and net:timeout stops a hung data channel from wedging the command forever. If passive mode returns nothing, flip ftp:passive-mode to false and retry — some machine-embedded FTP servers only implement active transfers.

Check before moving on: run the command by hand and confirm a CSV lands in /var/tmp/csv, the remote copy is gone, and the shell exit code is 0 (echo $?).

How do you drive the fetch from a flow?

With the command proven, the flow is a poll loop that only checks the exit code:

1. Wire it to an exec node in exec mode (wait for completion), not spawn. Spawn streams partial stdout and will hand the parser half a file.
2. Follow with a switch node on msg.rc.code: == 0 continues, anything else routes to a catch/log branch. A non-zero return with empty stderr usually means the glob matched no file — normal when the machine has not written yet, so log it at debug level, not as an alarm.

Set the exec node's timeout below the poll interval. Without it, a stalled data channel leaves overlapping child processes and the CPU load climbs until the gateway stops responding.

Check: attach a debug node to the exec output. Every poll should show rc.code: 0 and file contents on stdout or in the following file read.

How do you reduce the file to one speed value?

The payload arrives as text in the machine's format:

Date,Time,speed
2022/10/07,08:01:00,36

Pass it through a csv node configured with a comma separator and "first row contains column names" enabled, output as a single message containing an array of objects. Then take the last row in a function node:

const rows = msg.payload;
if (!Array.isArray(rows) || rows.length === 0) {
    node.warn("empty CSV, nothing to publish");
    return null;
}
const last = rows[rows.length - 1];
const speed = Number(last.speed);
if (!Number.isFinite(speed)) {
    node.warn("non-numeric speed: " + last.speed);
    return null;
}
msg.payload = {
    ts: last.Date + " " + last.Time,
    speed: speed
};
msg.topic = "line1/machine1/speed";
return msg;

Returning null on a bad parse is deliberate: a truncated file caught mid-write produces a header-only or half-written line, and publishing NaN corrupts the historian far worse than a skipped sample. A few seconds of latency between the CSV timestamp and the MQTT message is acceptable; a wrong value is not.

Carry the CSV's own Date and Time in the payload rather than stamping arrival time. When the poll drifts or a retry delays a fetch, the machine's timestamp is the only record of when the speed was actually measured. Note that it carries no timezone — record the machine's local offset in the flow documentation so the consumer can align it.

Check: a debug node after the function shows one object per poll with a finite speed and a plausible ts.

When is it safe to publish and delete?

Order the operations so a failure loses nothing:

Setting	Value	Reason
MQTT port	1883 plain / 8883 TLS	TLS if the link leaves the cell network
Client ID	Unique per gateway	Duplicate IDs cause a reconnect loop that drops messages silently
QoS	1	At-least-once survives a broker reconnect; the consumer handles the rare duplicate
Retain	Off for streaming speed	A retained value looks live long after the machine stops
Local file removal	After publish	Re-read is possible if the broker was down

Remote deletion is already handled by lftp -E, which only removes the server copy after the bytes are on disk — that is the safe order. If instead you delete with a separate command, run it strictly after the transfer succeeds, never in the same pipeline stage. Deleting first is how a poll cycle loses a sample permanently.

Delete the local copy in a file node (delete action) wired downstream of the MQTT out node, so a broker outage leaves the CSV on disk. If files accumulate, the broker is unreachable — that directory becomes a free health indicator.

Check: stop the broker for one poll cycle. The remote file should be gone, the local file should remain, and it should clear once the broker returns.

Does the chain hold end to end?

1. Subscribe from a third machine: mosquitto_sub -h <broker> -t 'line1/#' -v. Confirm one message per file, arriving on roughly the machine's 30 s cadence.
2. Compare the ts field against the wall clock over ten cycles. Drift beyond a few seconds means the poll and the write cycle are beating against each other — shorten the poll interval.
3. Watch for repeats of the same ts. A duplicate means a delete failed and the same file is being re-read; check the FTP account's write permission on the machine's data directory.
4. Pull the network cable to the machine for two minutes. Expect non-zero rc.code logged each poll, no MQTT publishes, and clean recovery with no backlog storm when the link returns.
5. Restart Node-RED mid-cycle and confirm any local CSV left on disk is picked up or safely discarded, with no NaN reaching the topic.
6. Leave it running for an hour and count messages at the subscriber: the total should match the polls that returned rc.code: 0, with no gap larger than one cycle.

Frequently Asked Questions