The first two fields of a trend.trd record are an epoch counter in seconds and a millisecond counter that repeat the date and time already in fields 3 and 4. The recorder layout is fixed, so strip the two fields on a retrieved copy: filter with -match, strip the prefix with -replace or cut -d';' -f3-, and write the result to a new file.
A trend.trd record opens with two machine timestamps, 1319589773;2573151;, ahead of the date field. The recorder offers no option that drops them, so remove them after retrieval: strip the first two semicolon-delimited fields on a copy. The line then begins at 26-10-2011;00:42:53;PLC_PRG.DATA1;2; exactly as your consumer expects. One cut command or a short PowerShell pipeline cleans the whole file. This page covers what the two fields hold, where the cut belongs, the exact commands, the spreadsheet route, and the checks that show the cleaned file matches the raw one record for record.
What are the first two columns of a trend.trd record?
They are two timestamps. Field 1 counts seconds since 1970-01-01 UTC, 1319589773 in the sample. Field 2 counts milliseconds since midnight, 2573151. Both repeat the date and time already carried in fields 3 and 4.
| Field | Sample value | What it holds |
|---|---|---|
| 1 | 1319589773 | Seconds since 1970-01-01, referenced to UTC |
| 2 | 2573151 | Milliseconds since midnight of the record's day |
| 3 | 26-10-2011 | Date, day first (dd-mm-yyyy) |
| 4 | 00:42:53 | Time of day |
| 5 onward | PLC_PRG.DATA1;2;... | Tag and value pairs; the line ends with a trailing semicolon |
Both readings are taken from the sample record. The worked example below converts each counter back onto 26-10-2011 at 00:42:53, which is the check that fields 1 and 2 carry nothing the date fields lack.
Can the recorder write the file without them?
No. The trend recording object exposes no export-format option that changes this layout. Treat the recorder output as fixed and strip the two fields after the file has been retrieved, on a copy, never on the file the recorder is writing.
Look once through the trend recording configuration in your programming system. If your version offers a format choice for exports, take it and the job is done at the source. On the file sampled here no such setting exists, and the five-field prefix is what the recorder writes every cycle.
One rule for everything below: retrieve trend.trd to a PC first and run every command against a copy, never against controller storage while the recorder writes.
Where in the data path should you remove the columns?
On the PC, after retrieval, on a copy of the file. The recorder keeps writing its fixed layout, the raw file stays available for anything that needs the millisecond fraction later, and the cleaned file matches the layout your consumer expects.
Three places can do the job:
| Approach | When it fits |
|---|---|
| Read from field 3 onward inside the consumer | You control the reader; no file is rewritten |
| Delete columns A and B after a spreadsheet import | A one-off look at one file |
| Script on the retrieved copy | Every export, or files too large to open by hand |
Two other routes make the problem disappear entirely. Logging PLC variables to a database captures values already shaped for queries, and showing full test duration on a live trend chart covers watching a run as it happens with no export at all.
Recommendation: script the removal for every recurring export, reading the retrieved trend.trd and writing a new file; use the spreadsheet import with two deleted columns only for a single one-off look.
How do you strip the two fields with a script?
Match each line against the two-numeric prefix, then strip everything up to the second semicolon. In Bash that is cut -d';' -f3-. In PowerShell it is a -match filter on the array followed by a -replace on the array. Both leave every tag, every value and the trailing semicolon untouched.
You need a retrieved copy of trend.trd in an empty working folder and either PowerShell on a PC or any Bash shell.
- Copy the retrieved trend.trd into the working folder and open the shell there.
- In PowerShell, run:
((Get-Content .\trend.trd) -match '^\d+;\d+;') -replace '^\d+;\d+;', '' |
Set-Content .\trend_clean.trd -Encoding utf8
- In Bash, the same job is one line:
cut -d';' -f3- trend.trd > trend_clean.trd
The -match filter keeps only lines that begin with two numeric fields, so blank lines and partial records drop out. The -replace then removes exactly the text from the start of the line through the second semicolon. Everything after it passes through untouched, including the trailing semicolon. In cut, -f3- means field 3 through the end of the line, the same cut point.
How do you remove the columns in Excel without breaking the dates?
Import through Data > From Text/CSV with the delimiter set to semicolon, then delete columns A and B. Force the date column to Text and the decimal separator to a dot, or a day-first date and a value like 4.000000 both import wrong.
- Open a blank workbook, choose Data > From Text/CSV, and select the retrieved copy.
- Set the delimiter to Semicolon.
- Set the date column to type Text, or pick an import locale that reads day-first dates.
- Load the table and delete columns A and B.
Two silent errors come from regional settings. On a US locale, a date whose day is 12 or lower flips to month-first, and days above 12 arrive as text. One column ends up mixing real dates and strings. On a comma-decimal locale, 4.000000 arrives as text and your plots come out empty.
What does a worked example on one record show?
Take the raw record 1319589773;2573151;26-10-2011;00:42:53;PLC_PRG.DATA1;2;PLC_PRG.DATA2;4.000000;PLC_PRG.DATA3;8.000000;: the two leading counters reduce to exactly the date and time already printed, and the split leaves eight fields where the raw line had ten.
Convert field 2 first: 2,573,151 ms ÷ 1000 = 2,573.151 s, and the whole seconds come to 42 min 53 s, so field 2 is 00:42:53.151 on the record's own day, field 4's time plus the fraction.
Convert field 1: 1,319,589,773 ÷ 86,400 s per day = 15,273 days with a remainder of 2,573 s. Count 15,273 days from 1970-01-01 and you land on 26-10-2011, and that remainder is 00:42:53, so field 1 is the same moment, referenced to UTC. It matches fields 3 and 4 whenever the controller clock runs on UTC.
Now the removal. The pattern ^\d+;\d+; matches 1319589773;2573151; at the head of this line and nothing else. After the replace, the line reads 26-10-2011;00:42:53;PLC_PRG.DATA1;2;PLC_PRG.DATA2;4.000000;PLC_PRG.DATA3;8.000000;. The raw line carries 10 fields plus a trailing empty one after the final semicolon; the cleaned line carries 8 plus the trailing empty. The difference is exactly the two columns you set out to remove, and PLC_PRG.DATA2;4.000000 passes through byte for byte.
What do you lose by dropping the epoch and millisecond fields?
Two things go with them: the millisecond fraction between samples inside one second, and an unambiguous ordering across a clock change. Keep the raw file and both stay available.
| Lost with the cut | Consequence | What to do |
|---|---|---|
| Millisecond fraction | Two samples inside the same second look identical | Keep trend.trd when you log more than one sample per second |
| UTC reference | The fall-back hour of a daylight-saving change repeats local time, so ordering by fields 3 and 4 can swap records an hour apart | Sort long runs by field 1 from the raw file |
Archive the raw export next to every cleaned file. When a number from this record has to appear as a clock or an elapsed time somewhere else, PLC timestamp unit conversion for factory displays works the same arithmetic in the other direction.
How do you check the cleaned file before using it?
Compare record counts and the first record. The cleaned file must hold exactly as many records as the raw file's two-numeric-prefix lines. Its first record must equal the raw record from field 3 onward, trailing semicolon included.
- Count both files:
((Get-Content .\trend.trd) -match '^\d+;\d+;').Count
(Get-Content .\trend_clean.trd).Count
The two counts must match. A cleaned file a few records short means the retrieval raced the recorder; pull the export again between recording cycles.
- Print the first cleaned record:
Get-Content .\trend_clean.trd -TotalCount 1. It must begin 26-10-2011;00:42:53;PLC_PRG.DATA1;2; and end with a semicolon. - Load trend_clean.trd in the consumer. The first row reads 26-10-2011, 00:42:53, and the three values plot as numbers: 2, 4.000000 and 8.000000.
When all three checks pass, ship the cleaned file and archive the raw one beside it.
Questions
What happens if you run the cleaning script on its own output?
The second pass writes an empty file. Cleaned lines begin with a date such as 26-10-2011, so they fail the two-numeric-prefix match and nothing survives the pipeline. Always read from the retrieved trend.trd and write to a distinct name such as trend_clean.trd. Keep the two files in separate folders if you script this on a schedule.
Why does the epoch number disagree with the controller's local time?
Field 1 counts seconds from 1970-01-01 in UTC, while fields 3 and 4 show the controller clock. The two agree when that clock runs on UTC. They differ by your UTC offset when it runs on local time. Convert one record before you decide which column is authoritative for your site.
Which encoding should the cleaned file use?
Trend.trd holds plain ASCII text, so match the consumer. Set -Encoding utf8 or ascii explicitly in Set-Content: Windows PowerShell 5.1 and PowerShell 7 choose different defaults. A byte-order mark at the top of the file breaks some CSV importers and analysis tools. ASCII matches the raw file byte for byte outside the removed prefix.
Does the trailing semicolon at the end of each record matter?
It leaves one empty field at the end of every row in most importers, which is harmless for reading and plotting. The script preserves it on purpose, so a cleaned record differs from its raw source by exactly the removed prefix and nothing else. Trim it only if your consumer rejects empty trailing columns, and trim after cleaning.
Planning information, not legal or engineering advice. Rules differ by place: check codes and permits with your local authority and your licensed trades.