Combine daily HMI log files into one CSV

What you need

• The daily log files, copied off the panel's USB stick or network share into a working folder on the PC. Work on copies; the originals are your recovery set.
• A Windows PC with Command Prompt.
• Notepad or another plain text editor.
• Excel, or any spreadsheet, for the import at the end.

If the panel warns that its log memory is full while you collect, clear that first; fixing HMI log buffer memory full errors on one screen is a separate job, and merging files from a buffer that is already dropping records only preserves the gaps.

Check the files before you merge

Open one file from the start of the period, one from the middle and one from the end in the text editor. Three checks:

1. Same columns, same order, in all three files: date and time in the first column, then the logged values. Files from one trend or log setup merge cleanly; files whose column count or labels differ belong to different trend setups and must be merged as separate sets.
2. One header row at the top of every file, or none anywhere. Headers in some files only mean the header output was switched on and off during the period, and the repeat headers need stripping after the merge.
3. The same extension on every file. Run dir *.txt in the folder and compare the count with what the period should produce; a mixed set of extensions means two merges or renamed copies.

Pick the output name now. Keep the output folder separate from the input folder, or give the output a name no daily file can match, so a rerun cannot fold an old merge back into itself.

Run the merge command

1. Copy the daily files into a working folder on the PC, for example C:\Logs\March. Leave the stick or share untouched; the same habit as backing up HMI recipe files before a project download applies here: the backup exists before you touch anything.
2. Open Command Prompt: press the Windows key, type cmd, press Enter.
3. Type cd followed by the working folder path and press Enter. help cd prints the syntax if the path has spaces.
4. Run dir *.txt and confirm no all.txt is listed.
5. Run copy *.txt all.txt. Windows answers 1 file(s) copied, and a merge of a few hundred files takes seconds.
6. Type exit to close the window.

The panel keeps writing its own daily files while you do this; nothing on the machine changes.

Example: 180 daily files logged once a minute

Inputs: 180 daily files, one per day, the six-month period you staged in the working folder; one sample every 60 seconds, the log interval set on the panel; 86,400 seconds in a day; one header row per file with header output on; Excel's worksheet limit of 1,048,576 rows, in place since the Excel 2007 release.

The rule in words: rows in the merged file equals the number of files times the samples per day, plus one header row per file, where samples per day is 86,400 divided by the interval in seconds.

The arithmetic:

• 86,400 ÷ 60 = 1,440 rows per file
• 180 × 1,440 = 259,200 data rows
• 259,200 + 180 = 259,380 rows in the merged file
• 1,048,576 − 259,380 = 789,196 rows to spare

Result: all 180 files fit in one worksheet with room to spare. Merge the whole six months in one pass and import once.

The decision, with its threshold: each merged file holds 1,441 rows per day of logging (1,440 data rows plus one header), so the ceiling works out to roughly 727 days per file at 1,048,576 rows (1,048,576 ÷ 1,441 ≈ 727). A year or two of one-minute logs merges into one worksheet. Three years does not: 1,095 days × 1,441 = 1,577,895 rows, so split the merge by year. The interval moves that ceiling hard. At one sample every 5 seconds a day is 86,400 ÷ 5 = 17,280 rows, the ceiling drops to 60 days (60 × 17,280 + 60 = 1,036,860, just under the limit), and 63 days at 1,088,640 rows would overflow, so at that rate split the merge by month.

Check the merged file

Open all.txt in the text editor before anything touches Excel.

• The first record carries the earliest date you staged and the last record the latest. Either one missing means the staging folder was incomplete; compare the file count against dir.
• Search for the header text. With headers on, it appears once per source file: 180 files, 180 header lines. More than that and an old merge got folded in.
• Read one day boundary: the last rows of one date, then the first rows of the next, with the column count unchanged.

If a check fails, fix the staging folder and rerun the command. The daily files were never modified, so rebuilding costs nothing but the seconds the command takes.

Import into Excel once

Start Excel's text import (the Text Import wizard or the Get Data from text option, depending on the version) and point it at all.txt. Set the delimiter to the one the logs use, comma for CSV-format logs, and set the date and time column to text so the stamp stays in one field. A stamp split into a date fragment and a time fragment in separate columns is the most common import failure; redoing the import with that column as text fixes it. Then compare three rows in the sheet against the same records in Notepad: the first record, the last record and one from a day boundary. Sort by the date and time column, filter to the period you report on, and save the sheet as an .xlsx working copy. Keep all.txt and the daily files; the spreadsheet is the throwaway.

When the plain merge is not enough

The command is a one-off tool. If the column layouts changed during the period, merge each layout as its own set and map the columns deliberately. If the logs feed a recurring report or a dashboard, the merge belongs in that system's export step, not in a command you run by hand. And if the analysis only ever needs shift summaries, check whether the panel's logging interval can be relaxed, because fewer rows mean more history per worksheet.

Recommendation: merge daily HMI logs with copy *.txt all.txt when the files share one column layout, the output folder is separate from the input folder, and the merged row count stays under Excel's 1,048,576-row worksheet limit; open files and copy rows by hand only for a handful of days or a one-off look at a single shift.

Questions