Turn a comma-delimited PLC data log into an Excel sheet

What does a comma-delimited PLC data log look like before you import it?

Before Excel comes anywhere near it, the file is plain text: two header lines at the top, then one line of comma-separated values per sample, written at the polling interval, every 20 seconds in the example used through this article. A logger set to poll over Modbus or a similar PLC network link reads the configured registers and appends one line per poll. The file grows steadily with the same fields in the same order:

V02000:B,V02001:B,DATE,TIME
0023,0024,04/02/12,06:41:00
0147,1288,04/02/12,06:41:20

The extract starts at the file's second line. The logger writes two header lines at the top of every file. The column-name line shown here sits below one more header line, and the data begins on line 3. The first data line carries two register values, a date and a time; the second shows the interval, 06:41:00 to 06:41:20.

Open the copy in any plain-text editor and read the top three lines before anything else. That one look settles every import setting that follows: the header line count, two here, and the field separator, a comma here. Other loggers use a tab or a semicolon, and the editor shows which one your file carries.

Why does a whole data line land in one Excel cell?

Excel split the line on the wrong character. The file separates its fields with commas and the import stayed on its default, typically tab. Or the file came in through the double-click route, where the defaults decide. Set the delimiter to comma and the same line lands as four cells: 0023, 0024, 04/02/12, 06:41:00.

The separator is a property of the file's contents, fixed when the logger was configured, and the import is where you state it. Tab advice fits tab files only; the comma you counted in the editor is what this file needs. The confusion is understandable because a .csv double-clicks straight into a comma guess. Renaming the extension sometimes appears to fix things, until a setup line, a stray tab or a decimal comma breaks the guess on some later row.

The same point appears from the other side in reading a panel's event-log CSV in Excel: start Excel from the menu and browse to the file, so the import dialog runs with your choices instead of the double-click defaults.

How do you import a .txt data log into Excel column by column?

Run the file through Excel's Get Data import with comma chosen as the delimiter. The route on the Data tab is Get Data, then From File, then From Text/CSV, and the whole file arrives with every field in its own column.

What you need:

• the log file copied from the USB stick into a working folder, with the stick's original left untouched
• a plain-text editor for the thirty-second look at the top of the file
• Excel with the Get Data commands on its Data tab; older versions run the same job through the legacy text import wizard, which asks for the delimiter on its first screen

1. Copy the file from the USB stick to the working folder. If the stick only ever holds the first day of logs, the copy button's press-and-hold behaviour is the usual cause, and HMI USB log copy stops after one day covers that fix.
2. Open the copy in the plain-text editor and note two numbers from the top of the file: the header line count, two in this example, and the fields per data line, four here.
3. In Excel, on the Data tab choose Get Data, then From File, then From Text/CSV, the route Excel's own documentation gives for importing text and CSV files.
4. Choose comma as the delimiter and read the preview: 0023, 0024, 04/02/12 and 06:41:00 must appear as four separate boxes.
5. Before loading, drop the header lines. The import's transform step carries a row-removal command that takes the number of top rows to skip: skip 2 and the sheet starts on real data, or skip 1 to keep the column-name line as the headers.
6. Load, then save the sheet as an Excel workbook with Save As. The .txt stays in the folder as the raw record, and the workbook can always be rebuilt from it.

Can you merge the separate files into one sheet first?

Import the files one at a time and stack the data afterwards. Each file restarts with its own two header lines, so a blind merge drops header rows into the middle of the data and breaks the every-20-seconds rhythm that the checks rely on.

The order that keeps the stack honest: import file one, import file two, then copy file two's data rows, minus its two header rows, underneath file one's data rows. Sort the stack by the date and time columns and read down the time column afterwards. Clean joins show as 20-second steps continuing across the boundary; a header row shows as text sitting where a time belongs, and two rows at the same timestamp show a doubled file.

Name the files so the stack can be rebuilt in order: giving each data log a unique CSV file name keeps day, machine and sequence readable in the file list. There is also a size reason to cut files at a month, and the next section puts numbers on it.

Worked example: how many rows does a 20-second log produce?

One line every 20 seconds is 4,320 lines a day: a day holds 24 × 60 × 60 = 86,400 seconds, and 86,400 divided by 20 is 4,320, so 30 days is 129,600 data rows and a 365-day year is 1,576,800 rows.

Set those against Excel's worksheet limit of 1,048,576 rows per sheet:

The two header lines add 2 rows to each file and change the answer nowhere. So the scenario lands here: a month of 20-second samples imports whole, 129,600 rows, with room to sort and chart; a year must be cut, and monthly files are the natural cut because they match the import-one-file habit from the merging section. A slower interval stretches everything proportionally: one line a minute is 1,440 rows a day, and a full year of that, 525,600 rows, fits one sheet again.

Is a 20-second polled flow total good enough to bill from?

For the history sheet, yes; for the invoice, log the total the PLC stores when the run completes, because a snapshot every 20 seconds can sit up to 20 seconds behind the true value at the moment the meter stops.

A polled register gives an honest running picture: how the flow climbs through a shift, when consumption starts, which hour peaked, line after line of it. An invoice wants one number: the total held when the customer's run ended. Flow that ran inside the last 20-second gap before the stop appears in no snapshot, so the closest snapshot and the invoice total are two different numbers whenever flow moved in that gap.

Capture the exact number in the same file format: log on change, so a new line is written whenever the total moves and the last line of a run carries its end value, or log a start-total and end-total pair per run and subtract. The PLC side of logging on change, a compare-and-save bit that fires when the register moves, is set out in log PLC data on a V-memory change with a compare-and-save bit.

Decision: keep the 20-second polling for history, and take anything that goes on an invoice from an event line or a start/end pair.

Should the PLC write the log line, or should the logger poll?

Let the logger poll when periodic samples answer the question, which is the case for the history sheet here; use serial print when the record must carry exactly the fields you choose, written at the moment the PLC decides, one line per invoice.

Polling needs no PLC code: the logger reads the configured registers over the network link and appends one comma line per interval, each one importing through the steps above. Serial print moves timing and content to the PLC: its print or ASCII-output instructions send a formatted line out a serial port, and the logger recognizes its write-to-file pattern in that text and appends the line to the file you named. The file name follows the old 8.3 convention, eight characters, a dot, a three-letter extension, so Invoice1.csv qualifies.

Pick polling for history sheets and shift reports; pick serial print when one line per completed transaction, in your own column order, is the deliverable. The write-to-file patterns belong to the logger's documentation: build the print line to that format and try it on a test file before pointing it at a live record.

How do you check the import worked?

Read the first two data rows against the clock: 06:41:00 in the first row and 06:41:20 in the second shows the 20-second interval survived the import, and four cleanly separated cells in each row shows the comma did its job.

Then run the checks down the sheet:

• Four cells per row, no cell holding two fields: register value, register value, date, time.
• Times stepping 20 seconds all the way down: 06:41:00, 06:41:20, 06:41:40. A jump marks a dropped line or a header row merged in from another file; a repeated timestamp marks a doubled file.
• Row count against run time: rows × 20 seconds equals the period covered, so a day file shows 4,320 rows.
• The date column reading the intended day and month; the month/day order question is in the FAQ below.
• Seconds visible in the time column. If Excel shows 06:41 and hides the seconds, that is the column's number format, and trend log timestamps show no seconds in Excel walks through the fix.

Recommendation: Import one file at a time with comma chosen as the delimiter, skip the two header rows before loading, and keep files cut at one month so every sheet stays far under the 1,048,576-row limit at a 20-second interval. Stack sheets only after each import shows its four columns lined up and its times stepping 20 seconds, and take any number that goes on an invoice from an event-logged line or a start/end pair, never from a 20-second snapshot.

Questions