Combine FTP trend files into one Excel sheet

What you need

• The folder path where the FTP uploads land, on the server or the PC that holds the files.
• Excel on Windows. The Get & Transform Data tools live on the Data tab in current versions, so most installs need nothing added.
• One structure across the batch: the same columns in the same order, one delimiter. The combine step reads its columns from a single sample file and applies them to every file in the folder, so one odd file pollutes the whole table.
• Two or three sample files opened once in a text editor, so you know the layout before you build anything.

Confirm the file format first

Open one received file before building anything. In a text editor, check that a Tab character separates the fields and write down the column order: timestamp, tag, value, whatever the panel exports. If you would rather look in Excel, the From Text/CSV import on the Data tab parses the file with the delimiter you choose and shows the columns before anything loads.

Two things stop a folder query before it starts. A file that is not text at all: some panels write the trend graph in their own binary format, and no delimiter choice will read it, so export that log as CSV at the panel first; the article on exporting CSV when a trend LOG file will not open covers that path. And files that drifted out of column order after a project change, which show up later as rows full of empty cells. Compare headers across a few weeks of files before you combine them.

Build the folder query

1. On the Data tab, choose Get Data, then From File, then From Folder. Browse to the landing folder.
2. Choose Transform Data. You get one row per file, with the file name, the extension and the Content column holding each file.
3. Filter that list to the batch you want: on the extension column for the file type, on the name column for machine or date patterns. This filter is what keeps alarm exports out of the trend table later.
4. Select the Content column, then choose Combine Files on the Home tab. Pick a representative file as the sample, set the delimiter to Tab, check that the preview shows clean columns, then OK.
5. The query parses the sample with that delimiter, applies the same parse to every filtered file and appends the rows into one table. A Source.Name column records which file each row came from. If the FTP job names each file with machine and date, that is your machine and date columns for free; the article on giving each HMI data log a unique CSV file name covers the naming.
6. Set the data types while the editor is open: timestamps as Date/Time, measured values as numbers. Text-typed numbers sort wrong and chart worse.
7. Choose Close & Load. The table lands on a new worksheet.

From then on the folder is the source: drop the day's uploads in, refresh the query, and the table grows. If the exports are comma-delimited, the same steps hold with the Comma delimiter; the article on combining daily HMI log files into one CSV walks that case.

Example: one month of trend files from 10 machines

Scenario: 10 machines, each uploading one daily trend file with one row per logged minute, so 1,441 lines per file counting the header. Over 30 days the folder holds 10 x 30 = 300 files.

Combining by hand means opening 300 files and pasting 300 x 1,441 = 432,300 rows, then hunting down the 299 duplicated header rows in the middle. At 2 minutes a file, that is 600 minutes, about 10 hours of pasting.

The folder query combines the same 300 files into 300 x 1,440 = 432,000 data rows under one header row, because the columns are read from the sample file once. Excel's worksheet limit is 1,048,576 rows, so the month fits on one sheet with room to spare. At this upload rate the sheet fills after about 73 days, and that is the point to switch to hourly summaries or one query per machine.

Recommendation: use the folder query as the demonstration build whenever the combined rows fit one worksheet. The month example at 432,000 rows is comfortable, and 1,048,576 rows is the hard wall. When daily uploads push a folder toward it, summarize by hour in the query, which takes the month from 432,000 rows to 10 x 720 = 7,200 rows at one row per machine-hour, or run one query per machine, before reaching for anything heavier.

Keep alarms and trends in separate queries

The combine step needs one file type and the same columns in every file it touches, and alarm exports do not share columns with trend samples. Run one query per file family: a trend query filtered to the trend file names, an alarm query filtered to the alarm names, each loading to its own sheet. The filter from step 3 is what enforces this.

If each panel uploads into its own folder, point one query per machine at each folder, or move the machine name into the file name so the Source.Name column carries it into the combined table.

Check the combined sheet

Three checks before anyone sees the sheet:

• Row count. Files in the folder times data rows per file: 432,000 for the example. A count on the timestamp column should match, with no duplicated headers left to subtract.
• Machine coverage. Filter the Source.Name column: all 10 machine names appear, each with its 30 files for the month. A missing machine is an FTP job that stopped, and the query finds that before the meeting does.
• Spot check. Filter to one machine and one date, open that original file beside the filtered rows, and compare row count, first timestamp and last timestamp. The query reads the folder and writes nothing to it, so the original file stays as evidence.

Where to go from here

The sheet is a snapshot of the folder at the last refresh, which is the right weight for showing management what the files already contain. Nothing on a panel, a PLC or the plant network changes to produce it, which is usually what gets a first demonstration approved. When the request grows into values on demand through the working day, the same FTP files feed a database instead; the article on collecting HMI trend logs over FTP into a database covers that pipeline. And when the counts are worth seeing where the operators stand, shop-floor display boards put the refreshed numbers up where they get used.

Questions